Running multiple backups at the same time
-
At the present time we schedule the backups of the databases and transaction logs at different time during the night i.e. midnight, 1pm 2pm etc.
As we get more and more databases on some servers, finding a clear time slot for each backup is getting more and more difficult (the Good Lord only put so many hours in the day).
What would happen if we start more than two or more backups at the same time? And how many backups can SQL Server cope with running at any one time i.e. one or two could still be running when a fresh one starts?
Madame Artois
-
Did you ever think of using a maintenance plan? This will run, depending on your selections, all backups in sequential order. I have one that backs up all user databases. This is especially good because as you add new databases, they are automatically included in the plan. You don't have to remember to create two new jobs, one for full backups and the other for your logs.
Terry -
Maintenance plans don't really work for our scenario. The network is backed up by a separate system (Tivoli) which writes to the SQL Server Logs. However this backup can take place at any time as it's not within our control. Tivoli does a differential backup not a full backup plus we are not sure that it backs up the transaction logs. Also Tivoli does not appear to backup master, model or msdb.
Accordingly we set up the backups of master on any server at 12.00, model at 12.15, msdb at 12.30, live databases at 1.00 and so on to ensure that the databases are fine. It also helps when we are trying to work out who backedup what and when.
Since Tivoli can set off at any time, will it interfere with the 'true' SQL Server backups? We have tried to avoid Tivoli backup times (it starts in the early evening).
Madame Artois
-
If Tivoli has the file locked when it's backing up it will cause your SQL backup to fail. Try to avoid any contention with respect to file locks on the server. Why do you not have control of when Tivoli backs up? I would think your DB backups are important enough that you should have some say as to when it runs against your files/servers!
Terry -
DBA in charge/have some say in network backups? Don't go there, Terry, it won't be pretty. You see why we run backups in the early morning; the question is only how many a SQL Server can handle comfortably.
Madame Artois
-
Just a note, if you're backups aren't done, and put to tape, and you have a major crash and cannot restore the data, they are going to look at YOU first, not the network admin. If I were in your shoes, I'd get some support from the CIO or whomever as it'll be your job to explain why you can't restore last night's backup, not the network admin. Anyway, let me come off of the soapbox. I've had 10 backups scheduled at the same time on a SQL server (50+ databases on the server) and never had a problem. Again, that's when I went to a maintenance plan since databases were sometimes created without people letting me know. Then, when backups weren't being performed, the question was always "Why?". Well, if I don't know a new one was created, why would I creat the job(s)?? Maintenance plans can do all user databases, so as one is added, it automatically is included. I realize maintenance plans don't fit well for you but I've never seen an issue with multiple backup jobs running at the same time.
Terry -
Glad to see someone joining us on the soapbox!! Sometimes we feel like John the Baptist ' Lo, there was a voice crying in the wilderness'! Our last chief believed network admin when they said that their backups were the ones he should rely on. At present we don't have a chief.
Anyway I'm glad to say that we haven't got anything like 10 running at the same time on any one server so that is good to know.
We will await our new chief with anticipation.
Madame Artois
-
Here's a handy script I use quite a bit. It might help you analyze your backups v.s. the Tivoli backups. It shows when the backups ran, how long they took, throughput, user, size .....
-- Display ALL backup info w/ elapsed time ... ORDER BY DATE, DATABASE
SELECT a.server_name as 'Server',
a.database_name as 'Database',
convert(varchar(25),a.backup_start_date,100) AS 'Start Date',
convert(varchar(25),a.backup_finish_date,100) AS 'Finish Date',
DATENAME(weekday, a.backup_finish_date) AS 'Day' ,
datediff(minute, a.backup_start_date, a.backup_finish_date) as 'Mins' ,
cast(cast(datediff(minute, a.backup_start_date, a.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours' ,
case
when datediff(minute, a.backup_start_date, a.backup_finish_date) > 0
then cast(ceiling(a.backup_size /1048576) / datediff(minute, a.backup_start_date, a.backup_finish_date) as decimal (8,1))
else 0
end as 'Meg/Min',
ceiling(a.backup_size /1048576) as 'Size Meg' , cast((a.backup_size /1073741824) as decimal (9,2)) as 'Gig', -- div by 1073741824 to get gig
a.user_name,a.backup_size as 'Raw Size'
FROM msdb.dbo.backupset a
join msdb.dbo.backupset b on a.server_name = b.server_name and a.database_name = b.database_name
WHERE a.type = 'D' and b.type = 'D' AND a.backup_start_date > '2006-01-01'
group by a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name
order by a.backup_start_date desc, a.server_name, a.database_name
or ORDER BY DATABASE, DATE
order by a.server_name, a.database_name, a.backup_start_date desc
-
Thanks for that. I'll do some testing and see what I get.
Thanks again for the help (and the soapbox!)
Madame Artois
-
To answer your question, there is nothing wrong with running backups in parallel. If your system is I/O bound you will see a performance hit by doing so.
-
we run veritas and i have changed all the backups to be SQL jobs that use xp_cmdshell to call a batch file that calls the veritas executable with the backup script file as an argument.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply