smythsit
asked on
Full SQL backup - how many files
Hi Guys,
So here is the current story.
The Sql service can't be bounced until we get a full backup first. It's just too risky
We have completed a log file backup this morning with no issues.
We are going to do a database backup tonight - however the cause of all this mess was that we added a new drive (NAS Storage) to the server to receive the DB backup files as we were out of space on the local drive.
The DB is about 900GB
My plan is to split the DB backup into 30 files - each of 30GB
I have got the plan on the attached spreadsheet.
Is there a limit on the number of backup files we can use?
Does anybody see any reason why this backup plan would not work?
We normally split the backup into files anyway and spread it over the A and B drives in the spreadsheet.
Note that the DB data files are on the drive letters f : u
thanks
EE-Backup-Plan.xlsx
So here is the current story.
The Sql service can't be bounced until we get a full backup first. It's just too risky
We have completed a log file backup this morning with no issues.
We are going to do a database backup tonight - however the cause of all this mess was that we added a new drive (NAS Storage) to the server to receive the DB backup files as we were out of space on the local drive.
The DB is about 900GB
My plan is to split the DB backup into 30 files - each of 30GB
I have got the plan on the attached spreadsheet.
Is there a limit on the number of backup files we can use?
Does anybody see any reason why this backup plan would not work?
We normally split the backup into files anyway and spread it over the A and B drives in the spreadsheet.
Note that the DB data files are on the drive letters f : u
thanks
EE-Backup-Plan.xlsx
EvilPostIt
The maximum i believe is 64 therefore i see no problem with you backing up to 32. This is a very good idea to speed up the backup process. Although you are using SQL Server 2008 which means you can also use backup compression which will also speed up the backup time and reduce the footprint.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi
I think we've found the answer to both issues
http://connect.microsoft.com/SQLServer/feedback/details/428738/nativebackup-spid-get-in-to-suspended-mode-with-backupio-and-backupthread-as-last-wait-type-when-backup-process-get-intrrupted-in-sql-server-2008-rtm
It appears that the DB backup was caused by the above.
The Transaction is closed so a server restart will not cause a lengthy roll forward.
Thanks for all the help - we'll still do the file splitting once the DB is back up and running ok again
I think we've found the answer to both issues
http://connect.microsoft.com/SQLServer/feedback/details/428738/nativebackup-spid-get-in-to-suspended-mode-with-backupio-and-backupthread-as-last-wait-type-when-backup-process-get-intrrupted-in-sql-server-2008-rtm
It appears that the DB backup was caused by the above.
The Transaction is closed so a server restart will not cause a lengthy roll forward.
Thanks for all the help - we'll still do the file splitting once the DB is back up and running ok again