Home / server / Questions / 150333
Accepted
Mike Chamberlain
Asked: 2010-06-11 22:22:52 +0800 CST

Concurrent backups in SQL Server?

  • 772

We currently have our backups managed by a third party company. There are a bunch of agent jobs created that take full backups (4 times a day) and transaction log backups (4 times an hour).

We now want to manage our backups in house, but don't want to disable the third party's jobs until we are sure that we have everything configured correctly internally

So I am proposing to have a short period (say, a couple of days) where backups are being taken both by the old and the new system.

I am wondering what the ramifications of having these two different systems both manage backups, and the potential pitfalls of having backups taken simultaneously. Is this even supported? If so, and bearing in mind that the system can cope with one backup without any noticeable performance degradation, is it fairly logical to assume that it should be able to cope with two simultaneous backups?

Currently the load on the server is fairly light and it rarely struggles.

Any advice is appreciated

sql-server backup

2 Answers

  1. Best Answer

    You will have problems having both backups running together, with the same timing.

    For a start, your transaction logs will be split between two sites, and you will need all of them to recovery back to the last transaction.

    Also, there is a high chance that the backup jobs will clash and this will crash one (or both) of the backup statements, potentially leaving gaps in your backup.

    Recovery will also be more complicated.

    I recommend starting with leaving the existing jobs and just taking daily full backups on your site first, check that these are all OK and then switch the transaction log backups from the third party provider to your backup system.

    Of course, test your recovery scripts and scenarios. I assume your new backup scheme will place the backup files in a safe and secure location, away from the main servers!

    More information: SQL Server backup clashing

    • 4

  2. As an addition to the above post, you could use T-SQL to take a copy only backup that will not disrupt the backup schedule this third party company has in place at present. Depending on the timing of their backup, and just how many (backups) you need to take each day at the start, you could simply do something like:

    BACKUP DATABASE [database_name]
TO DISK = 'E:\Backups\DBName\20100611-whatever-you-call-it.bak'
WITH COPY ONLY
NAME = 'NameYourBackup'
DESCRIPTION = 'BriefDescriptionOfTheDatabase'

    That will create a copy of the database that won't affect the current schedule, and send it to whatever device you're going to store your backups on. Then you can restore the databases to a test/dev server to make sure that they are intact and everything you want is there.

    Using the above code, you can change it to take transaction log backups as well and put the code in steps in a SQL job to be scheduled at a given time.

    More information here: http://msdn.microsoft.com/en-us/library/ms186865.aspx

    • 2