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"

2 comentarios:

  1. If you want to recover SQL Server database from suspect mode then you can use SQL recovery software which is capable to recover database from suspect mode and restore database to the SQL Server. http://www.sqlrecoverysoftware.net/blog/repair-database-from-suspect-mode.html

    ResponderEliminar