Tuesday, December 24, 2013

SQL - Recover System Databases

  So we had one of those unfortunate things happen that you hope never happens - our SQL server database drive died. Not the drive running SQL, the drive that held the databases. Luckily we had backups of the databases. The drive was repaired but the drive also held the SQL system tables. Without the system tables SQL server will not run. I had the backups, the BAK files for the system tables, but to restore them you need something to restore them to.
  To recover the five system databases that I needed, master, model, msdb, tempdb, and distribution, I used another database server. I created five databases and restored the backups to the newly created databases. Then I copied the database files, the MDFs and LDFs, to the location that my crashed server was expecting to find them and renamed them to the file names that I knew the server would be looking for. When everything was in place I clicked start on the sql service in the server manager and the server started!
  The next day was spent recovering around forty databases. The databases were listed in SQL Server Management Studio (SSMS) but remember because of the crash there were no database files. For each one I used SSMS to create a new database and rename the database file, the MDFs and LDFs, to the names that the server was expecting. Then using SSMS I restored the backup.

No comments:

Post a Comment

Thank you for commenting!