Backing up multiple databases to the same point in time

Question

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.

Answer 1

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.

Answer 2

Your question is similiar to the one that I have answered - Backing up & restoring 10-20 SQL Server databases to a ~synchronous state?

Assuming your database recovery model is FULL, you should take a Full backup and then use Marked Transactions.

Lets debunk your myths ...

Start the backup of all the dbs at a time and release the db for use.

From backup myths (Highly advise you to read all the backup Myths):

Backup operations do not take locks on user objects. Backups do cause a really heavy read load on the I/O subsystem so it might look like the workload is being blocked, but it isn't really. It's just being slowed down. There's a special case where a backup that has to pick up bulk-logged extents will take a file lock which could block a checkpoint operation – but DML is never blocked.

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.

marked Transactions will help you take consistent LOG backup and you will be able to restore them using RESTORE ... WITH STOPBEFOREMARK = '<mark_name>'

Remember that Marked transactions for all databases on the instance are also recorded in the dbo.logmarkhistory table in the MSDB database.

Answer 3

--]---- Begin Code ----[
--–declaring variables
DECLARE @dbname VARCHAR(100) -- database name
DECLARE @bupath VARCHAR(100) -- path for backup location
DECLARE @filename VARCHAR(100) -- filename used for backup files
DECLARE @datestamp VARCHAR(25) -- date used for backup file timestamp
-- specify database backup directory
SET @bupath = 'D:\DB Bkp\'
-- file date formatting
SELECT @datestamp = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE
(CONVERT(VARCHAR(20),GETDATE(),108),':','')
--specify databases to backup
DECLARE db_cursor CURSOR for
SELECT name
FROM master.dbo.sysdatabases
WHERE name not IN ('tempdb') -- excludes these databases, if excluding multiple databases, seprate them by a comma
--backup process
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @filename = @bupath + @dbname + '_' + @datestamp + '.bak'
   BACKUP DATABASE @dbname TO DISK = @filename WITH INIT,
   CHECKSUM; --init overwrites existing files with the same name, and checksum verifies the backup
       FETCH NEXT from db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

for more details: https://blogs.technet.microsoft.com/letsdothis/2013/11/06/backup-multiple-sql-databases-in-a-single-swoop/