I suggest to have regular full backup of your system database along with user database. But if you do not have system database & your MSDB got corrupted then you have to rebuild it. You can do it by instmsdb.sql script comes with installation. It will result in loss of SQL Agent jobs, backup & restore history, database mail configuration etc. Let’s have a demo for that :-
1. Stop all services connecting to the Database Engine, including SQL Server Agent, SSRS, SSIS, and all applications using SQL Server
2. Start SQL Server from the command prompt using the command: NET START MSSQLSERVER /T3608
3. Detach the msdb database using SQLCMD : SQLCMD -E -S<servername> -dmaster -Q"EXEC sp_detach_db msdb"
4. Using SQL Server Configuration Manager, stop and restart the Database Engine service normally.
5. Now you need to execute instmsdb.sql. You can locate it in <Installation Directory><Instance Name>MSSQLInstallinstmsdb.sql
You can execute it from SSMS as well as from SQLCMD.
For SQLCMD from command prompt use command :
SQLCMD -E -S<servername> -i"<Installation Directory><Instance Name>MSSQLInstallinstmsdb.sql" -o" <Installation Directory><Instance Name>MSSQLInstallinstmsdb.out"
6. Re-apply any service packs or hotfix installed on the instance.
7. Recreate the user content stored in the msdb database, such as jobs, alert, etc.
8. Backup the msdb database.
Reference : Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN :-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
