February 21, 2009 at 10:39 am
The backupXXX tables in msdb are *purely* for historical purposes. They play no role whatsoever in a restore operation - so you can happily delete everything from them and the only ability you lose is easily knowing which backups you took and which backup files are part of which media sets. You can piece all that together using the output from RESTORE LABELONLY on individual backups.
So - no need to take a new set of backups just because you rebuilt msdb (although I'd make sure that your backup strategy included backing up msdb)
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 22, 2009 at 11:02 am
Hi all
sorry, I was off ...
thank you for all your answers ...
So, I understand that I have to rebuild the msbdb database (instead of repair with lost data ...).
On MSDN, I 've searched for infomation about how to rebuild this msdb database on SQL2K, but ... nothing official from Microsoft (maybe my search was bad ...!).
On serveral forums, I found 2 ways to rebuild msdb. What do you mean about each of both solutions ? Are they really working ? (My problem is on a production server, and I have no server for testing that ... 🙁 Which is the best ?
The first solution is (http://forums.databasejournal.com/showthread.php?t=8974) :
If you don't have a backup you can rebuild MSDB by rebuilding the Master DB. To do that I'd back up Master and Model (make 2 copies or each just for safety). I'd also back up all your user databases just in case. Then you need to rebuild the master by shutting down All SQL Server services and running Rebuildm.exe. Restart SQL, and restore the master. You'll have lost everything in MSDB that you put into it but at least it will have been rebuilt.
The second solution is from the msdn blog, a post from Paul Randal (http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx). Paul, you describe the procedure for SQL2005, but you wrote "This works on SQL Server 2000 as well." Did you test it ?
1. Detach the damaged msdb. You can't just detach msdb because you're not allowed to detach system databases. However, you can if you start the server with trace flag 3608. I did this by shutting down the server, navigating to the directory 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' and doing the following: start sqlservr.exe -c -T3608
2. Move or rename the damaged msdb files (msdbdata.mdf and msdblog.ldf in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' directory)
3. Run the instmsdb.sql script in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory
4. Shutdown and restart the server without the 3608 trace flag
My SQL2K is in SP3. Does the instmsdb.sql contain the SP3 specificities ? or Have I to reapply something after ?
thank's for your help and your advices !
February 22, 2009 at 2:19 pm
I believe I tested it but I can't remember for sure.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply