[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:
@ECHO OFF
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.