Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SQLServer 2000 Backup on Multiple Disks Expand / Collapse
Posted Monday, May 5, 2014 11:58 AM


Group: General Forum Members
Last Login: Monday, November 2, 2015 6:03 AM
Points: 12, Visits: 120
I have one of the database on SQL Server 2000. The database is close to 200 GB. Any single local disks I have are not free enough to accommodate the entire backup. Is there a T-SQL script that I can run to specify the multiple backup file location with size so I can distribute my backup across multiple disks. Or Any other workaround someone wants to suugest? I tried the USB disks , however SQL Server wont use that to save the backup file.


Post #1567655
Posted Monday, May 5, 2014 2:56 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 23, 2016 10:53 AM
Points: 3,657, Visits: 4,015
You can't specify a size for each backup file when backing up to multiple files but you can estimate their size based on the db size. So for a 200GB db spread across 4 files, they should all be roughly 50GB each. For example, if you have 100GB free on the C drive and 50GB free on the D and E drives you could do something like this:

DISK = N'C:\dbname1.bak',
DISK = N'C:\dbname2.bak',
DISK = N'D:\dbname3.bak',
DISK = N'E:\dbname4.bak'

Post #1567715
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse