Bulk Backup and Restore at the tip of your finger


(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.

   

@BackupFolder

Folder where the backups have to be taken from. This can be any folder accessible for SQL Server

@BackupFilePattern

Pattern that has to be taken for enumerating the files on disk
wildcards are possible and to be written in DOS style, e.g. *.bak

@Overwrite

Specifies if existing databaes have to be overwritten

@Debug

Will trigger to output the commands only instead of executing the
RESTORE command directly

@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.

-Jens

 

Comments (14)

  1. hacktek says:

    This was AWESOME! The restore one saved me a bunch of time, thanks a lot!

  2. Pierre says:

    Your link to the code doesn't work: Access denied. Can you restore it? Thanks

  3. Szadek says:

    Very nice job. Thanks.

  4. josh obudzinski says:

    Very awesome and helpful.  I did make some minor adjustments that when you choose move option that instead of using the old locations it would move to the default data and log file paths on the new server.  Just something that works for me, but thought I would let you know.  If you are interested in what I did just shoot me  a message and i can send you the updated stored procedure.  joshobud@gmail.com  Thanks again!

  5. B. Christopher Wang says:

    Nice script!

    Had to do some adjustments sice SQL 2012 std edition supports compression and I added parameters to export users and userrights aswell :). The script was a great baseline and did just what I needed.

  6. Philippe Meunier says:

    First of all, thank you for those scripts, they are very useful. made a small edit to your code in the BACKUP part. Our installation of SQL Server contains databases with long names and this script breaks due to that. QUOTENAME used to build the path accepts a parameter up to 128 characters. So, if your path is too long or your database names are long, it will not backup anything.

    Here's the edit I made, to the SELECT statement near the end :

    SELECT @SQL += 'BACKUP DATABASE ' + QUOTENAME(name) + ' TO  DISK = N'''

    + REPLACE(@BackupFolder + name + '_' + @TimeStamp + '.bak', '''', '''''') + ''''

    + ' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10' +  

    CASE WHEN @Compression = 0x1 THEN ',COMPRESSION' ELSE '' END +  

    CASE WHEN @CopyOnly = 0x1 THEN ',COPY_ONLY' ELSE '' END + ';' +  

    CHAR(10) + + CHAR(13)  

    FROM sys.databases  

    WHERE name LIKE @DatabaseWildCard

    Hope it helps

  7. Brian says:

    We are trying to use the restore feature, as the backup worked just fine, and are having a problem with the fact that they path names differ, since we are using 2012 vs. 2008.  We are trying to use the @IncludeMoveOperation parameter, so that it moves it to the new set location of the instance, however, in looking at the code, it appears that it's still pulling from the backup file itself….as it fails.

    IF @IncludeMoveOperation = 0x1

    BEGIN

    SELECT @IncludeMoveOperationScript += CHAR(13) + ' MOVE ' + QUOTENAME(R.LogicalName, CHAR(39)) + ' TO ' + QUOTENAME(R.PhysicalName, CHAR(39)) + ','

    FROM @RestoreInformation R

  8. VintageDon says:

    Just wanted to say how much this script has become a regular use for me and has become a part of my toolbox.  So thanks for the time you took on this, and publishing it, no less, for people to use.

    Unfortunately, I've hit the same brick wall that Brian has above: I have a couple of hundred DBs that I need to move from one server to a new upgraded server, and need to change the paths, as I'm now storing the DBs and logs on a RAID10 SSD volume.

    Using the @IncludeMoveOperation simply pulls the information from the backups, instead of accepting a new location.  I've looked at the code, and don't think I'm missing anything … can you perhaps take the time to release a new piece of code with that fixed, or let us know if we're thinking wrong and there's another way to do this?

  9. JensS says:

    Hi Don,

    and thanks for the candid feedback. good to hear that the script is useful in your daily life and saves you time as this was my intention helping people not spending their valueable time on such tedious work.

    Nor back to your request. I heard the request that many times >/( Here is the catch I also answered to some people contacting me privately:

    Yes, the @IncludeMoveOperation Option simply means that it will include the “MOVE” Syntax in the script but will not change the paths. I had some requests for this already to also change the paths with that parameter. The problem was that many customers have different paths which should be changed depending on their environment., so you would have to enable the parameters to include multi-matrix combinations of old target / new target. This would be hard to handle and would make maintenance harder (Imagine people with hundreds of databases, tens of drives and / or mountpoints)

    As many customers want to move specific used parts to other specific parts, the idea behind this is:

    1. Create the restore scripts by using the @Debug Option. This will only output the RESTORE Statement in the result pane.

    2. Copy the stuff from the output pane and replace the path(s) formerly used to another path(s) where You want to restore the files.

    3. Execute the changed script and it will apply the new paths to the data files.

    Hope this makes the usage and purpose of this switch a bit more clear. If you have any additional questions let me know. 🙂

    -Jens

  10. Jose Longhi says:

    Hello Jens,

    The link to download the code is broken.

    Can you link it again.

    Seem like this is what I'm looking for.

    Thanks.

  11. Kumar says:

    How can I restore database with .mdf to D: drive and .ldf to E: drive ?

  12. Pepe Elizondo says:

    Hi Jens,

    I've found your script very useful as well, I had a situation where I had to move several databases at a time via backup & restore which would have been a pain if I haven't found your script.

    I made a slight change to the Restore script in order to have the files restored to the default data and log files specified at the instance, I'd like to post you this version so you can give me your thoughts, perhaps fix it, and finally share it to the community.

    Thank you!,

    Pepe

  13. Pepe Elizondo says:

    Jens,

    Also… I added a parameter to bulk restore full backups, then differential backups.

    Thank you again,

    Pepe

  14. Alex Vdovyn alex.vdovyn@decryptex.com says:

    Hello,

    Thank you for the script. It is very useful.

    But, I still have some bugs.

    The backup procedure works well.

    For the restore procedure I have following errors:

    Column name or number of supplied values does not match table definition.

    RESTORE HEADERONLY is terminating abnormally.

    Any ideas what could be the problem.

    I am using SQL Server 2014 BI Edition.

    Thank you,

    Alex