Backup and Restore with Split and Mirror

Backup and Restore your SQL Database

You can take the backup of your database by few line of t-sql like the one below

BACKUP DATABASE PracticeDatabase TO

DISK = N'D:\PracticeDatabase.bak'

, DISK = N'D:\try\PracticeDatabase.bak'

WITH FORMAT

NAME = N'PracticeDatabase-Full Database Backup'

GO

WITH FORMAT : means if there exist any backup with the name u defined in the query then it will delete/format that backup.

How to Split your database backup

There are situation where you want to split your database backup fine and want one file to stored in say d drive and another in e drive. You can do this very easily. Look at the code below

BACKUP DATABASE PracticeDatabase TO

DISK = N'D:\PracticeDatabase.bak'

, DISK = N'D:\try\PracticeDatabase.bak'

WITH FORMAT

NAME = N'PracticeDatabase-Full Database Backup'

GO

Here the backup is not going to stored in two location instead the whole backup get divided into two parts. One part get stored in D:/ and another will in D:/try

What is mirror backup and how can you take this?

Mirror backup is nothing but taking the same backup in two different locations.

BACKUP DATABASE [PracticeDatabase] TO
DISK = N'D:\PracticeDatabase.bak'
MIRROR TO DISK = N'D:\try\PracticeDatabase.bak'
WITH FORMAT
NAME = N'PracticeDatabase-Full Database Backup'

How to restore database

Restoring a database is very simple you just need to write few line of sql statement specifying the database name where you want to restore your database and the path where the backup file is lying.

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

Here we are restoring our database backup file at TempPractice database. And the backup file is lying at C:\Backup\SingleFile\.

How to restore Split database

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 [TempPractice]
FROM DISK = N'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks3.bak'
GO

Here the database AdventureWork split into three part AdventureWork 1, AdventureWork 2, AdventureWork 3.  So to restore such split database you need to specify each and every split backup file in Disk option.    

Post Reference: Vikram Aristocratic Elfin Share