Lost backup history, safe to reimport?

Question

Lost backup history, safe to reimport?

Ninja's_RGR'us

SSC Guru

Points: 294069
More actions
September 6, 2011 at 7:56 am

#256627

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.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Pouliot Philippe SSCommitted Points: 1912 More actions · Answer 1

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

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.

adb2303 SSCertifiable Points: 7588 More actions · Answer 3

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

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).

adb2303 SSCertifiable Points: 7588 More actions · Answer 5

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 🙂

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

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.

adb2303 SSCertifiable Points: 7588 More actions · Answer 7

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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

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 ;-).

Perry Whittle SSC Guru Points: 233901 More actions · Answer 9

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" 😉

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

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!

Perry Whittle SSC Guru Points: 233901 More actions · Answer 11

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" 😉

Perry Whittle SSC Guru Points: 233901 More actions · Answer 12

Here you go

http://www.sqlservercentral.com/articles/virtual/72682/[/url]

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Perry Whittle (9/6/2011)
Here you go
http://www.sqlservercentral.com/articles/virtual/72682/[/url]

Tx, will check it out later this week.

PS, I love how part 2 of the article links to part 2 of the article instead of part 1 :-D.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Can you ballpark the amount of time required to learn + install?