[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 -v -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 -E -d master -Q "alter database %DBNAME% set single_user with rollback immediate"
::RESTORE DATABASE
osql -E -d master -Q "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 -E -d %DBNAME% -Q "sp_grantdbaccess '%ASPNETACCOUNT%'"
osql -E -d %DBNAME% -Q "sp_addrolemember 'db_owner', '%ASPNETACCOUNT%'"
::RESTORE TO MULTI USER
osql -E -d master -Q "alter database %DBNAME% set multi_user"
ECHO Done, press Enter key to close...
PAUSE
::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 -v -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 -E -d master -Q "alter database %DBNAME% set single_user with rollback immediate"
::RESTORE DATABASE
osql -E -d master -Q "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 -E -d %DBNAME% -Q "sp_grantdbaccess '%ASPNETACCOUNT%'"
osql -E -d %DBNAME% -Q "sp_addrolemember 'db_owner', '%ASPNETACCOUNT%'"
::RESTORE TO MULTI USER
osql -E -d master -Q "alter database %DBNAME% set multi_user"
ECHO Done, press Enter key to close...
PAUSE