[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:


'osql -E -d master -Q "BACKUP DATABASE '
+ '.bak'' WITH INIT, NOUNLOAD, NAME = N'''
+ 'backup'', NOSKIP , STATS = 10, NOFORMAT"'
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

FOR %%in (*.bakdo CALL :Subroutine %%A

Finished restoring database backups
ECHO Finished restoring database backups


set DBNAME=%~n1

TITLE Restoring %DBNAME% Database
ECHO Restoring %DBNAME% Database

osql --d master -"alter database %DBNAME% set single_user with rollback immediate"

osql --d master -"restore database %DBNAME% from disk='%~dp0\%DBNAME%.bak' WITH MOVE 



::osql -E -d %DBNAME% -Q "sp_grantdbaccess '%COMPUTERNAME%\ASPNET'"
::osql -E -d %DBNAME% -Q "sp_addrolemember 'db_owner', '%COMPUTERNAME%\ASPNET'"

osql --d master -"alter database %DBNAME% set multi_user"


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.


  • Wonderful,

    I need restore script too to restore all backup databases to new clean instance of SQL Server. The explanation is as below:

    How to backup SQL Server 2005 user databases in SQL script with all data (.sql) and restore this script of all databases on SQL server 2008


    How to restore all databases backed up through this script into new clean instance of SQL Server (backed up in SQL Server 2005 standard and restore to SQL Server 2008 web edition).

    Can someone help me to backup all MS SQL Server 2005 standard user databases into SQL script (single file) and restore all databases with data from single backup script file to SQL server 2008 web edition.

    Upgrade option for SQL Server standard 2005 to SQL SQL Server 2008 web edition is not supported. I've planned to upgrade SQL Server 2005 standard instance to SQL server 2008 web edition that is not supported and I'm thinking to backup all databases on SQL server 2005 standard into single script file (.sql) install new instance of SQL Server 2008 web edition and restore single script file into new instance. Please help me backup all databases into single script file.


    Alternative option is to generate script to restore all databases through your script in new installation of SQL Server 2008 web edition.

    Help me please.

  • I don't see why spend time in creating scripts to backup databases while there are so many easier ways to do it.

    First and most obvious option would be using Management Studio. There are also many utilities available like this http://sqlbackupandftp.com which lets you backup more than one database at a time. This tool even lets you copy your backup to a remote server.

Comments have been disabled for this content.