Backup and Restore and Split File Backup

1) Conventional and Split File Backup and Restore
BACKUP DATABASE demo
TO DISK = 'C:\demo1.bak',
DISK = 'C:\demo2.bak',
DISK = 'C:\demo3.bak'
GO

RESTORE DATABASE weather
FROM DISK = 'C:\demo1.bak',
DISK = 'C:\demo2.bak',
DISK = 'C:\demo3.bak'
GO

2) 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.


taken by:- http://dotnetslackers.com/articles/sql/Mirrored-Backup-and-Restore-and-Split-File-Backup.aspx