I would like to create a backup of four databases on one SQL Server 2000 instance at the exact same moment in time.

How can I achieve it?

Creating different jobs with the same schedule date and time does apparently produces a difference in the moment when the backup is taken.

  • 1
    Do you need the backups to be taken at exactly the same time or do you need to restore them to the exact same point in time? – Tom V - try topanswers.xyz Sep 29 '16 at 18:26
  • As the person answering noted, you will need to take a backup as they are without them being in sync, then do a 'point in time' restore using the transaction logs on a different instance or name. Doing that lets you state exactly at what time you want the restore to finish. Do note this does not guarantee cross database transactions unless they were written that way but in this case it probably doesn't matter. – Ali Razeghi Sep 29 '16 at 19:00

You will need to have the Full Recovery Model enabled on all four databases.

Now you will issue a full backup on all four databases, let's say ten minutes before the point in time you require.

Now you will do a transaction log backup of each database, at a point in time immediately after the exact moment you need.

On the other end, you will restore each full backup WITH NORECOVERY, and then transaction log backup, with the RESTORE LOG WITH STOPAT switch to specify the exact moment in time you need, as a DATETIME.

Remember to RESTORE WITH RECOVERY at the end to bring them into active use.


If you want the backups to start at the same time, this would do it. Create a batch file that contains something like the following and run it manually or run it through the SQL Agent (osql for SQL 2000).

start osql -E -Q "backup database master to disk = 'c:\temp\master.bak'"
start osql -E -Q "backup database model  to disk = 'c:\temp\msdb.bak'"
start osql -E -Q "backup database msdb   to disk = 'c:\temp\msdb.bak'"

You might ask, why not just paste this in a SQL Agent command job? SQL seems to only allow one command to run and no more. I haven't tried this with SQL 2000 in a while though--it might work on that version.

  • II've rescinded the downvote because the question isn't clear. Sorry about that. – Randolph West Sep 29 '16 at 21:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.