September 6, 2011 at 7:56 am
Bascially we had a DR situation last week. I had to guide, over the phone, the network admin on how to PIT restore the prod db.
Low and behold I find myself now in a situation where the admin did a drop db from the gui (which drops the history be default).
Now I have backups of msdb so I'm wondering a few things here :
#1 Can I restore msdb as a normal user db?
#2 Is it safe to reimport the backup history into msdb's table for that dropped db?
I need those tables to estimate the growth over time. Plan c would be to do a copy of those tables but I'd rather use the base tables so my scripts work on all servers.
September 6, 2011 at 8:15 am
I did this operation in the past, because somebody because the SysAdmin delete sql job by error. http://msdn.microsoft.com/fr-fr/library/ms190749.aspx
But you need to disabled the Sql Agent for do this operation.
September 6, 2011 at 8:22 am
I don't want to overwrite the msdb db.
I want to restore msdb as a user db called <msdb_oldor whatever>.
Then I want to insert "missing" data in a couple of the history tables of msdb. Those tables are ms_shipped but not considered system tables which are not accessible in any way.
September 6, 2011 at 8:33 am
Hi,
I've just restored a backup of msdb to 'old_msdb' on one of our virtual servers. It went into the user database list no problem
Andrew
September 6, 2011 at 8:38 am
adb2303 (9/6/2011)
Hi,I've just restored a backup of msdb to 'old_msdb' on one of our virtual servers. It went into the user database list no problem
Andrew
Thanks, I forgot to mention that I don't have a test server I can risk losing atm ;-).
Paranoidying.... anything can go wrong by reimporting the data (assuming I respect the keys & all).
September 6, 2011 at 8:50 am
I inserted the contents of old_msdb.dbo.restorehistory into msdb.dbo.restorehistory without any problems. Do you have a particular table in mind you want to recover? I don't mind breaking my virtual server 🙂
September 6, 2011 at 8:56 am
adb2303 (9/6/2011)
I inserted the contents of old_msdb.dbo.restorehistory into msdb.dbo.restorehistory without any problems. Do you have a particular table in mind you want to recover? I don't mind breaking my virtual server 🙂
msdb.dbo.backupset AS bs 😀
TIA.
September 6, 2011 at 9:28 am
It looks as though you should be okay.
msdb.dbo.backupset has a foreign key constraint on backup_set_id with msdb.dbo.backupfilegroup, so as long as you restore the associated rows, I can't see a problem - it doesn't appear to have broken anything anyway.
(sorry took so long - pesky work got in the way)
September 6, 2011 at 9:31 am
adb2303 (9/6/2011)
It looks as though you should be okay.msdb.dbo.backupset has a foreign key constraint on backup_set_id with msdb.dbo.backupfilegroup, so as long as you restore the associated rows, I can't see a problem - it doesn't appear to have broken anything anyway.
(sorry took so long - pesky work got in the way)
Thanks for all the time. Since this was a major event here, I basically have a green light to make sure everything works and doesn't break next time ;-).
September 6, 2011 at 9:47 am
Ninja's_RGR'us (9/6/2011)
Thanks, I forgot to mention that I don't have a test server I can risk losing atm ;-).
get a VM up should take no more than a couple of hours
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 6, 2011 at 10:04 am
Perry Whittle (9/6/2011)
Ninja's_RGR'us (9/6/2011)
Thanks, I forgot to mention that I don't have a test server I can risk losing atm ;-).get a VM up should take no more than a couple of hours
Agreed but I don't know how to do it, don't have the necessary accesses and our Admin has 400 NEW ticket this morning and he's short 2 guys (which means 100% of the rest of the staff). Not the best time to ask for this :-D.
But totally agree that I need a box I can destroy and rebuild at will!
September 6, 2011 at 10:34 am
follow my guide here on SSC how to build a virtual sql server cluster, only just leave out the clustering part and build a stand alone VM. VMWare server 2.0.x is free to download, install it on your desktop\laptop 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 6, 2011 at 10:36 am
September 6, 2011 at 10:41 am
September 6, 2011 at 10:42 am
Can you ballpark the amount of time required to learn + install?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply