system database restore - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

system database restore

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-08-18 : 18:01:47

Hi All,

I tried to restore the system databases. I was able to restore the master but model and msdb were giving errors. what is also observed once after master is restored, sql server stopped and i had start it once again using -T3608 trace flag

backup database master to disk = 'd:\sqldump\master.bak'
backup database model to disk = 'd:\sqldump\model.bak'
backup database msdb to disk = 'd:\sqldump\msdb.bak'

restore headeronly from disk = 'd:\sqldump\master.bak'
restore headeronly from disk = 'd:\sqldump\model.bak'
restore headeronly from disk = 'd:\sqldump\msdb.bak'

-- verified the backups

restore verifyonly from disk = 'd:\sqldump\master.bak'
restore verifyonly from disk = 'd:\sqldump\model.bak'
restore verifyonly from disk = 'd:\sqldump\msdb.bak'

/*

The backup set on file 1 is valid.
The backup set on file 1 is valid.
The backup set on file 1 is valid.

*/

C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Binn>sqlservr.exe -sSQLEXpre
ss03 -T3607

C:\>sqlcmd -Swin-seven\SQLEXPRESS03 -E
1> restore database master from disk = 'd:\sqldump\master.bak';
2> go
Msg 3108, Level 16, State 1, Server WIN-SEVEN\SQLEXPRESS03, Line 1
To restore the master database, the server must be running in single-user mode.
For information on starting in single-user mode, see "How to: Start an Instance
of SQL Server (sqlservr.exe)" in Books Online.
Msg 3013, Level 16, State 1, Server WIN-SEVEN\SQLEXPRESS03, Line 1
RESTORE DATABASE is terminating abnormally.
1>

C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Binn>sqlservr.exe -sSQLEXpre
ss03 -T3607 -m

C:\>sqlcmd -Swin-seven\SQLEXPRESS03 -E
1> restore database master from disk = 'd:\sqldump\master.bak';
2> go
Processed 360 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

C:\> -- sql server stopped

C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Binn>sqlservr.exe -sSQLEXpre
ss03 -T3608

1> restore database msdb from disk = 'd:\sqldump\msdb.bak';
2> go
Processed 712 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 6 pages for database 'msdb', file 'MSDBLog' on file 1.
Msg 3283, Level 16, State 1, Server WIN-SEVEN\SQLEXPRESS03, Line 1
The file "MSDBLog" failed to initialize correctly. Examine the error logs for mo
re details.
Msg 3013, Level 16, State 1, Server WIN-SEVEN\SQLEXPRESS03, Line 1
RESTORE DATABASE is terminating abnormally.
1>

Can anyone figure what the problem is all abt??

I also tried with MOVE option, still the same error for model and msdb.

I see they are in restoring state in management studio. :(

Thanks any advance.

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-08-23 : 15:47:11

restart your mssql server in master only by using cmd and restore msdb and model database

Manju

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources