Configure backup options for several databases at once with LiteSpeed

In this blog we are going to learn how to use the "Multi-Database Backup" option which allows us to configure backup options for several databases at once.

To open this option we can Right-click the server instance and select "Multi-Database Backup" or alternately we can select the "Multi-Database Backup" button at the top.

 Let's start! First of all let's select the databases

The Backup Type

And the Backup Destination

Hold on! Someone is asking me a question!

Gunther: Could you have selected cloud instead of disk as a backup destination for the multiple backups?
Me: Thanks for asking! Yes, we can select cloud (Amazon S3 and Azure) and also the options below as a backup destination :

Now let's continue with our example.
When we click on the generate hyperlink, LiteSpeed generates this name for the files:

What do these parameters mean? Anytime you have a doubt anywhere in LiteSpeed just press F1. LiteSpeed has a very good help section and is illustrated with a lot of examples. Give the help a try and you won't regret it!

 



So according to the help, the file name should be something like this

RossDb_full_1233424444.bak

We will verify this later.

Let's ignore in this blog the following options of the wizard (which allows us to compress and encrypt backups, run all backups immediately or schedule via a SQL Server job. )

Note:  In case you select to schedule the backups to run at a specified times(in the "Backup Schedule" section), the wizard creates a job with 'Selected Databases' appended to the job name. Just go to the "Job Manager" and look for the job there!

Let's come back to the main example:

In the "Execute Script" option we can see the script that LiteSpeed will run by clicking the Preview Script hyperlink

 

In this case the script would be:

exec master.dbo.xp_backup_database
@MultiDatabaseType = N'SELECTED',
@progressname = N'a6aa1e59-b1cb-40e6-bc5e-000dd41c2c0d',
@database = N'ChandlerDb',
@database = N'JoeyDb',
@database = N'MonicaDb',
@database = N'PhoebeDb',
@database = N'RachelDb',
@database = N'RossDb',
@backupname = N'%DATABASENAME% - Full Database Backup',
@desc = N'Full Backup of %DATABASENAME% on %Y-%m-%d %I:%M:%S %p',
@compressionlevel = 2,
@filename = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\%D_%T_%z.bak',
@init = 0,
@OLRMAP = 1 ,
@with = N'STATS = 10'
GO

Let's see the backups generated:

The backups names are as expected . Note that with this nomenclature we assure that there are not collisions within the names.

Oh! another question! We are on fire today!
Ursula : Could I choose another name for the backups?
Me: Good one! yes sure. Let's see an example!

According the help (F1) we have these parameters:

Let's add these ones (in this case we will modify the script but we could also modify this in the wizard)

exec master.dbo.xp_backup_database
@MultiDatabaseType = N'SELECTED',
@progressname = N'a6aa1e59-b1cb-40e6-bc5e-000dd41c2c0d',
@database = N'ChandlerDb',
@database = N'JoeyDb',
@database = N'MonicaDb',
@database = N'PhoebeDb',
@database = N'RachelDb',
@database = N'RossDb',
@backupname = N'%DATABASENAME% - Full Database Backup',
@desc = N'Full Backup of %DATABASENAME% on %Y-%m-%d %I:%M:%S %p',
@compressionlevel = 2,
@filename = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\%D_%T_%z_%A_%B.bak',
@init = 0,
@OLRMAP = 1 ,
@with = N'STATS = 10'
GO

 And let's check the file names:

Hope this helps! It was good to have questions from my imaginary friends but I can also answer to real people so do not hesitate to ask me if you have any questions or concerns about this!

Thanks for reading! Learn more about SQL Server backup, compression and restore.

Anonymous