[SQL] FTP download and restore an MSSQL database backup

I extended my DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account to pull the database backup from an FTP location.

This has been a big timesaver on a recent DotNetNuke based project. The designer and client are working off a staging server, so I regularly need to refresh my development database to keep in sync. We have a scheduled nightly database backup on our stage server that writes to a location that's accessible via FTP, so I can just run this script and I'm caught up to the most recent backup.

The variables are set at the top of the script. The ones you'll definitely need to change are shown in bold red.

@ECHO OFF

::SET VARIABLES
ECHO Setting Variables
set DBNAME=mydatabase
set DBLOGICALNAME=%DBNAME%
set DBDIRECTORY=C:\Program Files\Microsoft SQL Server\MSSQL\Data
set DBBACKUPFILE=%DBNAME%_backup
set FTPADDRESS=ftp.myserver.com
TITLE 
Restoring %DBNAME% Database from FTP

::Use %COMPUTERNAME%\ASPNET for localhost
::Use DOMAIN\ACCOUNT$ for a domain account
set ASPNETACCOUNT=%COMPUTERNAME%\ASPNET

set /p GETLATEST=Download backup from FTP (Y/n)
IF "%GETLATEST%" == "N" GOTO RESTORE
IF "%GETLATEST%" == "n" GOTO RESTORE

set /p FTPUSERNAME=Enter FTP User Name
set /p FTPPASSWORD=Enter FTP Password
CLS
set 
FTPDIRECTORY=DatabaseBackups

ECHO Downloading backup via FTP
:: Create the temporary script file
script.ftp USER
>>script.ftp ECHO %FTPUSERNAME%
>>script.ftp ECHO %FTPPASSWORD%
>>script.ftp ECHO binary
>>script.ftp ECHO prompt n
>>script.ftp ECHO CD %FTPDIRECTORY%
>>script.ftp ECHO get %DBBACKUPFILE%
>>script.ftp ECHO bye

:: Use the temporary script for unattended FTP
FTP --s:script.ftp %FTPADDRESS%
:: Overwrite the temporary file before deleting it
TYPE NUL >script.ftp
DEL script.ftp

:RESTORE
ECHO Restoring database
::PUT DATABASE IN SINGLE USER MODE TO ALLOW RESTORE
osql --d master -"alter database %DBNAME% set single_user with rollback immediate"

::RESTORE DATABASE
osql --d master -"restore database %DBNAME% from disk='%~dp0\%DBBACKUPFILE%' WITH MOVE '%DBLOGICALNAME%_Data' TO '%DBDIRECTORY%\%DBNAME%_Data.MDF', MOVE '%DBLOGICALNAME%_Log' TO '%DBDIRECTORY%\%DBNAME%_Log.LDF'"

::GRANT PERMISSION TO ASPNET USER
osql --%DBNAME% -"sp_grantdbaccess '%ASPNETACCOUNT%'"
osql --%DBNAME% -"sp_addrolemember 'db_owner', '%ASPNETACCOUNT%'"

::RESTORE TO MULTI USER
osql --d master -"alter database %DBNAME% set multi_user"

ECHO Donepress Enter key to close...
PAUSE

 

1 Comment

Comments have been disabled for this content.