Monday, 25 February 2013

Mirrored Backup and Split File Backup

1. Conventional one-file backup-

BACKUP DATABASE [hhhh] 
TO  DISK =  N'D:\DBA-HIMALAYA-PC$SQL2008R2\AAA\hhhh.bak' 
GO

2. 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 [hhhh]
TO DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\A.bak',
DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\B.bak',
DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\C.bak'
GO


RESTORE DATABASE [hhhh]
FROM DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\A.bak',
DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\B.bak',
DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\C.bak'
GO







It's a common practice to create an exact copy of the backup and store it to several places.
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.




BACKUP DATABASE [hhhh_new] TO  DISK = N'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\hhh.bak'
mirror to DISK = N'C:\DBA-HIMALAYA-PC$SQL2008R2\AAA\hhh.bak'
WITH FORMAT, INIT,  NAME = N'hhhh_new', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO









No comments:

Post a Comment