0

I have a SQL Server Agent job that grabs a bak file and restores it to a database on the server (code below). IT added memory and remapped network drives, so the "oldnetworkdrivename" is now a different name or path.

Original query:

USE [master]
    ALTER DATABASE [dataBase_A] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    RESTORE DATABASE [dataBase_A] 
    FROM  DISK = N'\\oldnetworkdrivename\dataname.bak' 
    WITH  FILE = 1,  
    MOVE N'dataBaseRegistry' TO N'\\oldnetworkdrivename\dataname.mdf',  
    MOVE N'dataBaseRegistry_Log' TO N'\\oldnetworkdrivename\dataname.ldf',  
    NOUNLOAD,  STATS = 5
    GO
    ALTER DATABASE [dataBase_A] SET MULTI_USER WITH ROLLBACK IMMEDIATE

After IT updates, I changed the oldnetworkdrivename to the new one, but I can't re-run the job, the DB is stuck in Single User mode. I have tried setting back to Multi User, taking DB offline, detaching DB, dropping DB, it won't let me do any of this and keeps giving this error like it is stuck looking for the old path:

The operating system returned error 67(The network name cannot be found.) to SQL Server during a read at offset 0x0000000014a000 in file '\oldnetworkdrivename\dataname.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. (.Net SqlClient Data Provider)

It's like it's hung up, still tying to execute the old commands on the old network path, and I can't get it to stop/reset. Any help is very much appreciated, thanks.

Using SSMS (v18.8) on MS Windows10Pro; server is Microsoft SQL Server Standard (64-bit) version 12.0.6433.1

5
  • 1
    Storing mdf and ldf files on network drives is in any case not recommended, it has serious performance and integrity implications. Are you sure you get an error if you do ALTER DATABASE [dataBase_A] SET OFFLINE? Also did you try doing any of these solutions? Jan 12, 2022 at 17:22
  • Can you see if there's any process hanging and kill it? sp_who2 can show if there's another user connected to that database somehow using the single connection.
    – Ronaldo
    Jan 12, 2022 at 21:32
  • You receive an error when trying to set it back to multi user mode?...what is that error? Have you tried killing the process of the backup restore query, for example following these steps? (Note you might find Adam Machanic's sp_WhoIsActive more helpful for pinpointing the process.)
    – J.D.
    Jan 13, 2022 at 0:03
  • @J.D. It gives the same error as in the original post (ALTER DATABASE [dataBase_A] SET MULTI_USER WITH ROLLBACK IMMEDIATE).
    – sean
    Jan 13, 2022 at 20:34
  • @sean Have you tried killing the process for the database as per my links?
    – J.D.
    Jan 13, 2022 at 23:17

1 Answer 1

0

There seems to be an issue with SQL Server where databases can get into a RECOVERY_PENDING state and SQL Server won't realize it. The only way that I've found to be able to fix it is by RESTARTING SQL Server :(

I'm assuming that this is some sort of test environment where the databases and up time don't matter, but I would recommend restarting your SQL Server instance to allow recovery to be run on your database, which will then allow SQL Server to put it into a RECOVERY_PENDING state where you can then drop the database and re-restore/attach it with the proper paths. Make sure that before you do so you follow the usual pre-cautions of making sure you only do so during a maintenance window for the SQL Server instance, because it's going to take that SQL Server offline.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.