SQL SERVER – Mirrored Backup and Restore and Split File Backup

Introduction

This article is based on a real life experience of the author while working with database backup and restore during his consultancy work for various organizations. We will go over the following important concepts of database backup and restore.

  1. Conventional Backup and Restore
  2. Spilt File Backup and Restore
  3. Mirror File Backup
  4. Understanding FORMAT Clause
  5. Miscellaneous details about Backup and Restore
Note: Before running all the examples, make sure that you have the required folders created on your drive. It is mandatory to create Backup folders prior to creating backup files using SQL Server.

In our example, we will require the following folders:

  • C:\Backup\SingleFile
  • C:\Backup\MultiFile
  • C:\Backup\MirrorFile

Conventional and Split File Backup and Restore

Just a day before working on one of the projects, I had to take a backup of one database of 14 GB. My hard drive lacked sufficient space at that moment. Fortunately, I had two 8 GB USB Drives with me. Now, the question was how to take a backup in two equal sizes, each of 7 GB, so I can fit them on each USB drive. Well, conventional backup takes one large backup in one file. However, SQL Server backup command can take backups in two or more split parts.

Let us see an example of a conventional one-file backup using the AdventureWorks database.

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'
GO

The result is displayed below. Here, the backup is taken in a single file.

Now, let us see how we can split one database into two different database files. This method is very similar to taking a single-file backup. By simply adding an additional DISK option we can split the files backup files.

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'
GO

In the previous example, we can clearly see that backup is split into three equal parts of the original backup file size.

Restoring a backup from a single-file backup is quite easy. Let us go over an example where we restore the AdventureWorks database from a single backup file.

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'
GO

Running the above script will give a successful message.

Now let us see an example where we restore a database from a split file. This method is very similar to restoring a database from a single file; just add an additional DISK option.

RESTORE DATABASE [AdventureWorks]
FROM DISK = N'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks3.bak'
GO

Running the above script will give a successful message as shown in the image below.

Make sure that while restoring database, the database is not in use, otherwise it will give an error of database in use. In the event of an error taking place, close all the connections and re-attempt to restore the database.

Mirror Backup of the file

It is quite a common practice to create an exact copy of the backup and store it to several places to deal with any catastrophes which might affect the place where the database is stored. Once a full backup is accomplished DBAs generally copy the database to another location in their network using a third party tools like robocopy or native DOS commands like xcopy.

In SQL Server 2005 and later versions, there is a Mirror command that makes a copy of the database backup to different locations while taking the original backup. The maximum limit of additional locations that can be specified with MIRROR clause is 3.

Mirrored backup can be taken in local computer system as well as in a local network. Let us now see two examples of mirror backup.

Example 1. Single File Backup to Multiple Locations using Mirror

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'
MIRROR TO DISK = 'C:\Backup\MirrorFile\AdventureWorks.bak'
WITH FORMAT
GO

If this command is being run for the first time, it is mandatory to use the WITH FORMAT clause; but for sub sequential runs it is not required. WITH FORMAT reinitializes the backup.

When checked in both the folders ‘SingleFile’ and ‘MirrorFile’, backup files are exactly the same files. As mentioned earlier, four mirror backup can be specified in total.

Example 2. Split File Backup to Multiple Locations using Mirror

We have earlier seen an example where we can have multiple split files of large database backup files. SQL Server Mirror functionality also supports backup of the split files.

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'
MIRROR TO DISK = 'C:\Backup\MirrorFile\AdventureWorks1.bak',
DISK = 'C:\Backup\MirrorFile\AdventureWorks2.bak',
DISK = 'C:\Backup\MirrorFile\AdventureWorks3.bak'
WITH FORMAT
GO

All the mirror sets will need the same number of DISK clauses as the original backup media.

Mirrored database backup can be restored using the same method as the original backup. Mirrored backup is in fact an exact replica of the original backup.

Understanding the FORMAT Clause

The FORMAT clause is used to reinitiate a backup media. Although it is a very useful clause it should be used with caution. When the clause is used it erases everything present in backup media. I have noticed that some DBAs are confused while taking a backup on a local disk where they have SQL Server installed. They have a misconception that if the format command is used, it will erase the complete disk including the SQL Server installation. However, the fact is that SQL Server format clause is quite different from OS format. The effect of SQL Server format clause is limited to a folder or path specified in the DISK clause.

In our example, when the FORMAT clause is specified, it will format only folders like C:\Backup\MultiFile\ or C:\Backup\SingleFile.

Related Errors

Error 3010
Invalid backup mirror specification. All mirrors must have the same number of members.

This error can show up while taking a mirrored database backup along with a regular backup; and DISK and MIRROR TO DISK do not match accurately.

The following image demonstrates how the error takes place.

To fix the error, match the members of DISK and MIRROR TO DISK to each other.

Error 3215
Use WITH FORMAT to create a new mirrored backup set

This error can spring up when a new backup is initiated and an existing media header needs to be reset for all headers on the backup media. If there is already a backup on the media, it will display this error and prevent backup from being overwritten. To fix this error, use WITH FORMAT as shown in an earlier example.

Miscellaneous details about Backup and Restore

When no options are specified, BACKUP DATABASE takes only full backups. Before taking the first log backup, full database backup is necessary to take one full backup. Backups created on later versions of SQL Server cannot be restored to earlier versions of SQL Server. The user needs permissions of sysadmin or db_owner or db_backupoperator roles to perform backup operation.

Watch a 60 second video on this subject

Reference : Pinal Dave (http://blog.sqlauthority.com)

About these ads

37 thoughts on “SQL SERVER – Mirrored Backup and Restore and Split File Backup

    • Hi dave,

      When w are taking backups on multiple drives using mirror option, will it be more time consuming?

      Regards,

      sanjeev Kumar

  1. If you make a “Split File Backup”. Does SQL Server make it faster? Use threads or something? Or takes the same amount of time?

    Thanks,
    Erik

  2. Morning Pinal,
    This is one of the best Post that i have liked..Excellent explanation..!!!! Really really excellent…!!

    Gr8 going…

    Thanks,
    Nitin Sharma

  3. Hi Pinal,

    I am a new learner of SQL but you explained it so easily that it really rocks!!

    Thank you so much.This will help me a lot in my application.

  4. Hi Mr.Pinal,

    I’ve been working 1.5 yr as a developer in ASP.Net. i had more helps from your blog. Really its unbelievable.You are giving to hand to more DBA and developer people.for that my heartiest thanks

  5. Great tutorial as usual, Pinal. I might add, though, that mirroring backups are only available on Enterprise Edition. If you run it with those keywords, you’ll get this nice delicious error:

    Msg 3218, Level 16, State 1, Line 9
    Backup mirroring is not available in this edition of SQL Server. See Books Online for more details on feature support in different SQL Server editions.
    Msg 3013, Level 16, State 1, Line 9
    BACKUP DATABASE is terminating abnormally.

  6. Hi Pinal,

    i have dought in Backups

    1)In Backups how to find out sequential backup.

    what is the command.?

    2) In log backup ,lsn are miss-matched.
    you have a 10 log backups there which is suitable to find out to your log backup?
    3) what is fork -lsn?

    Thanks &Regrads
    harishkumar.M

  7. Before you apply for restore make sure you try to use TailLog backup or use the optoin “Restore SQL Server database and overwrite existing database” on SSMS. or you can use Replace on T-SQL.

    T-SQL

    Restore full backup using WITH REPLACE
    The command below will restore the database and disregard any active data in the current transaction log

    RESTORE DATABASE [AdventureWorks]
    FROM DISK = N’C:\Backup\MultiFile\AdventureWorks1.bak’,
    DISK = N’C:\Backup\MultiFile\AdventureWorks2.bak’,
    DISK = N’C:\Backup\MultiFile\AdventureWorks3.bak’
    WITH REPLACE
    GO

    Reference

    http://www.mssqltips.com/tutorial.asp?tutorial=121

    http://www.sqlbackuprestore.com/backingupthetail.htm

  8. Dear sir
    thank you for your helpful blog,
    i have a question and i am so confused about this problem , i am a beginner and i would be happy if you help me ,
    is there any way to back up from a database (sqlserver)
    into the local computer ??
    i mean i want to make a back up in my local computer from server .
    thanks alot

  9. Hello Nafiseh,

    Create a network drive for your local disk and then backup on the network drive.
    Other wise take the backup on the server’s disk and then copy to your local disk.

    Regards,
    Pinal Dave

  10. Hi, we have several mirrored databases.
    What is the ‘correct’ way of backing up the mirrored dbs?
    Recently we had a situation when after a couple of the dbs have failed over, the backup job failed to complete as the dbs status had changed from principal to mirrored.
    Should we setup identical backup jobs on both servers in anticipation that only one job succeed against the principal database…

    Your help would be much appreciated.

    Regards,
    Vadim

  11. Hi Pinal,

    I m little bit confussed here in ur example u have specifed that u r having two USB of 8 GB each.

    so the drive path for the USB will be different are you saying that we can split the 14 GB database backup into two file like 7GB each.

    Can u please clarify.

    I have understood ur concept.

    Regards
    sam

  12. Pingback: SQL SERVER – Taking Multiple Backup of Database in Single Command – Mirrored Database Backup Journey to SQL Authority with Pinal Dave

  13. Thanks a lot,
    this way we can gain a high availability when mirroring,
    i’d like to ask how to do this in a maintenance plan in SQL2k8.
    Regards,

  14. Hello Pinal, nice job!
    I’d like to add that, if you have to restore a splitted backup, the sequence of the files included in the RESTORE DATABASE statement doesn’t matter

    For example:

    RESTORE DATABASE AdventureWorks
    FROM DISK=N’E:\AdventureWorks2.bak’,
    DISK=N’D:\AdventureWorks1.bak’
    with (…)

    will work fine.
    Obviously, you have to speccify all of the files the backup in splitted in, the sequence in unimportant.
    Cheers!

  15. Hello Pinal, nice job!
    I’d like to add that, if you have to restore a splitted backup, the sequence of the files included in the RESTORE DATABASE statement doesn’t matter

    For example:

    RESTORE DATABASE AdventureWorks
    FROM DISK=N’E:\AdventureWorks2.bak’,
    DISK=N’D:\AdventureWorks1.bak’
    with (…)

    will work fine.
    Obviously, you have to speccify all of the files the backup in splitted in, the sequence in unimportant.
    Cheers!

  16. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  17. @Murali,

    USE MSDB
    SELECT * FROM Backupset

    From there you can filter what information you need via the WHERE clause.

    We use that T-SQL above for our DRS site. Our admins there just click the batch file we made and it restores all differentials and translogs.

    Commandline would be:

    SQLCMD -S -E -d msdb -h-1 -W -Q “”

    -E is for trusted connection
    We use -h-1 so we don’t have to see header information. Makes it easier to parse the data returned.
    -W removes any trailing spaces.

    I know this was a little late, but I thought this might help anyone.

  18. Pingback: SQL SERVER – What is Piecemeal Restore – Quiz – Puzzle – 22 of 31 « SQL Server Journey with SQL Authority

  19. The multiple restore throws up the following error

    “Msg 3231, Level 16, State 1, Line 1 The media loaded on “D:\projects\SIMS\db\DIMS2Auditcopy.bak” is formatted to support 1 media families, but 2 media families are expected according to the backup device specification. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.”

    for -

    RESTORE DATABASE SIMDB
    FROM DISK = N’D:\db\db1.bak’,
    DISK = N’D:\db\db2.bak’
    GO

    executed from SQL Management Studio.

  20. I have 10 databases. and I have n different different systems which has own SQL-SERVER . All systems may contain any no. of databases from 10 databases.

    I want to write a cmmon query to take backup of those database only which lies in those 10 databases only.

    Ex. databases is A,B, C, D, E, F, G, H

    SYSTEM 1 HAS DATABASE A,B,C AND Z
    SYSTEM 2 HAS DATABASE A, C, D, E, P, Y, Z

    i want to write a query which can take backup only A,B,C or A,C,D,E not P,Y,Z.

    pls help me

  21. Pingback: SQL SERVER – Take Database Backup using SSMS – SQL in Sixty Seconds #037 – Video « SQL Server Journey with SQL Authority

  22. Pingback: SQL SERVER – Restore SQL Database using SSMS – SQL in Sixty Seconds #044 – Video « SQL Server Journey with SQL Authority

  23. can restore different or multiple files *. bak from microsoft magnament studio, from a path example c: \ bakups \; containing different databases. many scrips in internet but none refers to this

  24. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s