[SQL] Scripting backup and restore all databases on a server (Part 1 - Simple Case)
We just migrated a group of production sites from one hosting environment to another. The new environment has staging and production servers, so we really completed two migrations. There were a lot of databases, and if you've been following my blog at all you probably know that I love to script repetetive tasks - not (only) from laziness, but from a desire to avoid typographical errors due in repetetive manual work.
First, I backed up all databases on the old server. I ran this script, which generated a DOS batch file:
DECLARE @BACKUP_DIRECTORY varchar(100)
SET @BACKUP_DIRECTORY = 'E:\DB_Backups\'
SELECT
'osql -E -d master -Q "BACKUP DATABASE '
+ QUOTENAME(CATALOG_NAME)
+ ' TO DISK = N'''+@BACKUP_DIRECTORY+''
+ CATALOG_NAME
+ '.bak'' WITH INIT, NOUNLOAD, NAME = N'''
+ CATALOG_NAME
+ 'backup'', NOSKIP , STATS = 10, NOFORMAT"'
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN ('master','tempdb','msdb','model','Northwind','pubs')
So I shifted the Query Analyzer output to text mode (ctrl-t), ran the above query, and saved the results to a file - BackupDatabases.bat. I wanted a batch file so I could test the migration up to the cutover day, at which time I'd need to do a final backup of the old sites. Looking good so far.1
At this point, I zipped up the backup directory (full of .bak files) and copied it to the new server. Now it's time to restore all those .bak files. The following batch file rips through a directory, restoring everay .bak file it finds:
SET DBDIRECTORY=D:\Program Files\Microsoft SQL Server\MSSQL\Data
SET BACKUPDIRECTORY=C:\DB_Backups
PUSHD %BACKUPDIRECTORY%
FOR %%A in (*.bak) do CALL :Subroutine %%A
POPD
TITLE Finished restoring database backups
ECHO Finished restoring database backups
PAUSE
GOTO:EOF
:Subroutine
set DBNAME=%~n1
TITLE Restoring %DBNAME% Database
ECHO Restoring %DBNAME% 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\%DBNAME%.bak' WITH MOVE
'%DBNAME%_Data' TO '%DBDIRECTORY%\%DBNAME%_Data.MDF', MOVE '%DBNAME%_Log' TO
'%DBDIRECTORY%\%DBNAME%_Log.LDF'"
::GRANT PERMISSION TO ASPNET USER - UNCOMMENT IF DESIRED
::osql -E -d %DBNAME% -Q "sp_grantdbaccess '%COMPUTERNAME%\ASPNET'"
::osql -E -d %DBNAME% -Q "sp_addrolemember 'db_owner', '%COMPUTERNAME%\ASPNET'"
::RESTORE TO MULTI USER
osql -E -d master -Q "alter database %DBNAME% set multi_user"
GOTO:EOF
Unfortuantely, it didn't work for a few of the databases. What had I done wrong? Well, I'd assumed the simple case (hence the title of this post) - I'd assumed that the database logical names matched the database name, so Example.bak would restore to Example_Data (in Example_Data.mdf) and Example_Log (in Example_Log.ldf). That's not always the case, especially if a database has been copied via backup / restore / rename.
I'll talk about how to script backup / restore when database logical names don't match the database name next...
1 You can use of course set up SQL Server jobs to backup your databases, but there are advantages to having a single batch file which backs up all databases in one go.