Forum Discussion
DR Scenario - Restore DB to Azure with Transaction Logs
I am working on a DR Scenario (test) where I need to restore an active DB on-premise to Azure. In testing, I need to be able to restore the database including up to the minute (15 min) transaction logs. When doing a restore, I can only select the full backup file to restore. It never grabs all of the TRN files as I would expect. What step I am missing? All of the Backup files including the TRN files are in the same folder. I know doing a full backup and restore is working by grabbing just the Backup file but in a DR scenario, there could be days of transaction logs that would also need to be included in that restore.
Hi Jeff Harlow --
Are you leaving the database in NORECOVERY mode after the initial restore of the FULL backup? Setting the database to NORECOVERY allows additional differential and/or transaction log backups to be applied to the database, thereby facilitating point-in-time recovery. Below is a quick example using TSQL. Take care.
RESTORE DATABASE [TestDatabase01] FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\TestDatabase.BAK' WITH NORECOVERY RESTORE LOG [TestDatabase01] FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\TestDatabase.TRN' WITH RECOVERY
5 Replies
bake13Microsoft
Hi Jeff Harlow --
Are you leaving the database in NORECOVERY mode after the initial restore of the FULL backup? Setting the database to NORECOVERY allows additional differential and/or transaction log backups to be applied to the database, thereby facilitating point-in-time recovery. Below is a quick example using TSQL. Take care.
RESTORE DATABASE [TestDatabase01] FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\TestDatabase.BAK' WITH NORECOVERY RESTORE LOG [TestDatabase01] FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\TestDatabase.TRN' WITH RECOVERYLet me clarify this a bit since I have determine the issue. When I go to restore a database on the existing server; select database (versus file); I can restore the database from a full backup plus select all of the transaction logs up to the point of failure (even it there dozens). When I do the same from a file, I have to first restore the BAK (turning off recovery mode, as you mentioned) and then restore each transaction log one at a time. I see there are scripts to get around this, but seems odd that Microsoft did not provide the same user friendly solution as a database restore. When one thinks about a DR scenario, often you are restoring a database to a new server (floods, tornados, hurricanes, fire, etc.). Restoring a DB to a new server in that type of scenario, should have the same experience in my opinion. I ran several different tests and it does seem like the script method is my only option. We were doing a full backup once a week and relying on transaction logs in between. I have since changed this to full backups twice a day, so I dont have hundreds of TRN files to have to "import".
- bake13
Hi Jeff Harlow --
When you are restoring a database on the existing server, the backup history from the msdb database is being used to generate the list of full and transaction log backups needed to restore a database to a point in time.
On a brand new server, like a DR server, the msdb database will contain no information regarding full and transaction log backups for a database that occurred on another server.
I'd also recommend considering the use of differential backups in conjunction with the full and transaction log backups to reduce both the number of full backups you are taking and the number of transaction log backups necessary to recover a database. Take care.
