0

I have a 2-node clustered Lab SQL Server and I need to restore the database to the previous day state. This is my first time to try a restore in a cluster. I have a full recovery model and since this environment is rarely used, I have a once a week full backup complemented by 2 differential backups and daily log backups; i.e. Full=Mondays at 1am, Diff=Wed and Fri at 1am, and Log=daily at 9pm.

Now my questions is - if I wish to restore the database on the Monday state, can I simply restore the full backup on Monday (1am), then restore log backup on same day (9pm), then recover the database?

Do I have to worry about restoring any of the master, model or msdb system databases?

Thanks in advance.

Regards, Trev

1

There isn't a difference between a restore on a standalone instance and one on a Failover Cluster Instance. There IS a difference if it is in an Availability Group.

Yes you can restore the FULL from Monday and the LOG from 9pm.

Whether or not you have to restore the system databases is dependent upon what you are needing to recover. If it's just data in the user database, then simply restoring the user database is sufficient.

  • Thank you Tara. I was able to restore the database successfully though I had to work a little harder as I encountered 2 stop errors during the restore. Prior to restore, I did a copy_only backup then do a restore verifyonly to boost my confidence. Error-1: Msg 3101, Level 16, State 1, Line 3 Exclusive access could not be obtained because the database is in use. This was corrected by alter... set single_user and rolling back uncommitted transactions. ...continued on next comment – trevzeuq Sep 14 '16 at 13:30
  • Correcting Error No.1 somehow contributed to error no. 2 which said: Msg 3159, Level 16, State 1, Line 1 The tail of the log for the database "dbname" has not been backed up... This was fixed by adding replace on the restore sequence. Thus my TSQL start was RESTORE DATABASE <db-name>FROM DISK = 'file-location' WITH REPLACE, NORECOVERY; and so on with the log file and the final recovery line. And for people who will be on the same boat, don't forget to execute this command at the end: alter... set multi_user – trevzeuq Sep 14 '16 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.