Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Database went into Restoring State for unknown reason Expand / Collapse
Author
Message
Posted Wednesday, November 06, 2013 11:32 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
I have a Database that went into Restoring State for unknown reason.

I had recovered it this morning. Then I performed a Full and Transaction Log Backup.

I treid restoring to a new database but I get errors that the device does not exist.

I specified a Database with a Restore Sufix for now for proof of concept.

Could I do the following?

1.Stop the service (MSSQLSERVER);
2.Rename or delete the Database and Log files (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data...) or wherever you have the files;
3.Start the service (MSSQLSERVER);
4.Delete the database with problem;
5.Restore the database again.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1511968
Posted Wednesday, November 06, 2013 1:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:13 PM
Points: 138, Visits: 254
I would run a DBCC CHECKDB against the restored copy (probably off hours, as this will hit the disk pretty hard), just to be sure everything is correct with the restored copy. I would be much more concerned over the root cause of the database going to a restoring state. Without knowing that, this particular database could surprise you at just about any time.
Post #1512000
Posted Wednesday, November 06, 2013 2:09 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 41,559, Visits: 34,482
Restoring or recovering?

A DB will go restoring if someone runs BACKUP LOG ... WITH NORECOVERY or runs a restore over the database and specifies NORECOVERY. In either case running RESTORE DATABASE <db name> WITH RECOVERY will bring it back online. Can also happen if someone starts a restore and stops the restore part way through (perhaps realising they were restoring over the wrong DB)

A database will go into the recovering state if SQL is restarted and that DB was not cleanly shut down. It's a temporary state, once recovery has completed the DB will come online.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1512003
Posted Wednesday, November 06, 2013 3:20 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
GilaMonster (11/6/2013)
Restoring or recovering?

A DB will go restoring if someone runs BACKUP LOG ... WITH NORECOVERY or runs a restore over the database and specifies NORECOVERY. In either case running RESTORE DATABASE <db name> WITH RECOVERY will bring it back online. Can also happen if someone starts a restore and stops the restore part way through (perhaps realising they were restoring over the wrong DB)

A database will go into the recovering state if SQL is restarted and that DB was not cleanly shut down. It's a temporary state, once recovery has completed the DB will come online.


I went into a restoring state.

I was able to restore the database.

Thank you.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1512022
Posted Wednesday, November 06, 2013 10:03 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 41,559, Visits: 34,482
Then check jobs, maintenance plans, make sure you don't have something trying to do a tail-log backup (backup with norecovery) and that no one restored the wrong DB (error log and backup/restore history will help there)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1512081
Posted Thursday, November 07, 2013 4:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 14,836, Visits: 27,312
GilaMonster (11/6/2013)
Restoring or recovering?

A DB will go restoring if someone runs BACKUP LOG ... WITH NORECOVERY ....


In what might be an epic role reversal, don't you mean RESTORE LOG etc.?



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1512182
Posted Thursday, November 07, 2013 4:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 41,559, Visits: 34,482
Grant Fritchey (11/7/2013)
GilaMonster (11/6/2013)
Restoring or recovering?

A DB will go restoring if someone runs BACKUP LOG ... WITH NORECOVERY ....


In what might be an epic role reversal, don't you mean RESTORE LOG etc.?


No, I really do mean backup.

If you try a RESTORE LOG on a database that's online, all you'll get is an error (no files are ready to roll forward), the database state won't change. If you do a tail log backup (BACKUP LOG ... WITH NORECOVERY) then you're telling SQL you want to take a log backup and ensure that no more transactions can be done after the backup (it's done prior to starting a restore or moving a database). It will then do the backup (to DISK or whereever else) and once the backup completes the database will switch into the restoring state. This ensures that the log backup captured the very last transactions that ran so that none are lost.

The problem is, both the backup wizard and the maintenance plan backup task have the option 'back up the tail of the log' in them, if that's selected erroneously (especially in a maintenance plan), then the database will appear to switch into RESTORING for no good reason.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1512185
Posted Thursday, November 07, 2013 10:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:29 PM
Points: 32,819, Visits: 14,965
GilaMonster (11/7/2013)

No, I really do mean backup.

If you try a RESTORE LOG on a database that's online, all you'll get is an error (no files are ready to roll forward), the database state won't change. If you do a tail log backup (BACKUP LOG ... WITH NORECOVERY) then you're telling SQL you want to take a log backup and ensure that no more transactions can be done after the backup (it's done prior to starting a restore or moving a database). It will then do the backup (to DISK or whereever else) and once the backup completes the database will switch into the restoring state. This ensures that the log backup captured the very last transactions that ran so that none are lost.

The problem is, both the backup wizard and the maintenance plan backup task have the option 'back up the tail of the log' in them, if that's selected erroneously (especially in a maintenance plan), then the database will appear to switch into RESTORING for no good reason.



Wow, did not know this. I was about to ask what Grant did.

And yet, it does cause it to go into restoring:
CREATE DATABASE MyRestoreTest
;
GO
BACKUP DATABASE MyRestoreTest TO DISK ='myrestoretest.bak';
GO
CREATE TABLE mytable( id INT)
;
GO
BACKUP LOG myrestoretest TO DISK = 'myrestoretest_log.trn' WITH norecovery

and I can get things back.

RESTORE DATABASE myrestoretest WITH recovery







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1512366
Posted Thursday, November 07, 2013 12:13 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
I believe you can also use BACKUP DATABASE ... WITH NORECOVERY; and it does the same thing. Very good for taking a last backup when migrating to a new system.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1512410
Posted Tuesday, November 12, 2013 4:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 3:32 PM
Points: 205, Visits: 830
But for "BACKUP LOG DB_Name...........WITH NORECOVERY" to run successfully, SQL server needs exclusive access on the respective database right? If there's any active session in the Database, it won't let you take log backup with norecovery and fails with below error and shouldn't go to "RESTORING" state. Please correct me If I am wrong...

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Post #1513654
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse