Monday, January 23, 2012

Restoring SYSTEM databases

I had the opportunity to restore a few system databases due to disk inconsistency/corruption. Actually my first time in a long time and was it fun. I have a few tips to share

All below were done on a SQL Server 2005 instance

Restoring MASTER Database!!
- start SQL Server in Single User mode (add -m to the startup parameter)
- You can Google this with lots of results. Microsoft How to on SQL 2008 R2

Restoring MSDB Database
- This is easy, just restore like a user database

Restoring MODEL Database!
- Restore model in RECOVERY mode, never in NORECOVERY mode as SQL Server will stop (due to tempdb unable to create, without a model database)
- If you did NORECOVERY, the only way to get in is manual work.
For me, I started it and detach-and-attach the model database that I copy from another location, as one cannot issue RESTORE command in Single-User mode

net start MSSQLSERVER /c /m /T3608 (you can go into SQL BINN and run sqlserver -c -m -T3608 as well)

sqlcmd -E (to connect to the SQL Server)

Detach and Re-attach model database
sp_detach_db 'model'
sp_attach_db 'model', 'DATA.mdf', 'LOG.ldf'
use model

Command line parameterUse
-cForces an instance of SQL Server not to start as a service.
-mAllows updates to the system catalog.
-T3608Recovers the master database only.