CODEWITHSUNDEEP
sqlsql-serversql-server-2008
David Garcia
David Garcia

Reputation: 2696

SQL Restore DB frozen at 100% [SQL SERVER 2008 R2]

This is the second time I've restored my DB and it freezes at 100% not sure why.

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 16305440 pages for database 'db1', file 'db1' on file 1.
Processed 74479 pages for database 'db1', file 'db1_log' on file 1.

I've tried using the console for restoring and also running the command directly into the window and it stays at 100%, it took 63m to completely restore and now is being sometime since then.

session_id  command Percent Complete    ETA Completion Time Elapsed Min ETA Min ETA Hours
61  RESTORE DATABASE    100.00  2017-11-13 16:02:54 69  0.00    0.00

What is going on?

RESTORE DATABASE [db1] FROM  DISK = N'D:\DATA\db1.bak' WITH  FILE = 1,  MOVE N'db1' TO N'D:\DATA\db1.mdf',  MOVE N'db1_log' TO N'D:\DATA\db1_1.ldf',  NOUNLOAD,  STATS = 10

SQL SERVER 2008 R2

progress status

Upvotes: 1

Views: 4979

Answers (2)

sepupic
sepupic

Reputation: 8697

Your RESTORE command has no WITH NORECOVERY and the default is WITH RECOVERY, so it was no need to execute another RESTORE WITH RECOVERY.

Check your SQL Server log instead. Maybe you'll find there smth like

Recovery completed for database MyDB (database ID 5) in N second(s) (analysis K ms, redo L ms, undo M ms.) This is an informational message only. No user action is required.

This means that even if your database was restored the database recovery was not finished yet. This happens when your db has open transactions at the time backup starts, so these transactions should be rolled forward/rolled back depending on whether they were committed or not at the moment backup was finished

Upvotes: 1

LONG
LONG

Reputation: 4620

Normally, after restoring a database backup file without restoring the transaction log backup file, it will leave the database inaccessible, because the database is waiting for the latest transaction log backup. If you need to bring your database back to accessible and you are sure that .bak is the last one your need to restore, try RESTORE DATABASE db_name WITH RECOVERY.

So generally, the order of restoring a database:

Restore database name from ... with norecovery --'.bak'
Restore database name from ... with norecovery --'.dif'
Restore database name from ... with recovery --'.TRN'

If you are not sure if there is any .TRN available, you could add RESTORE DATABASE name WITH RECOVERY at the very end to make sure database can be accessed after restoring jobs. It wont be hurt if the database has been restored with recovery before or not, just a 2nd defense xD

Upvotes: 2

Related Questions