Sql-server – Copying a restoring database in SQL Server 2012

backupsql serversql-server-2012transaction-log

I've got two SQL Servers, one of these servers (Server A) is backing up transaction logs on some database and uploading them to the other (Server B). Unfortunately I have no access to Server A, I simply have to trust that it is doing its job of periodically uploading its transaction logs to Server B (via something like FTP).

Now, suppose Server B needs to recover the database to do some reading and writing. Doing this will break its ability to receive further transaction log backups.

Is there any way to copy/branch/backup the restoring database, so I can have one version of it that will continue to apply the transaction logs, and one version that will be recovered for reading/writing?

Best Answer

If you want to (1) maintain your log shipping environment and (2) want to have a read/write copy occassionally you will need to maintain 2 databases, TargetDB and Target2DB.

Question, could you just restore the backup from the Server A SourceDB to Server B Target2DB? That might take a little longer, but there are fewer moving parts running all the time.

If read/only access would be sufficient, you can create a database snapshot for reading purposes. This creates a point in time snapshot of the database, supported by the .SNP file that maintains the needed data to support the snapshot, despite other changes coming in.

CREATE DATABASE TargetDB_Read
  ON (NAME = 'datafile', FILENAME = 'F:\TargetDB_Read.SNP')
  AS SNAPSHOT OF TargetDB

Note: You can create several snapshots if needed at different points in time. In that case, name the snapshot for the time it is created. E.g. TargetDB_0800.) See: http://technet.microsoft.com/en-us/library/ms175876.aspx

Of course if you bring Target2DB online so as to do Read/Write, once you are done you will need to restore a Full backup of the SourceDB as the new Target2DB, then catch up with changes through restoring Differential and Log backups until you are once again ready for log shipping.

Related Solutions

Sql Anywhere 11: Restoring incremental backup failure

Summarizing the information linked to in the post above....

The command you have used is for a live backup but what you want is an incremental backup. The approach you want to use (per this blog) uses somewhat different switches. You want to use the -n -t -x switches, and the -o to specify a log file. So the command ends up something like:

dbbackup -n -t -x -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)" -o "c:\backup\backup_log.txt"

Sql-server – Sql Server 2008 R2: Simple recovery model with transaction log backups

Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?

Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.

A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.

Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.

For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.

Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.

Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)

Best Answer

Related Solutions

Sql Anywhere 11: Restoring incremental backup failure

Sql-server – Sql Server 2008 R2: Simple recovery model with transaction log backups

Related Question