Database Stuck at "Restoring" after Backup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Database Stuck at "Restoring" after Backup

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-01-18 : 15:34:21

For a certain database...

The sys.databases.log_reuse_wait = 2, and the sys.databases.log_reuse_wait_desc = LOG_BACKUP

I backed up the database twice, and then I backed up the transaction log. It did change the log_reuse_wait = 0, and the log_reuse_wait_desc = NOTHING

However, to the right of the database in the Object Explorer it now says (Restoring...). That's because, on following the advice of certain posts, I chose the "Backup the tail of the log, and leave the database in the restore state", instead of "Truncate the transaction log". The posts made it seem like this was the best option, with phrases like, "Prevents work loss and keeps the log chain intact". However, I had no idea it was going to leave the database in this perpetual state where nothing can be done to it. So my questin is, how to cancel the "Backup the tail of the log, and leave the database in the restore state". I've tried exiting the SQL management studio, but the restore state persists. Also, it won't allow me to directly edit the "state" and "state_desc" columns in the sys.databases. Would it let me run a regular update query?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-18 : 15:40:39

You received some very bad advice I'm afraid. You've restored the database rather than backed it up like you had intended, and you specified the option to not recover it, thus leaving it in this state. It's waiting for you to recover it. You can run this command to complete it:

RESTORE LOG YourDbName
WITH RECOVERY

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-01-18 : 15:51:59

Thanks very much, Tara. That worked. A lot of bad advice out there, and always hard distinguishing the good from the bad.

quote:
Originally posted by tkizer

You received some very bad advice I'm afraid. You've restored the database rather than backed it up like you had intended, and you specified the option to not recover it, thus leaving it in this state. It's waiting for you to recover it. You can run this command to complete it:

RESTORE LOG YourDbName
WITH RECOVERY

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-18 : 15:52:58

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-01-18 : 15:59:51

I think what you actually did was a tail log backup with the NORECOVERY option, which puts the database into the RESTORING state.

Example:

BACKUP LOG MyDatabase
TO DISK = '\\MyServer\MyFileshare\MyFolder\MyDatabase.trn'
WITH NORECOVERY

You could just recover the database with this command:

RESTORE DATABASE MyDatabase WITH RECOVERY

CODO ERGO SUM

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-01-18 : 16:13:31

I'm now trying to run:

DBCC SHRINKFILE (mydatabase_1, 10);

where the name of the database mentioned above is "Mydatabase" and the name of its transaction log file is "Mydatabase_1". I'm getting this error:

"Msg 8985, Level 16, State 1, Line 1
Could not locate file 'mydatabase_1' for database 'mydatabase' in sys.database_files. The file either does not exist, or was dropped."

I checked sys.database_files - there are only 2 rows, one for master.mdf and one for mastlog.ldf - so the error makes no sense to me. Keep in mind the whole point of my doing this is to try to shrink the physical size of the log file on disk, since they are running out of disk space. But instead I seem to be churning up one mysterious error after another.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-18 : 16:22:26

You're in the master database, hence sys.database_files is returning the files for the master database. Change context to the database you're working with.

Also, please read these:
Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]
Please read through this: [url]http://www.sqlservercentral.com/articles/Transaction+Log/72488/[/url]

--
Gail Shaw
SQL Server MVP

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-01-18 : 16:25:30

Try this script after changing the database name.

use MyDatabase

declare @file_id int

-- Find first transaction log file
select top 1 @file_id = fileid from sysfiles where groupid = 0 order by fileid

select size/128.0 as FileSize, * from sysfiles where @file_id = fileid

dbcc shrinkfile (@file_id,10)

select size/128.0 as FileSize, * from sysfiles where @file_id = fileid

CODO ERGO SUM

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-01-18 : 16:37:03

Well golly gee that worked fine - thanks for the help.

quote:

Originally posted by Michael Valentine Jones

Try this script after changing the database name.

use MyDatabase

declare @file_id int

-- Find first transaction log file
select top 1 @file_id = fileid from sysfiles where groupid = 0 order by fileid

select size/128.0 as FileSize, * from sysfiles where @file_id = fileid

dbcc shrinkfile (@file_id,10)

select size/128.0 as FileSize, * from sysfiles where @file_id = fileid

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources