How I resolved “Recovery_Pending State” which occurred while moving SQL Server files

I ran into an issue lately while moving some files around. “Recovery Pending State” in SQL Server is what I kept bumping my head against.

My goal was to move my files on my desktop to a new drive since I received a replacement. TempDB needed to be altered for the location but I didn’t need to move those files since SQL Server re-creates them. (You can’t back them up either!)

Master needed to be adjusted in the SQL Server Configuration Manager and the rest was done via a script. (For the Master files, open SQL Server Configuration Manager -> navigate to SQL Server Services -> Right-click the service you need to adjust, input the new StartUp Parameters and hit apply. It will take effect when you restart the SQL Service.)

Where to edit Master MDF and LDF locations.

The scripts I ran to edit the rest of the databases looked similar to the below:

ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBDat' , FILENAME = 'M:\MSSQL\Data\MSDBDat.mdf' );
ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBLog' , FILENAME = 'L:\MSSQL\Logs\MSDBLog.ldf' );

Once I finished altering all of my database files to their new locations, I stopped the SQL Server Service in Services. I copied and pasted all MDF and LDF files to their correlated new destinations and then started the SQL Server Service once more.

That’s when I ran into the interesting issue of “Recovery in a Pending state”. Some digging and sleuthing brought me back to my scripts.

1. In one script, I had copied the wrong database name over. Example below:

ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBDat' , FILENAME = 'M:\MSSQL\Data\OzarSurvey.mdf' );

Obviously changing this to MSDBDat.mdf resolved the problem.

2. The second issue I had was because I left the file extension off. Example below:

ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBDat' , FILENAME = 'M:\MSSQL\Data\MSDBDat' );

Changing this to MSDBDat.mdf resolved that issue. Hopefully, this post helped you if you landed on this page from Google, if not, I hope my references help! Good luck!

References:

MSDN on Moving System Databases

Priyanka Chauhan on StellarInfo.com

Leave a Reply

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