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.)
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: