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 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.
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 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.
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 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 LOG backupthat 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 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 don’t want to have anyone enter data there while we’re migrating to 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 an 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 backupfrom 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 in the beginning of the post, we set out to try and return to our starting instance by using LOG backups alone. I didn’t take 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 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 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.
To wrap up on what we just did. First we migrated our data from one database to another, by using combination of FULL and LOG backups. After that, we performed data movement back to 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 rollback changes to infrastructure and return to starting point.
Naturally it would only work if you were using 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.