June 13, 2013 at 11:37 am
We are using SAN Snapshots as a quick way to recovery our SQL databases. We are taking nightly full backups using DPM, however on a 1TB database, a restore will take hours. We also take log backups every 30 minutes which are shipped off server. We are using SQL 2012 SP1.
If we had to use a 2 hour old SAN snaphot to recover our data drive for something like complete logical disk corruption, how could we roll the database forward using the transaction log backups and the LDF file after the snapshot was presented to the server? Our SAN does not have the ability to mount the database in a NORECOVERY state, so the database comes online just fine, but the data is simply 2 hours old.
June 13, 2013 at 11:41 am
if the database is restored in a recovered state there is no way to use the transaction logs to roll it forward from that point.
---------------------------------------------------------------------
June 13, 2013 at 11:57 am
I thought in 2008 R2 you could use the command RESTORE DATABASE db WITH NORECOVERY, and the database would flip back to a recovering state. Is that not the case in 2012?
June 13, 2013 at 12:04 pm
You can do that, in any version of SQL. If you do it and then try to restore logs you'll get an LSN mismatch error and the restore will fail.
Once a database has been recovered, there is no way to use the transaction log backups to roll it forward from that point.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2013 at 12:06 pm
nope, you can restore db with recovery to bring it online without restoring any further files, but you cannot reverse that with the norecovery clause.
Using norecovery you need to supply a backup file, so you would have to restore from snapshot, restore a SQL full backup with norecovery, restore your chain of log backups with norecovery except use recovery on the last log restore.
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply