If I want to restore database of SSIS catalog as yesterday's status on the same server overwriting existing database using last night backup, does restore database the only thing I need to do? I don't specifically to need to run restore master key statement, correct?
Also does the master key change between before and after the database missing a project .?
thanks
May 13, 2020 at 5:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 13, 2020 at 9:12 am
I think the easiest way to find out is to actually try it.
John
May 13, 2020 at 3:32 pm
Well, that does not help much.
I know the steps to restore to another server, but on the same server, overwrite the same database, do I also need to go through the steps of backup master key and then restore the master key?
I can try it, but I want to clarify this, since either restore master key or not, that may not let me see the difference clearly.
May 13, 2020 at 4:00 pm
Sometimes people are more willing to help if they can see you've made the effort to help yourself. Why not create another database with the same sort of setup, and attempt to overwrite that?
John
My advice is to back up the master key no matter what. Hypothetically, the server dies and needs to be rebuilt from scratch. You install the SQL instance, restore the database and without that master key backup you are stuck (unless you know the master key password). If you NEED something to do a restore of the system, make 100% sure you have a backup copy of the thing.
If you have a test instance (which I expect you do), try it out there. If you don't have a test instance, I'd be very very careful about "testing" on live unless you really don't like your job...
My understanding is that if you are restoring onto the same system, you should not need to restore the master key.
The other thing you could do is add a password onto the master key. That way you can open the master key and add decryption by service master key later. And thus, no master key backup required.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 15, 2020 at 7:23 pm
Thanks, just clarify when you say master key you mean database master key, correct?
I add database in front I think it is as bold below, if not correct, please clarify
My advice is to back up the database master key no matter what. Hypothetically, the server dies and needs to be rebuilt from scratch. You install the SQL instance, restore the database and without that database master key backup you are stuck (unless you know the database master key password). If you NEED something to do a restore of the system, make 100% sure you have a backup copy of the thing.
If you have a test instance (which I expect you do), try it out there. If you don't have a test instance, I'd be very very careful about "testing" on live unless you really don't like your job...
My understanding is that if you are restoring onto the same system, you should not need to restore the master key.
The other thing you could do is add a password onto the database master key. That way you can open the master key and add decryption by service master key later. And thus, no database master key backup required.
May 15, 2020 at 7:43 pm
Yep. That was what I meant - the database master key in every case of the "master key" except when I had said "service master key".
But basically, if you NEED the thing to do a restore in the event the system fails, I make sure I have a backup of that thing. On top of that, make sure your bacckups of those files are on a different server or, preferrably, offsite. The reason I say this is if your backup is stored on the same physical machine or VM as the SQL instance and that system dies (hardware failure, VM corruption, etc), you want to be sure you can get your backups back. I recommend offsite because if the server room literally explodes, you will want to be able to get your files back.
Probably just me being paranoid, as in the 12 years as a DBA I have never needed to pull things off of the offsite storage, but I like knowing it is there. I've also never needed to call the fire department due to my house being on fire, but I like to know they are there.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 15, 2020 at 8:50 pm
Thanks, I did a backup of both service master key and Database master key to files.
And I will try to make them to another offsite place.
May 15, 2020 at 9:10 pm
Also for your statement:
"The other thing you could do is add a password onto the database master key. That way you can open the master key and add decryption by service master key later. And thus, no database master key backup required."
If for example server A is crashed, we restore database to Server B, and we know the password for the database master key, then we can open the master key and add encryption by new server service master key.
But we did not run drop service master key on Server A before the crash and database backup like below :
Use mydb
alter master key drop encryption by service master key;
Will adding new server service master key work?
Thanks
May 15, 2020 at 9:44 pm
Yep. That will work. When you add encryption by service master key, it will overwrite the previous one.
I've had to do this when doing some restores from live to test and the database master key needed to be re-encrypted with the service master key.
As long as you can open the database master key, you can add encryption by the service master key.
Experts - please correct me if the above is incorrect. My experience is that it is accurate though.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply