Sql-server – Change the database from restoring state to standby mode

restoresql serversql-server-2012

I have a SQL Server 2012 database that must be always in 'standby' mode because I must restore transaction logs from a vendor database to my local database on stand by restoring option.

It has been around 3 months since I started to download log backups from vendor's sFTP server and restore everyday to my database (in standby mode). Today, in the middle of restoring, SSMS crashed and the application closed immediately, which left the database in restoring mode.

I don't want to work all over again from the beginning. How can I change the database to standby mode and keep the sequence of LSNs; from the last LSN restored to next LSN of log backup file?

My database has no log shipping configuration between two servers, it is just a target database.

Best Answer

The way I solved is (in case it helps others): I first identified the last log file restored from the database properties (shows by date) and continued to restore on standby mode starting from the last log file then works perfect!

Related Solutions

Sql-server – Error restoring SQL differential database files

To check the LSN number from the backup file do:

restore headeronly from disk='g:\backups\name_of_backup_file.bak'

The result set will have a column which will show you the last LSN number. As gbn said, to restore from a diff backup you need a full + last diff backup from the same backup set.

Sql-server – Read only/Standby database – Set user permissions

The user has to be granted permissions in the source copy of the database.

The restored copy is just that -- a copy -- of the source, so there's no way to change one and not the other.

Since this is a SQL Server login, you'll need to create the logins on both servers with identical SIDs so the mapping from the database user to the login matches in all cases. You can do this by recreating the login on the secondary, specifying the SID manually in the CREATE LOGIN statement.

If you went with a solution such as replication (not saying you should, just giving an example), this works using two read/write databases, where you could grant permissions completely independently.

Best Answer

Related Solutions

Sql-server – Error restoring SQL differential database files

Sql-server – Read only/Standby database – Set user permissions

Related Question