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

Sometime we may come across the problem of not having sufficient space in a single drive during Database backup. Suppose we have 2 drive in system. The drive C has 10 GB Space and drive D has also 10 GB.

Now we want to take backup of a Database which is over 10 GB. So we cannot take backup on any single drive. So in this case we can use striped backup. Striped backup is easy to use and can be done using SSMS and also from TSQL Query.

Open SSMS. Right click on Database Name. Click on Task > Back up….

Now a dialogue box opens for backup. Here we have added 2 backup files (C:\Backup1.bak and D:\Backrup2.bak). It is form of striped backup. So total size of backup will be divide into two parts.

Now during restore we have to select both file to restore.

We can do striped backup from TSQL query also:

BACKUP DATABASE VBDotNet TO   

DISK = N'C:\Backup1.BAK',   

DISK = N'D:\Backup1.BAK',   

WITH NOINIT,   

NAME = N'Database Backup',  

SKIP, NOREWIND, NOUNLOAD,  STATS = 10 

GO 

The above script will create 3 backup files. It will be dividing total size into 3 different files. Now during restore you must have all the files. If any of the file is missing, you cannot restore the database. Below is the TSQL Query to restore Database:

RESTORE DATABASE VBDotNet  

FROM  DISK = N'C:\Backup1.BAK',   

DISK = N'D:\Backup2.BAK' WITH FILE = 1,   

MOVE N'VBDotNet' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VBDotNet.mdf',   

MOVE N'VBDotNet_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VBDotNet.ldf',   

NOUNLOAD,  STATS = 10 

GO