March 24, 2016 at 3:51 am
Good morning
I have a databases at 1.15TB but the Log file is 184GB. So i need space for 966GB
1MDF file and 28 NDF files.
I have to backup using a SQL script but i want to split the load of the backups over a few drives. I have one drive which is 560GB free, 310GB and one at 120GB
with 28ndf files. i want to specifically say which nfd files go one to which drive. I need to backup the database
The script i am using is. Taken out names for security purposes. Its a Mirrored databases Asynchronous. So need sot be Copy only.
BACKUP DATABASE DB TO DISK ='L:\Backup\\_1.bak'
, DISK ='L:\Backup\\.bak'
, DISK ='L:\Backup\\3.bak'
, DISK ='L:\Backup\\4.bak'
, DISK ='L:\Backup\\5.bak'
, DISK ='L:\Backup\\6.bak'
, DISK ='L:\Backup\\7.bak'
, DISK ='L:\Backup\\8.bak'
, DISK ='L:\Backup\\9.bak'
, DISK ='L:\Backup\\10.bak'
, DISK ='L:\Backup\\11.bak'
, DISK ='L:\Backup\\12.bak'
, DISK ='L:\Backup\\13.bak'
, DISK ='L:\Backup\\14.bak'
, DISK ='L:\Backup\\15.bak'
, DISK ='L:\Backup\\16.bak'
, DISK ='L:\Backup\\17.bak'
, DISK ='L:\Backup\\18.bak'
, DISK ='L:\Backup\\19.bak'
, DISK ='L:\Backup\\20.bak'
, DISK ='L:\Backup\\21.bak'
, DISK ='L:\Backup\\22.bak'
, DISK ='L:\Backup\\23.bak'
, DISK ='L:\Backup\\24.bak'
, DISK ='L:\Backup\\25.bak'
, DISK ='L:\Backup\\26.bak'
, DISK ='L:\Backup\\27.bak'
, DISK ='L:\Backup\\28.bak'
, DISK ='L:\Backup\\29.bak'
WITH COPY_ONLY,
STATS = 1
many thanks
March 24, 2016 at 4:02 am
When you back up a database, you back up the entire database. How the backup is written doesn't depend on the file/filegroup structure of the DB.
By striping the backup (as you've done), you'll get the backup file spread evenly across 29 files. It's not one filegroup per backup file. It's the entire DB backed up and spread across the files.
When you restore, you specify where each file goes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2016 at 4:18 am
GilaMonster
Thanks for the help. If i use the backup section on the DB. then do files and filegroups backup process. then Surly this will allow me to do individual backups for each ndf files. this way i can copy to where i like. then i can add it to the external encrypted drive.
is this a good idea?
many thanks Sean
March 24, 2016 at 4:49 am
Not a particularly good idea, as the restore process is more complicated when you do file and filegroup backups. You'll need to take log backups as well and restore those or you won't have a usable DB at the end.
Besides, what does it matter?
If you want to split the files over drives for space reasons, use the striped backups like you have. You can put multiple stripes on a drive for space reasons.
For example, if the full backup is 1TB and you stripe over 10 files, then each file will be about 100GB. If you have one drive of 600GB, one of 300 and one of 200, then you put more of the stripes on the first drive and fewer on the other two.
Oh, and a backup isn't just the data files. There will be some portion of the log in it too, so you must account for that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2016 at 5:15 am
Ok i shall do that.
thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply