Database State is staying at Restoring status

2014-08-18

It's one of solutions...

--Check DB state
select state_desc,name,user_access_desc from sys.databases where name='xxxxx'


Switch to Single User
--Alter DB to  Single Mode
ALTER DATABASE [xxxxx] SET SINGLE_USER
/*
Msg 5052, Level 16, State 1, Line 1
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
*/


--Alter DB to Emergency
ALTER DATABASE [xxxx] SET EMERGENCY
/*
Msg 5052, Level 16, State 1, Line 1
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
*/

Switch to Online from Restoring
--Alter DB state
ALTER DATABASE [xxxxx] SET ONLINE
/*
Msg 5052, Level 16, State 1, Line 1
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
*/

Because I did not do a "CHECK DB"??
--Check DB
DBCC CHECKDB ([xxxxx],REPAIR_ALLOW_DATA_LOSS)
/*
Msg 927, Level 14, State 1, Line 1
Database 'xxxxx' cannot be opened. It is in the middle of a restore.
*/

Still Failed....
OK, let me restore it again...
--Restore DB again
RESTORE DATABASE [xxxx] FROM  DISK = N'\\xxxx\backup$\xxxx.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO
/*
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 23848 pages for database 'xxxx', file 'xxxx_Data' on file 1.
Processed 2 pages for database 'xxxx', file 'xxxx_Log' on file 1.
RESTORE DATABASE successfully processed 23850 pages in 2.287 seconds (81.469 MB/sec).
*/

There are more details need to discuss

Check status
select state_desc,name,user_access_desc from sys.databases where name='xxxx'
/*
ONLINE    xxxxx  MULTI_USER
*/

Remember change Owner, if you use "Log shipping"
--Change owner, because if you do a replication or logging shipping, you cannot drop db or tables without a db owner
USE [xxxxx]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO




Newer Older