Database Simplified Headline Animator

Database Simplified

Saturday 8 October 2011

Striped Backup of Database In SQL Server, Taking Database Backup In Different Parts

This blog describes resolution to the problem generally faced when we come across the problem of not having space in a drive to take full backup of database. for example if you have 3 drive in your machine with following free space in each drive.

  1. C:\ ( 4 GB Free)
  2. D:\  (5 GB Free)
  3. E:\ (6 GB Free)

Now you want to take backup of database which comes around 10 GB in Size which cannot be taken in any of the drive mentioned above. So In this case you can use striped backup, Striped backup are easy to use. Striped Backup can be done using SSMS (SQL Server Management Studio) Or T-SQL Query. See Following Examples Of Taking Striped Backup Of Database.

Kuldeep Bisht

As you can see above its Database Backup Dialogue. Here the Important thing is we have added 3 backup files ( highlighted above) which is form of striped backup so total size of database backup is divided into three different parts. Taking Striped backup from SSMS GUI can be bit misleading too as you might be aware that when you take backup from SSMS GUI then that backup path is saved and when you open SSMS GUI Backup Dialogue next time then that path is automatically shown in the list. Suppose you don’t remove that path from that list and you add your new backup file path to list. So Your List may Look Like Above list. If you ask a user who is not aware of Striped Backup then user will typically tell your full backup will go to E:\STRIP2.BAK (because it is selected). and user will not bother about D:\STRIP1.Bak and C:\STRIP3.BAK and finally losses that data.

Okay back to Topic Now.

Following Script can be used to take striped backup of a database.



BACKUP DATABASE [TestDB] TO 
DISK = N'D:\STRIP1.BAK', 
DISK = N'C:\STRIP2.BAK', 
DISK = N'E:\STRIP3.BAK'
WITH NOINIT, 
NAME = N'Helo-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

So Above script create 3 backup files dividing total size into 3 different files. You must have all the files while restoring the database. if any of the the file has been lost  then you cannot restore the database. To restore database see following



RESTORE DATABASE [STIPCHECK]
FROM  DISK = N'D:\STRIP1.BAK', 
DISK = N'C:\STRIP2.BAK', 
DISK = N'E:\STRIP3.BAK' WITH  FILE = 1, 
MOVE N'TestDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\STIPCHECK.mdf', 
MOVE N'TestDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\STIPCHECK_1.ldf', 
NOUNLOAD,  STATS = 10
GO

So Stripped backup is nice option you run out of space. You can take your backup in chunks.

No comments:

Post a Comment