Forum Discussion
DR Scenario - Restore DB to Azure with Transaction Logs
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
Let 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".
MicrosoftHi 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.
Curious, is there a way to backup that history and restore it first? Assuming not. Since there are only two databases we are concerned with and both around less than a gig in size, doing a full each day seems like the best option.
- bake13
Hi Jeff Harlow --
Yes, you can backup the msdb from one SQL Server instance and restore it to another. The only caveat or thing to keep in mind is that the destination SQL Server instance must be the same version as the source SQL Server instance. Take care.