Few times, we will not have enough space in a single drive to take the database backup. Assume that you have 50GB database, You have 2 drives having free space 30GB each, in this scenario, backup needs to be split into 2 files. Just learned from Pinal's post that we can split the backup using below query.

BACKUP DATABASE AdventureWorks
TO  DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',
    DISK = 'D:\Backup\MultiFile\AdventureWorks2.bak',
    DISK = 'E:\Backup\MultiFile\AdventureWorks3.bak'
GO

We can restore the database using multiple files by using below query

RESTORE DATABASE [AdventureWorks]
FROM    DISK = N'C:\Backup\MultiFile\AdventureWorks1.bak',
    	DISK = N'D:\Backup\MultiFile\AdventureWorks2.bak',
    	DISK = N'E:\Backup\MultiFile\AdventureWorks3.bak' 
GO

Published under: SQL Server Tips ·  ·  ·  ·