(Code can be downloaded here)
(A newer version of the code can be downloaded here)
The Backup part
“Can you please give me a recent backup of all user databases on that computer please ?”
That is the sentence that will delay your planned work for the afternoon and will keep you busy for numerous hours depending on the number of databases you have in your environment. Wouldn´t it be nice to have something like an automated process that you can trigger to create those backups for you ? Well, that is what I thought some years ago and so I created a backup script / procedure which survived all the time through my consultant life. Adapted and pimped with new features and requests it now has only few parameters to tweak the backup process.
Once you place the stored procedure in your server, you will be able to use the following parameters:
|@BackupFolder||Folder where the backups will be placed. This can be any
folder accessible for SQL Server
|@DatabaseWildCard||Pattern that will be taken for enumerating the databases on the server that will be backed up, wildcards are possible and have to be written in TSQLstyle, e.g. SomeDB%|
|@CopyOnly||Will use the CopyOnlyFlag in the Restore command preserving the log chain.|
|@Compression||Will honor the compression flag if possible in this edition of SQL Server|
|@Debug||Will trigger to output the commands only instead of executing the BACKUP command|
After calling the stored procedues with the e.g. following commands, you will get a compressed backup, preserving the log chain on the server for all databases ending with “Prod”:
EXEC dbo.SpRestoreDatabaseBackups @BackupFolder = 'C:\Temp\Backups\' @DatabaseWildCard = ‘%Prod’, @CopyOnly = 0x1, @Compression = 0x1, @Debug = 0x0
The cool thing is that there will be also a time stamp included in the backup file name, making sure that you will take the right name for restoring.
Once you created the backups and transferred them to a new server, the next problem arises:
The Restore part
Have you ever had the problem of building up a test environment (maybe also again and again) and the only artifacts you had were the actual backup files thrown over the fence by an administrator ?
What will you do to restore the databases ? Opening SQL Server Management Studio and going through the GUI again and again ? Tedious if you get 100 backup files and you have to restore 100 databases. Sure, once you create the scripts you can easily pick them up again, but what if the structure changes ? Wouldn´t it be nice to have something like a snippet to open the backup media, read the file list and generate the RESTORE command ? What about also directly executing them if the physical paths are no different ?
That is were the anti-matter part of the Backup/restore solution comes into play. This procedure will enable you to pick up backup files (with a specific pattern in the file name or all files) from a specified folder and restore them. (Note: This functionality makes use of the xp_cmdshell for getting the file names via DIR. Make sure you enable the feature before executing this on the server and maybe disable it again if this is a threat for you, scripts are included in the source code files). Even less parameters than the Backup part, you will be able to tweak the restore process as follows:
Update: 22.09.2011: included new parameters to toggle the MOVE option as well as the DROP option for databases.
Folder where the backups have to be taken from. This can be any folder accessible for SQL Server
Pattern that has to be taken for enumerating the files on disk
Specifies if existing databaes have to be overwritten
Will trigger to output the commands only instead of executing the
|@DropDatabaseFirst||Specifies if the database will be dropped first|
|@IncludeMoveOperation||Specifies whether to include the MOVE operation or NOT (will make it easier if you want to relocate files)|
So, after calling the stored procedues with the e.g. following commands, you will get all databases from the specified folder of the name ending with “Prod” restored and overwritten from the actual backup files. The information will be automatically taken from the Backup fileheaders and a command will be created and executed for you.
After calling the stored procedues with the e.g. following commands, you will get all database backups in the specified folder where the wildcard applies restored to your system. If databases with the same name exist, they will be overwritten.
EXEC dbo.SpRestoreDatabaseBackups @BackupFolder = 'C:\Temp\Backup\', @BackupFilePattern '*Prod*.bak', @Overwrite = 0x1, @Debug = 0x0
If you are suspicious about those commands generated, you can also take the @Debug switch for both procedures and grab only the exeuction statements from the output window. Review them (and maybe change them according to your needs if you have different physical structures on the restoring platform than you have on the platform you did a backup from) and execute them with your favorite execution app.
If you find that useful or even have a similar approach or maybe have ideas on how to extend that procedure, leave me a note at JensS@microsoft.com.