Skip to content
Fix Code Error

SQL Server: Database stuck in “Restoring” state

March 13, 2021 by Code Error
Posted By: Anonymous

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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAMyDatabase.mdf
MyDatabase_log  C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAMyDatabase_log.LDF

Solution

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 sucess 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.

Answered By: John Sansom

Related Articles

  • How do you clear the SQL Server transaction log?
  • Convert array to nested JSON object - Angular Material tree
  • Tensorflow: how to save/restore a model?
  • How do I keep only the first map and when the game…
  • InfluxDB - ERR_EMPTY_RESPONSE - Used to come up in…
  • What is "android:allowBackup"?
  • SQL Server database restore error: specified cast is…
  • How to restore to a different database in sql server?
  • What are the undocumented features and limitations…
  • TypeError: Cannot read property 'first' of undefined…
  • Restore DB — Error RESTORE HEADERONLY is terminating…
  • Wipe data/Factory reset through ADB
  • Remove similar tuple from dictionary of tuples
  • Start redis-server with config file
  • How to import a bak file into SQL Server Express
  • What is and how to fix…
  • Best way to replace multiple characters in a string?
  • Possible to restore a backup of SQL Server 2014 on…
  • Type error: Member not found or visible in unit256
  • Fixing a systemd service 203/EXEC failure (no such…
  • How to add a local repo and treat it as a remote repo
  • SQL Server database backup restore on lower version
  • How does PHP 'foreach' actually work?
  • Cannot open backup device. Operating System error 5
  • How do SO_REUSEADDR and SO_REUSEPORT differ?
  • How to deal with persistent storage (e.g. databases)…
  • Restoring a mongo database but mongo shell does not show it
  • How to create websockets server in PHP
  • Postgres: How to Backup & Restore a table
  • SQL Network Interfaces, error: 50 - Local Database…
  • Use SQL Server Management Studio to connect remotely…
  • Remix error The transaction has been reverted to the…
  • Compiling an application for use in highly…
  • How to import a SQL Server .bak file into MySQL?
  • Code for Discord.js responding with a throw error
  • Why is "except: pass" a bad programming practice?
  • Restore a postgres backup file using the command line?
  • Where is database .bak file saved from SQL Server…
  • java.lang.IllegalStateException: Cannot (forward |…
  • SQLite3 database or disk is full / the database disk…
  • The definitive guide to form-based website authentication
  • How do I obtain a Query Execution Plan in SQL Server?
  • How to backup a local Git repository?
  • Interrupt an earlier timeout event in Simpy
  • ADB - Android - Getting the name of the current activity
  • XAMPP - MySQL shutdown unexpectedly
  • how to add nodes and links do d3-force without enter
  • Cannot connect to SQL Server named instance from…
  • Recover sa password
  • Polymer dom-repeat not rendering changes with…
  • Mock MQRFH2 header in JUnit Testing Error [MQRFH2…
  • "No backupset selected to be restored" SQL Server 2012
  • integrating disqus with emberjs only works on first…
  • Angular: Can't find Promise, Map, Set and Iterator
  • How to take MySQL database backup using MySQL Workbench?
  • Backup/Restore a dockerized PostgreSQL database
  • polymer paper-dialog within paper-dialog
  • Replacing multiple lines of xml file from ant target
  • Format of the initialization string does not conform…
  • How to config backups of Azure Managed Disks from Terraform?
  • How to Replace Multiple Characters in SQL?
  • Logging best practices
  • How to return grandchilds names for each person in array JS?
  • How can I multiply dates in Solidity after…
  • What is your most productive shortcut with Vim?
  • mysqldump & gzip commands to properly create a…
  • Creating new database from a backup of another…
  • How to put a value of data object in another data…
  • "gatsby-source-airtable" threw an error while…
  • By clicking on different elements with the same css…
  • The transaction log for database is full. To find…
  • How to create a search filter using Vue js from API Data?
  • Ukkonen's suffix tree algorithm in plain English
  • How to send an email with Python?
  • SQL-Server: Error - Exclusive access could not be…
  • SQL server 2008 backup error - Operating system…
  • SQL Server: Query fast, but slow from procedure
  • Azure Pipeline fails on `dotnet build` with error…
  • Passport.js in Next.js app not saving user across…
  • How can I get column names from a table in SQL Server?
  • WhatsApp cannot detect local backup file
  • How to automate parameters passed into pandas.read_sql?
  • Advantages of SQL Server 2008 over SQL Server 2005?
  • Unable to connect to SQL Server instance remotely
  • Backup database in Laravel 8
  • Smart way to truncate long strings
  • ODEINT - Different results when new equations added
  • How to connect to local instance of SQL Server 2008 Express
  • Apache server keeps crashing, "caught SIGTERM,…
  • java.sql.SQLException: - ORA-01000: maximum open…
  • Trouble understanding behaviour of modified VGG16…
  • VueJs single file component not reading data/props/methods
  • Sinon stubs not been called
  • DBCC SHRINKFILE on log file not reducing size even…
  • SQL-Server: Is there a SQL script that I can use to…
  • how to rename an index in a cluster?
  • What are forward declarations in C++?
  • mssql '5 (Access is denied.)' error during restoring…
  • How do I decrease the size of my sql server log file?
  • Configuring Hibernate logging using Log4j XML config file?

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

How can I open a URL in Android’s web browser from my application?

Next Post:

Clear form fields with jQuery

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error