SQLStarters

Backups, backups, and backups. That’s pretty much my answer to what should be the top 3 priority topics that any DBA needs to understand well. And that’s what we’re going to be looking at this post, backups! Backups play a vital and important role in keeping your data (and in some cases your career) safe, but they are also a great way to migrate data from one location to another.

In this post, we’ll look into migration scenario, how to move data from one instance to another and then back again.

The Scenario

At the office we have a Very Important Database, which in this demo is aptly named VeryImportantDB, and it has an important table, named (drum roll) ImportantTable. We’re asked to migrate this database to a new server, and we decide to accomplish this by taking FULL backup and a set of LOG backups to minimize the downtime.

All seems to go well, but then, unlike in real life, something horrible happens, and we need to return to the previous server. Can we accomplish this quickly and with minimal downtime just by using transaction log backups alone? I’ll tell you right away that the answer is: Yes.

But don’t just take my word for it, read on.

Initial backup

First let’s take a look at the ImportantTable, which holds all our clients, or some other equally important information.

SELECT FirstName
     , LastName
FROM dbo.ImportantTable;

At this point, we’ll run a FULL backup, which we’ll use to begin the migration to a new server.

BACKUP DATABASE VeryImportantDB
TO  DISK = 'C:\Work\Backups\Temp\VeryImportantDBDump.BAK'
WITH FORMAT
   , INIT;

During the day new rows are added to the database, these are not included in the FULL backup which was taken earlier, but they will be in the soon to come LOG backup.

USE [VeryImportantDB];
GO
INSERT INTO [dbo].[ImportantTable]
(
    [FirstName]
  , [LastName]
)
VALUES
('John', 'Doe');
GO
INSERT INTO [dbo].[ImportantTable]
(
    [FirstName]
  , [LastName]
)
VALUES
('Patty', 'Smith');
GO

Periodically, we run a LOG backup that takes care of capturing all the changes we’ve done to our database after the FULL backup.

BACKUP LOG VeryImportantDB
TO  DISK = 'C:\Work\Backups\Temp\VeryImportantDBLog_1.TRN'

Again, more rows are added to the database, and now we’re nearing the previously agreed cutover time for our application.

USE [VeryImportantDB];
GO
INSERT INTO [dbo].[ImportantTable]
(
    [FirstName]
  , [LastName]
)
VALUES
('Simon', 'Templar');
GO
INSERT INTO [dbo].[ImportantTable]
(
    [FirstName]
  , [LastName]
)
VALUES
('Shirley', 'West');
GO

At this point, we’re going to do something that is called a tail-log backup, where we leave the database we’re backing up into a Restoring state. This can be accomplished with the following command. One thing to note here, there can’t be any users in the database, if there are this will fail.

USE master;
GO
BACKUP LOG VeryImportantDBRestored
TO  DISK = 'C:\Work\Backups\Temp\VeryImportantDBLog_2.TRN'
WITH NORECOVERY;

Looking at the database in Management Studio, we can see that it’s now in Restoring state, meaning no one can access it or add any data there. This is pretty important as we want to avoid having anyone enter data there while we’re migrating to a new server.

Restoring the database

In the new instance, we naturally start the process by restoring the initial FULL backup first. This time we’ll put it into Standby mode which allows us to run queries against it without doing full recovery of the database, which would prevent us from restoring further backups.

USE master;
GO
RESTORE DATABASE VeryImportantDBRestore
FROM DISK = 'C:\Work\Backups\Temp\VeryImportantDBDump.BAK'
WITH MOVE 'VeryImportantDBData'
     TO 'C:\Work\Databases\VeryImportantDBRestoredData.MDF'
   , MOVE 'VeryImportantDBLog'
     TO 'C:\Work\Databases\VeryImportantDBRestoredLog.LDF'
   , STANDBY = N'C:\Work\Backups\VeryImportantDB_Standby.BAK';
GO

Again, looking from the Management Studio, we can see that the status of the VeryImportantDBRestore is Standby / Read-Only.

And true enough, once we query it, we can see that there’s only the single row we inserted in the very beginning.

USE VeryImportantDBRestore;
GO
SELECT IT.FirstName
     , IT.LastName
FROM dbo.ImportantTable AS IT;

We can now continue restoring the LOG backups we took previously.

USE master;
GO
RESTORE LOG VeryImportantDBRestore
FROM DISK = 'C:\Work\Backups\Temp\VeryImportantDBLog_1.TRN'
WITH NORECOVERY;
GO
RESTORE LOG VeryImportantDBRestore
FROM DISK = 'C:\Work\Backups\Temp\VeryImportantDBLog_2.TRN'
WITH STANDBY = 'C:\Work\Backups\VeryImportantDB_Standby.BAK';
GO

After applying the latest log, let’s take a one more look at the database contents to see where we’re at.

USE VeryImportantDBRestore;
GO
SELECT DT.FirstName
     , DT.LastName
FROM dbo.ImportantTable AS DT;

And all the data seems to be there.

Currently, neither of the databases is available for full access. We can confirm this by looking at the database states. In the screen capture you a see both of the databases in either Restoring or in the Standby / Read-Only state, this is how they’d appear in Management Studio.

The VeryImportantDBRestore database is now in Standby / Read-Only state. As seen previously, this allows us to both query the database, but also continue with further restores if necessary.

However, we’re now happy with the restores, so we can finalize the migration by recovering the database with the following command.

USE master;
GO
RESTORE DATABASE VeryImportantDBRestore WITH RECOVERY;

And if we look at it now, we can see that the database called VeryImportantDBRestore is in a normal state, and accessible.

But can we go back?

If you remember from the earlier when we took the tail-log backup of the database, we left our original database into Restoring state. This does, in fact, mean exactly that, you can perform restore operations against that database.

So let’s add some rows to our VeryImportantDBRestore database, then take a backup of those and restore them back to VeryImportantDB.

USE VeryImportantDBRestore
GO
INSERT INTO dbo.ImportantTable
(
    FirstName
  , LastName
)
VALUES
(   'Cheryl' -- FirstName - varchar(50)
  , 'Doe' -- LastName - varchar(50)
    )

After adding Cheryl to our ever-growing list of customers, we’ll take a LOG backup from the database. We also want to make sure that no one will add any rows to this database after we’ve taken the LOG backup, so we’ll do another tail-log backup.

 USE master
 GO
BACKUP LOG VeryImportantDBRestore
TO  DISK = 'C:\Work\Backups\Temp\VeryImportantDBRestoreLog_1.TRN'
WITH NORECOVERY;

Now both our databases are in Restoring state.

And then we’re approaching the moment of truth, let’s see what happens when we do this…

USE master
GO
RESTORE LOG VeryImportantDB
FROM DISK = 'C:\Work\Backups\Temp\VeryImportantDBRestoreLog_1.TRN'
WITH STANDBY = 'C:\Work\Backups\VeryImportantDB_Standby.BAK';

It seems to work, so let’s look at the contents of our database.

USE VeryImportantDB;
GO
SELECT FirstName
     , LastName
FROM ImportantTable;

And it does seem like we’ve got the latest addition as well.

Now that we’ve successfully restored our database to the original location, we can finish up and recover the database and continue using it again.

USE master
GO
RESTORE DATABASE VeryImportantDB WITH RECOVERY;

Like I mentioned at the beginning of the post, we set out to try to return to our starting instance by using LOG backups alone. I didn’t take a FULL backup of the migrated database I had already restored, but started straight up with the LOG backup, and it worked.

If you are wondering if the FULL backup would have made the difference here, the answer is: No.

There are few things to that can break up the LOG backup chain. Such as going to SIMPLE recovery model, or some specific options when taking the backup (which seem to be mostly discontinued in later versions of the SQL Server). Log backup chain is a bit too complicated topic to be properly explored in this post, but there are some good articles out there, and I might write something about it myself later on.

Wrapping up

To wrap up on what we just did. First, we migrated our data from one database to another, by using a combination of FULL and LOG backups. Subsequently, we performed data movement back to the original database using just the LOG backup taken from the newly migrated database. This in a real life situation could be useful, if after migration you need to quickly roll back changes to infrastructure and return to the starting point.

Naturally, it would only work if you were using the same SQL Server versions, as it’s impossible to restore backups only from newer/later versions of SQL Server to old one.

Hopefully, you found this post useful.