Thursday 6 March 2014

Stripping SQL Server Database Backup to Multiple Files

Stripping Database backup to multiple files and on different drives will make the backup speed faster and will reduce the backup duration.

Check the below sample script for the backup and restore. You can perform the same task using SSMS GUI as well.
Backup Script

   
BACKUP DATABASE [SQLDBPool] TO
DISK = N'C:\JSpace\Backup\SQLDBPool1.bak',
DISK = N'C:\JSpace\Backup\SQLDBPool2.bak',
DISK = N'C:\JSpace\Backup\SQLDBPool3.bak'
WITH NOFORMAT,
NOINIT,
NAME = N'SQLDBPool-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Restore Script

RESTORE DATABASE [SQLDBPool] FROM
DISK = N'C:\JSpace\Backup\SQLDBPool1.bak',
DISK = N'C:\JSpace\Backup\SQLDBPool2.bak',
DISK = N'C:\JSpace\Backup\SQLDBPool3.bak'
WITH  FILE = 1,  NOUNLOAD,  STATS = 10,replace
GO

No comments:

Post a Comment