Sql-server – Backing up multiple databases to the same point in time

backupsql serversql-server-2012

Usually when we start the backup we do not allow changes to commit or database will be not be accessible. I mean database will be in single user mode but I want to start the backup and release the db for use. Also, once I start the backup I do not want the ongoing changes to be written to the backup file.
I want to know how I can achieve this in Microsoft SQL Server 2012.Please assist me.

Well, let me explain my problem first. Currently I am setting the database to single user mode until the backup completes. This mode will serve my purpose of avoiding the data changes when backup is in progress. But my application is tied with the multiple databases(each databases are interlinked with each other and there are var dbs which keeps creating on monthly basis). So backing up all these databases has become tedious process and more importantly I have to keep the users out of the system when backup is in progress.

So I am looking for backup mechanism which will meet below mentioned requirements.

  1. Start the backup of all the dbs at a time and release the db for use.
  2. As databases are inter linked to each other I want to have data consistency maintained in backup files. So because of this data consistency requirement I don't want on going changes to be committed to my backup file.

All I want is – backup of all the dbs at a given time.

Best Answer

I am assuming that your task is to create a set of backups where you can restore all of them to a single static (no open transactions) point (all in sync). Similar to what a quiesce point will do for you in DB2. Unfortunately there is nothing in SQL Server that does exactly that. However, assuming your databases are in FULL recovery mode, you can do something similar.

You can create a single MARKED transaction across all of the databases at once. Then you take a transaction log backup of each database. Assuming you are keeping up with your log backups (which you should be anyway) log backups don't take very long. And there is no need to stop anyone from being in the system (except maybe long enough for you to create your marked transaction).

At this point if you need to do a restore then you do a RESTORE STOPBEFOREMARK on each of your databases. This will restore all of the databases to the same point (based on your transaction). Note: There is also a STOPATMARK if you want to include that transaction.

If you want an example I've got one in the link above along with quite a bit of additional reading.

I realize this doesn't exactly answer your question but hopefully it solves your problem.

Related Question