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.
ERROR message The log scan number (17:128:7) passed to log scan in database ‘master’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
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.
- Open the command prompt in this location.
- Run this command to rebuild the master DB.
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,
- 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.
- Run this command in Powershell
- Open a command prompt window.
- 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.
ERROR message The log scan number (17:132:14) passed to log scan in database ‘msdb’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
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.
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.
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.