martes, 18 de octubre de 2011

Recover MSDB Database in suspect mode

If you don't have a msdb backup, follow these steps:

1.-Stop the SQL services
2.-Navigate to the directory ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn’
3.-Start the SQL services with trace flag 3608 using following command : start sqlservr.exe -c -T3608 -s INSTANCENAME
4.-Detach the damaged msdb: sqlcmd -S INSTANCENAME -E -Q "EXEC sp_detach_db 'msdb','TRUE' "
5.-Move or rename the damaged msdb files (msdbdata.mdf and msdblog.ldf in the Data directory)
6.-Run the instmsdb.sql script in the ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install’ directory
sqlcmd -E -S INSTANCE -i instmsdb.sql
7.-Stop the SQL services
8.- Restart the SQL services without the 3608 trace flag
9.- Check your db isn't suspect
10.- Recreate all jobs/alerts/operators

(The 3608 Trace flag, prevents SQL Server from automatically starting and recovering any database except the master database.)

For activate remote connections to SQL Server check this link: http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277"

MSDB database is marked as suspect

1) I shut down the server, navigating to the directory 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn' and doing the following: start sqlservr.exe -c -T3608
2) renamed the damaged msdb files (msdbdata.mdf and msdblog.ldf in the 'd:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData' directory)
3) Run the instmsdb.sql script in the 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLInstall' directory