Recovery Advisor: Using SSMS to create/restore split backups
This is part 2 of a two part blog series that my colleague Vatsalya Agrawal wrote up on the new tool called Recovery Advisor. Recovery Advisor is a new tool, that we have introduced to make restoring databases easier for users.
--
MS SQL Server has capability to split a backup file into multiple files. This is known as “Split File Backup”. In this blog I am going to discuss the support provided in SSMS for creating/restoring split file backups. Split file backups provide the following advantages over single file backups:
1. Split file backups can be taken faster than single file backups as different backup files can be written on different disks in parallel.
2. Split file backups help reduce the backup file size to more manageable/desired sizes. These sizes can be tailor made to fit the capacity of available media.
Let’s now checkout how SSMS can be used to create/restore split file backups.
Creating split file backups
To create a split file backup add multiple destination files while creating the database backup. To add multiple destination files, click "Add" and enter the name of additional output files. For example in Figure 1, the full backup of AdventureWorks database is going to be split across 2 files, namely “AdventureWorks1.bak” and “AdventureWorks2.bak”.
Figure 1: Creating Split File Backup.
Some points to note about split file backups are:
1. A file created as a part of n-way splitting can only be used for n-way split backup. It cannot be used for m-way split. For example, in the example above both files “AdventureWorks1.bak” and “AdventureWorks2.bak” can now only be used in 2-way split backups. These files cannot be used for single file backups or for 3-way split backups.
2. Backups of multiple databases can be taken in a single set of files.
3. Different types of backups (Full, Differential and Transaction Log) can be taken in a single set of files.
4. If a database backup file of size 2G, needs to be broken into files no bigger than 700M each. Then the database backup should be split into 3 files, thus each file would be ~700M each.
Restoring Split File Backups
To restore a database from a split file backup, go to the devices option (Figure 2) in the restore database dialog and open the file browse window to select files.
Figure 2: Device option to add multiple backup files during database restore.
When all the required files are added, the recovery advisor goes thru the file headers, the LSNs, etc and creates a restore plan seamlessly as shown in Figure 3.
Figure 3: Restore plan from split file backup.
A natural question now is: “how would I know if I have added all the files that constitute a split file backup” OR more specifically “how would I know which file I have missed”. Well, when all the required files are not provided, recovery advisor throws an error reporting the missing file. That is, if in our example only the first file is added recovery advisor would throw an error saying that file sequence number 2 is missing from the files provided (Figure 4). This information is very helpful in pin-pointing the missing file.
Figure 4: Recovery advisor reporting the missing split backup file.
In summary, splitting backup files can make taking backups faster and backup files smaller. SSMS helps in seamlessly creating a split file backup and in creating a restore sequence from split file backups. Happy splitting.
Vatsalya Agrawal can be reached at vaagrawa@microsoft.com.
Comments
Anonymous
June 06, 2012
Great article and thanks for sharingAnonymous
August 09, 2012
Nice feature...!!! Thanks for clear explanation :)