Use T-SQL to Back Up and Restore SQL Server User Databases

I wrote a couple of T-SQL procedures—sp_backup_databases and sp_restore_databases—you can use to move all your user databases from one Microsoft SQL Server machine to another with ease. The sp_backup_databases procedure performs a complete database backup of all user databases to the specified directory. The sp_restore_databases procedure restores those databases.

Listing 1 shows the sp_backup_databases procedure. It should reside in the master database of the SQL Server machine on which all user databases exist. The sp_backup_databases procedure requires one parameter that specifies the backup destination directory. For example, if you run the command

exec sp_backup_databases 'c:sqlbackup' 

the sp_backup_databases procedure backs up all user databases to the C:sqlbackup directory. The names of the backups will be in the format .bak.

Listing 2 shows the sp_restore_databases procedure. It should reside in the master database of the SQL Server machine on which you want the databases to be restored. The procedure requires two parameters. The first parameter specifies the location in which to restore all database files. If this location is null, the files are restored to the original position listed in the backup header (as shown using restore filelistOnly). The second parameter is the location of backup files. For example, if you run the command

exec sp_restore_databases 'c:data','c:sqlbackup'

the sp_backup_databases procedure restores the database files in the C:sqlbackup directory to the C:data directory.

— Eli Leiba
[email protected]

Editor’s Note
Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $50.