| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2005-11-07 : 11:06:55
|
| I read the syntax in BOL for specifying multiple drives in a database backup. I would like to know how this works. My current backup drive is 200GB, with only 10GB free. Until we add another disk to the array, assuming we have room, what would happen if I specify a second DISK location? Would SQL Server use all 200GB on the current drive before using the space on the second drive and do I risk losing data?Thanks, Dave |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-07 : 21:24:40
|
| It wouldn't just use all the space on the current drive. You would have to setup the backup correctly to balance the space. If you have a second disk location available though, why don't you just divide your backups between the locations?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-11-08 : 14:39:58
|
| I ran a quick test and found SQL Server splits the backup in half when two disk locations are specified. What do you mean by 'divide your backups between the locations'? Isn't that what I am doing by specifying a second disk location.Backup Database DBNameTo Disk = 'X:\...\DBName.bak', Disk = 'Z:\...\DBName.bak'Thanks, Dave |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-09 : 01:09:52
|
| were you able to restore the file?which file was picked up first?TIA--------------------keeping it simple... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-09 : 03:25:39
|
quote:
Originally posted by jen
were you able to restore the file?which file was picked up first?TIA--------------------keeping it simple...
Like thisRestore Database DBNameFrom Disk = 'X:\...\DBName.bak',Disk = 'Z:\...\DBName.bak'MadhivananFailing to plan is Planning to fail |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-09 : 03:31:34
|
| but which file was picked up first by sql? and where you able to pull it off successfully?--------------------keeping it simple... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-09 : 03:39:48
|
| Order doesnt seem a matter. I run it successfully in different orderMadhivananFailing to plan is Planning to fail |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-11-09 : 09:57:02
|
| I didn't try to perform the restore, but I suspect one of two things would occur. Either SQL Server would look at the order of the disks specified in the Restore statement as indicated by Madhivanan or it would read the header information written to each disk location to determine the order of the restores. I'm not sure what would happen if NOFORMAT was specified in the backup operation.Dave |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-09 : 21:39:43
|
| so how large is the backup file Dave? and how long did it take to restore the "divided" file?I'm curious coz I am planning to implement the same with a >80GB backup file.TIA--------------------keeping it simple... |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-11-09 : 23:45:50
|
| The backup file is 170GB, but I have not tried to backup the database to multiple files. I wanted to find out how SQL Server handles multiple files before attempting a large backup and restore. I'm thinking about purchasing a tool like Litespeed to see how small it can compress backup file.Dave |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-10 : 00:59:13
|
yes, exactly, on smaller files it may be feasible and ETA may be lower but my worry is on larger filesright now all servers including my dev server are tied up and i can't "experiment" on this large a filehope you can update us?  TIA--------------------keeping it simple... |
|
|
|
|
Backing up large DB to multiple drives???