9

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.

  • 2
    You don't have to set your database to single-user mode when you take backups. SQL Server will never include uncommitted transactions in your backup. For a more complete answer, please edit your post to include details of your recovery model, whether you mean transaction log backups, full or incremental backups, as well as what problem you are experiencing. As it stands your question is a bit unclear. – Daniel Hutmacher Mar 20 '16 at 16:03
  • 1
    What do you want to achieve by this backup? Full recoverability to this point - or something else? In other words... If you have to restore from this backup, what should be restored --- any DDL changes? Changes to data, or what? – seventyeightist Mar 20 '16 at 16:24
  • What you want is not possible in SQL 2012. Back in the SQL 7.0 days, the database backup set was used to re-create the database as it was at the time the BACKUP statement started. Starting in SQL 2000 and onward, a database backup set is used to re-create the database as it was at the time the BACKUP statement completed. – stacylaray Mar 21 '16 at 6:44
  • @DanielHutmacher I am looking for full backups. – Praveen Mar 24 '16 at 9:38
13

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.

  • 2
    This is how BizTalk manages point-in-time backups of multiple databases. If it works for Microsoft BizTalk, it's pretty much a well-tested feature. – Max Vernon Mar 23 '16 at 17:29
  • We use it for TFS databases in logshipping we changed to alwaysOn. – Kin Shah Mar 23 '16 at 17:50
  • 1
    One (implied but unsaid) detail to drive Ken's answer home: You cannot have a FULL backup consistent across multiple DBs. The closest you can come is to take a round of full backups, immediately followed by a marked transaction, then a round of log backups. That first round of log backups would "true up" the consistency to the point in time. – AMtwo Mar 23 '16 at 19:47
  • @AMtwo technically you could by using the OPs method. Lock everyone out of the system, make sure there are no active connections (maybe mark the databases as read only/restricted), then take full backups of every database. However as the OP stated it's not the best method in the world. – Kenneth Fisher Mar 23 '16 at 19:50
7
+100

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.

-1
--]---- 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/

  • This does not guarantee same point of time backups. It's merely a sequence of consecutive backups. – vonPryz Jan 30 '17 at 11:33

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.