I backed up a database:
BACKUP DATABASE MyDatabase TO DISK = 'MyDatabase.bak' WITH INIT --overwrite existing
And then tried to restore it:
RESTORE DATABASE MyDatabase FROM DISK = 'MyDatabase.bak' WITH REPLACE --force restore over specified database
And now the database is stuck in the restoring state.
Some people have theorized that it's because there was no log file in the backup, and it needed to be rolled forward using:
RESTORE DATABASE MyDatabase WITH RECOVERY
Except that, of course, fails:
Msg 4333, Level 16, State 1, Line 1 The database cannot be recovered because the log was not restored. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
And exactly what you want in a catastrophic situation is a restore that won't work.
The backup contains both a data and log file:
RESTORE FILELISTONLY FROM DISK = 'MyDatabase.bak' Logical Name PhysicalName ============= =============== MyDatabase C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase.mdf MyDatabase_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase_log.LDF
You need to use the
WITH RECOVERY option, with your database
RESTORE command, to bring your database online as part of the restore process.
This is of course only if you do not intend to restore any transaction log backups, i.e. you only wish to restore a database backup and then be able to access the database.
Your command should look like this,
RESTORE DATABASE MyDatabase FROM DISK = 'MyDatabase.bak' WITH REPLACE,RECOVERY
You may have more success using the restore database wizard in SQL Server Management Studio. This way you can select the specific file locations, the overwrite option, and the WITH Recovery option. Sometimes, The restoration process stuck just because of size of the database file. see here: https://madhivanan.wordpress.com/2016/09/06/issue-in-recovering-a-database-that-is-in-the-restoring-state-reference/
Have you tried running a VERIFY ONLY? Just to make sure it's a sound backup.
I figured out why.
If the client who issued the
RESTORE DATABASE command disconnects during the restore, the restore will be stuck.
It's odd that the server, when told to restore a database by a client connection, will not finish the restore unless the client stays connected the entire time.
Here's how you do it:
OK, I have similar problem and exactly as it was in case of Pauk, it was caused by the server running out of disk space while restoring and so caused a permanent restoring state. How to end this state without stopping SQL Server services?
I have found a solution :)
Drop database *dbname*
I had this situation restoring a database to an SQL Server 2005 Standard Edition instance using Symantec Backup Exec 11d. After the restore job completed the database remained in a "Restoring" state. I had no disk space issues-- the database simply didn't come out of the "Restoring" state.
I ran the following query against the SQL Server instance and found that the database immediately became usable:
RESTORE DATABASE <database name> WITH RECOVERY
There can also be problem deleting a stuck database if snapshot is enabled. For me this worked:
I have had this problem when I also recieved a TCP error in the event log...
Drop the DB with sql or right click on it in manager "delete" And restore again.
I have actually started doing this by default. Script the DB drop, recreate and then restore.
I had a similar incident with stopping a log shipping secondary server. After the command to remove the server from log shipping and stopped the log shipping from primary server the database on secondary server got stuck in restoring status after the command
RESTORE DATABASE <database name> WITH RECOVERY
The database messages:
RESTORE DATABASE successfully processed 0 pages in 18.530 seconds (0.000 MB/sec).
The database was usable again after those 18 seconds.
this one did work :
I had a situation where my database showed restoring state and I couldn't run any queries and couldn't connect with our software.
What I did to get out of this situation is:
Stop all SQL related services from windows services.
I opened the DATA folder where the Ldf and Mdf files resides in the SQL directory, normally its like : "C:\Program Files***********\MSSQL\DATA
Then I copied both the Ldf and Mdf files of the database: [db name].mdf and [db name]_log.ldf
I copied both of these files to another folder.
Then I started all the SQL related services (in step 1) again from windows services.
Started my MS SQL Management studio with normal login.
Right click on the culprit database and hit DELETE (to delete the database at all).
All the LDF and MDF files related to this database have gone from DATA folder (mentioned in step 2).
Created a new database with the same name (same name of the one I deleted in step 6 - the culprit database).
Then [database name]->right click -> tasks -> Take Offline.
I then Copied both the files (from step 3) back to the DATA folder (step 2).
[database name]->right click -> tasks -> Bring Online.
Using following T-SQL:
SELECT filename FROM master.sys.sysaltfiles WHERE dbid = DB_ID('db_name');
Using T-SQL continuously:
RESTORE DATABASE FROM DISK = 'DB_path' WITH RESTART, REPLACE;
Hope this help!
WITH RECOVERY option is used by default when RESTORE DATABASE/RESTORE LOG commands is executed. If you're stuck in "restoring" process you can bring back a database to online state by executing:
RESTORE DATABASE YourDB WITH RECOVERY GO
If there's a need for multiple files restoring, CLI commands requires WITH NORECOVERY and WITH RECOVERY respectively - only the last file in command should have WITH RECOVERY to bring back the database online:
RESTORE DATABASE YourDB FROM DISK = 'Z:\YourDB.bak' WITH NORECOVERY GO RESTORE LOG YourDB FROM DISK = 'Z:\YourDB.trn' WITH RECOVERY GO
You can use SQL Server Management Studio wizard also:
There is also virtual restoring process, but you'll have to use 3rd party solutions. Usually you can use a database backup as live online database. ApexSQL and Idera has their own solutions. Review by SQL Hammer about ApexSQL Restore. Virtual restoring is good solution if you're dealing with large numbers of backups. Restore process is much faster and also can save a lot of space on disk drive. You can take a look on infographic here for some comparison.
This may be fairly obvious, but it tripped me up just now:
If you are taking a tail-log backup, this issue can also be caused by having this option checked in the SSMS Restore wizard - "Leave source database in the restoring state (WITH NORECOVERY)"
I had a similar issue with restoring using SQL Management Studio. I tried to restore a backup of the database to a new one with a different name. At first this failed and after fixing the new database's file names it was successfully performed - in any case the issue I'm describing re-occurred even if I got this right from the first time. So, after the restoration, the original database remained with a (Restoring...) next to its name. Considering the answers of the forum above (Bhusan's) I tried running in the query editor on the side the following:
RESTORE DATABASE "[NAME_OF_DATABASE_STUCK_IN_RESTORING_STATE]"
which fixed the issue. I was having trouble at first because of the database name which contained special characters. I resolved this by adding double quotes around - single quotes wouldn't work giving an "Incorrect syntax near ..." error.
This was the minimal solution I've tried to resolve this issue (stuck database in restoring state) and I hope it can be applied to more cases.
All the WITH RECOVERY based options did not work for me.
What did was to do the complete restore from Management Studio.
USE [master] RESTORE DATABASE Sales_SSD FROM DISK = N'D:\databaseBackups02\Daily_Sales_20150309_0941.bak' WITH FILE = 1, MOVE N'Sales_Data' TO N'C:\Data\SSD\Sales.mdf', MOVE N'Sales_Log' TO N'C:\Data\SSD\Sales_1.ldf', NOUNLOAD, REPLACE, STATS = 5
I had the same issue... although I do not know why my database experienced this problem as my drive was not full... It's like it got corrupted or something. I tried all of the above none of them fully worked, I especially thought the suggestion to stop the service and deleting the mdf and ldf files would work... but it still froze up on restore?
I ended up resolving this by deleting the files as mentioned but instead of trying to restore the DB again I copied over fresh .mdf and .ldf files and Attached these using the Front End Attachment wizard. Relief, it worked!!
It took FOREVER to copy over the new files as I am using a Virtual Machine... so copying and pasting using the clipboard took like an hour itself so I would only recommend this as a last attempt.
I had a . in my database name, and the query didn't work because of that (saying Incorrect syntax near '.') Then I realized that I need a bracket for the name:
RESTORE DATABASE [My.DB.Name] WITH RECOVERY
I have got the MyDbName (Restoring...) case because of SQL Express licensed limit.
In the log file, I found this:
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
So if you are trying to restore a bigger database, you need to switch your SQL Express server to Developer edition for instance.
By default, every
RESTORE DATABASE comes with
RECOVERY set up.
The 'NORECOVERY' options, basically tells the SQL Server that the database is waiting for more restore files (could be a DIFF file and LOG file and, could include tail-log backup file, if possible).
The 'RECOVERY' options, finish all transactions and let the database ready to perform transactions.
NORECOVERYoption, when you have a DIFF backup. No LOG backup are allowed in SIMPLE recovery model database.
NORECOVERYoption, then perform a DIFF followed by
NORECOVERY, and, at last, perform LOG restore with
Remember, THE LAST RESTORE QUERY MUST HAVE
RECOVERY OPTION. It could be an explicit way or not. In therms of T-SQL, the situation:
USE [master] GO RESTORE DATABASE Database_name FROM DISK = N'\\path_of_backup_file.bak WITH FILE = 1, [REPLACE],NOUNLOAD, RECOVERY -- This option could be omitted. GO
WITH REPLACE option must be used with caution as it can lead to data loss
Or, if you perform a FULL and DIFF backup, you can use this
USE [master] GO RESTORE DATABASE Database_name FROM DISK = N'\\path_of_backup_file.bak' WITH FILE = 1, NOUNLOAD,NORECOVERY GO RESTORE DATABASE Database_name FROM DISK =N'\\path_of_**diff**backup_file.bak' WITH FILE = 1, NOUNLOAD, RECOVERY GO
USE [master] GO -- Perform a Tail-Log backup, if possible. BACKUP LOG Database_name GO -- Restoring a FULL backup RESTORE DATABASE Database_name FROM DISK = N'\\path_of_backup_file.bak' WITH FILE = 1, NOUNLOAD,NORECOVERY GO -- Restore the last DIFF backup RESTORE DATABASE Database_name FROM DISK = N'\\path_of_DIFF_backup_file.bak' WITH FILE = 1, NORECOVERY,NOUNLOAD GO -- Restore a Log backup RESTORE LOG Database_name FROM DISK = N'path_of_LOG_backup_file.trn' WITH FILE = 2, RECOVERY, NOUNLOAD GO
Of course, you can perform a restore with the option STATS = 10 that tells the SQL Server to report every 10% completed.
If you prefer, you can observe the process or restore in real-time based query. As follow:
USE[master] GO SELECT session_id AS SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE') GO
Hope this help.
In my case, it was sufficient to drop the database which was hanging in state "Restoring..." with the SQL command
drop database <dbname>
in a query window.
Then I right-clicked on Databases and selected Refresh which removed the entry in Management Studio. Afterwards I did a new restore which worked fine (note that bringing it offline did not work, a restart of the SQL service did not work, a server reboot did not work as well).