Restoring to .bak process hung up because network path change



  • 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



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2