All geek questions in one place

MSSQL Database on External Hard Drive Shows Pending Recovery

I created a database in SQL Server 2012 with mdf and ldf, pointing to an external hard drive attached to my machine. I created tables, stored procedures, populated tables, etc. Etc. I removed the hard drive at the end of the day.

Today, when I connected the hard drive and tried to access the database in Management Studio, I see the database name with (Recovery Pending).

What does it mean? I see mdf and ldf files in driver D.

+6
source share
4 answers

When you removed the drive, you forcefully disconnected the database from SQL Server. SQL Server doesn't like this.

SQL Server is configured by default so that any created database is automatically saved until the computer shuts down or the SQL Server service is stopped. Before deleting the disk, you must have the database "disconnected" or the SQL Server service stopped.

You can start the database by running the following command in the query window: RESTORE DATABASE [xxx] WITH RECOVERY;

You could, although I would usually not recommend this, modify the database to automatically close after there are no active connections.

To accomplish this, you must run the following query:

 ALTER DATABASE [xxx] SET AUTO_CLOSE ON WITH NO_WAIT;
+9
source

What worked for me was to take the database offline * and then go back online - in this case, there was no need for RESTORE DATABASE, as far as I can tell.

In SQL Server Management Studio:

  • right click on database
  • select Tasks / Take Offline ... breathe deeply, cross your fingers ...
  • right click on database again
  • select Tasks / Take Online
+9
source

Below worked for me:

  • Launch SQL Management Studio as an administrator (right-click on the SQL Management Studio Icon and select "Run As")
  • Take database offline
  • Detach the database using the DROP option
  • Attach database
  • If you used this database with a web application running on IIS, you may need to restart the IIS server

Hope this helps someone

+1
source

Another way of working is to "Restart" the database engine. If this is possible and / or practical for this server, it can be faster when you have several databases in an external drive.

In SQL Server Management Studio:

  • Attach an external drive
  • right-click on the database engine: Server name (SQL Server 12.0.2000 ... etc.)
  • Select Restart
  • Answer Yes, when asked if you want to continue working.
+1
source

Source: https://habr.com/ru/post/975316/

More articles:


All Articles