Problem
How to take backup in Multiple files (Or you can say how to split backup file in multiple backup files)?
Solution
Advantage:
1. We can store backup on multiple drive if disk space is the issue
2. Easy to transfer on network servers
T-SQL for Backup
BACKUP DATABASE [Northwind] TO
DISK = ‘C:\Northwind_file1.bak’,
DISK = ‘D:\Northwind_file2.bak’,
DISK = ‘E:\Northwind_file3.bak’
WITH INIT , NOUNLOAD , NAME = ‘Northwind backup’, NOSKIP , STATS = 10, NOFORMAT
T-SQL for Restore
RESTORE DATABASE [northwind] FROM
DISK = N’C:\Northwind_file1.bak’,
DISK = N’D:\Northwind_file2.bak’,
DISK = N’E:\Northwind_file3.bak’ WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , REPLACE
Thanks JShan, Restore using above script worked well for me
Fantastic it’s working Fine but how we can change the DB Backups File name at run time. so we schedule as normal job