Today Dec 31, Sunday. Everybody waiting for the New Year. I’m also waiting for that and enjoying the last holiday of this year. But my SQL server doesn’t like the 2018 I guess 🙁 We have migrated our SQL Datawarehouse from Datacenter to AWS. We followed block level copying method which means sent entire Disks data to AWS then create volumes from that data. We kept data and logs on different disks.
During the migration, we have migrated the data volume first and then migrated log disk after 10hrs. In between the 10hrs, the log file has grown a bit and the LSN chain get changed. In the MDF file its showing different LSN. Once everything has been migrated the SQL server was unable to start. Then we found the data corruption on the Master, Model and msdb databases. And yes, you are right the holiday went to DR day. Here is the story I restore corrupted system databases.
Initially, I taught master DB has corrupted.
I can’t able to start the SQL server without master DB, Even I can’t restore the master DB with starting the SQL Server. So here are the steps I followed to restore the master DB.
- Rebuild the Master DB from the scratch.
- But unable to start SQL, because msdb and model databases are corrupted.
- Again rebuild the master DB.
- Moved the MSDB and model database’s Data files to the previous Location.
- Restored the msdb and Model DB from the Backup.
- Restored the Master Database
Rebuild the Master Database:
- Im using SQL Server 2012.
- Go to this location.
C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012
- Open the command prompt in this location.
- Run this command to rebuild the master DB.
.\Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SAPWD= Sql@123
Done. Now it’s a fresh database and the model and msdb are rebuild as new databases.
Move the master database files to the previous location:
My previous data files located like below,
USE master; GO ALTER DATABASE master MODIFY FILE (NAME = master, FILENAME = 'Z:\DATA\SystemDB\master.mdf'); GO ALTER DATABASE msdb MODIFY FILE (NAME = masterlog, FILENAME = 'F:\lOGS\SYSTEMLOG\masterlog.ldf'); GO
- Stop SQL server.
- Move the MDF and LDF files to the respective locations.
- Start SQL Server.
Restore the Old master Database from Backup:
- Stop SQL Server.
- Start SQL server with Singler user mode.
- Open Powershell and went to the below Directory.
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
- Run this command in Powershell
- Open a command prompt window.
sqlcmd -U sa -p Restore database master from disk ='O:\master_full.bak' with replace; GO
- Once it’s restored the SQL server will be automatically stopped.
- Started SQL Server and the master database was completely recovered.
Shocking!! SQL server won’t start again. Another issue with Model and msdb databases.
Again the same issue. Ok, then I decided to recover the MSDB and Model databases first.
Recover MSDB and MODEL Databases:
- Again I rebuild the Master database (followed the above steps)
- Execute the below query to move the files to the different drive.
- Stop SQL server and move the datafiles to the respective folders, then start the SQL Server.
USE master; GO ALTER DATABASE model; MODIFY FILE (NAME = model, FILENAME = 'Z:\DATA\SystemDB\model.mdf'); GO ALTER DATABASE msdb MODIFY FILE (NAME = modellog, FILENAME = 'F:\lOGS\SYSTEMLOG\modellog.ldf'); GO ALTER DATABASE msdb; MODIFY FILE (NAME = MSDBData, FILENAME = 'Z:\DATA\SystemDB\MSDBData.mdf'); GO ALTER DATABASE msdb MODIFY FILE (NAME = MSDBlog, FILENAME = 'F:\lOGS\SYSTEMLOG\MSDBData.ldf'); GO
Restore database model from disk ='O:\model_full.bak' with replace; Restore database msdb from disk ='O:\msdb_full.bak' with replace;
Restore the Master Database:
Now restore the Master database from the backup file.
- Stop SQL Server.
- Start with single User mode.
- Restore the database using SQLCMD.
Restore database master from disk ='O:\master_full.bak' with replace;
Finally, I have recovered all the system databases. But few of the databases went to suspect mode, but its not biggest deal to fix them.