What is the safest less risky way to move System Databases to another Server?

Question

What is the safest less risky way to move System Databases to another Server?

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 13, 2012 at 11:26 am

#266434

What is the safest less risky way to move System Databases to another Server?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Which of the system databases?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

I wouldn't even try to restore a different server's system databases.

instead, i'd do the following:.

1. script out your jobs in the msdb database,

2. script out the logins with sp_helprevlogin

3. script out the linked servers from the master database.

4. export master keys and any certificates you might have created.

5. script out the mail settings and profiles in the msdb database.

did i leave anything else out?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

azdzn SSCarpal Tunnel Points: 4128 More actions · Answer 3

Lowell (1/13/2012)
I wouldn't even try to restore a different server's system databases.
instead, i'd do the following:.
1. script out your jobs in the msdb database,
2. script out the logins with sp_helprevlogin
3. script out the linked servers from the master database.
4. export master keys and any certificates you might have created.
5. script out the mail settings and profiles in the msdb database.
did i leave anything else out?

you should listen to him, he's bigger than you ! 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 4

azdzn (1/13/2012)
Lowell (1/13/2012)
I wouldn't even try to restore a different server's system databases.
instead, i'd do the following:.
1. script out your jobs in the msdb database,
2. script out the logins with sp_helprevlogin
3. script out the linked servers from the master database.
4. export master keys and any certificates you might have created.
5. script out the mail settings and profiles in the msdb database.
did i leave anything else out?
you should listen to him, he's bigger than you ! 🙂

hey! my waistline should not be entering into the conversation here, thank you very much! 😀

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Lowell (1/13/2012)
I wouldn't even try to restore a different server's system databases.
instead, i'd do the following:.
1. script out your jobs in the msdb database,
2. script out the logins with sp_helprevlogin
3. script out the linked servers from the master database.
4. export master keys and any certificates you might have created.
5. script out the mail settings and profiles in the msdb database.
did i leave anything else out?

The only thing that I can think of which is no big deal is Database ID's would be different if I do not attach them in the same order then I would have to fix orphans.

That is the approach that I took the last time but I wanted to verify that I took the correct approach.

I have 2 Linked Server that connect to DB2 on an AS400 that is housed by a vendor.

The logins for one of them use a login for someone that is no longer with the company so I need to have them create a generic login with the same authority.

What worries me that most is that there are 211 SSIS packages with AS400 as the Data Source.

Then there is a proprietary router for the T1 that the guy left configured and is owned by the vendor(soon to be ex-vendor). Thank you!:cool:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

azdzn (1/13/2012)
Lowell (1/13/2012)
I wouldn't even try to restore a different server's system databases.
instead, i'd do the following:.
1. script out your jobs in the msdb database,
2. script out the logins with sp_helprevlogin
3. script out the linked servers from the master database.
4. export master keys and any certificates you might have created.
5. script out the mail settings and profiles in the msdb database.
did i leave anything else out?
you should listen to him, he's bigger than you ! 🙂

Oh Sh#$, I have to go to the vet tomorrow for shots. I hope he does dog me out.:hehe:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

george sibbald SSC Guru Points: 104210 More actions · Answer 7

The only thing that I can think of which is no big deal is Database ID's would be different if I do not attach them in the same order then I would have to fix orphans.

this won't cause orphans, but could lead to incorrect default databases

What worries me that most is that there are 211 SSIS packages with AS400 as the Data Source.

this means that copying the msdb database (only) could be worthwhile, at least in terms of the overall time it will take to copy the objects, although there are scripts for bulk export and import of SSIS packages.

you would have to drop any maintenance plans on the source msdb first though, any proxies you are using would be corrupted and would have to be dropped and recreated (this means any jobs owned by those proxies have to be dropped and recreated as well). Local IDS installed by SQL would be lost as well,so those should be scripted out from the target msdb and added after the restore.

Backup all msdbs first before touching them!

As you can see moving system DBs around is a PITA only done after a careful appraisal of the pros and cons.

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

george sibbald (1/14/2012)
[
What worries me that most is that there are 211 SSIS packages with AS400 as the Data Source.
this means that copying the msdb database (only) could be worthwhile, at least in terms of the overall time it will take to copy the objects, although there are scripts for bulk export and import of SSIS packages.
you would have to drop any maintenance plans on the source msdb first though, any proxies you are using would be corrupted and would have to be dropped and recreated (this means any jobs owned by those proxies have to be dropped and recreated as well). Local IDS installed by SQL would be lost as well,so those should be scripted out from the target msdb and added after the restore.
Backup all msdbs first before touching them!
As you can see moving system DBs around is a PITA only done after a careful appraisal of the pros and cons.

For whatever reason they do not store the SSIS Packages on MSDB, they are stored as File System Objects.

Perhaps they did not want to grant permissions to the server

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Lowell (1/13/2012)
I wouldn't even try to restore a different server's system databases.
instead, i'd do the following:.
1. script out your jobs in the msdb database,
2. script out the logins with sp_helprevlogin
3. script out the linked servers from the master database.
4. export master keys and any certificates you might have created.
5. script out the mail settings and profiles in the msdb database.
did i leave anything else out?

Lowell,

Just getting back to this project.

It has been a long time since I scripted out the jobs in the MSDB Database. I went to script and the wizard ran.

How do you recommend that I do this?

I can't detach the MSDB but I could restore the MSDB from the production backup?

Also in an attempt to script out logins I tried as you suggested:

EXEC sp_help_revlogin

Msg 2812, Level 16, State 62, Line 2

Could not find stored procedure 'sp_help_revlogin'.

Exactly how do you script out the mail settings and profiles in the msdb database?

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 10

for the scripting of jobs and linked servers, look in the script section here; there's several that do a great job there;i haven't done it in a while, so i can't recommend a specific script.

for sp_help_revlogin, i believe there are a couple of versions, all directly from microsoft, depending on if you are extracting from 2000,2k5 or 2k8 and above. here's the linky for sql2005: http://support.microsoft.com/kb/918992

for mail settings, i did the legwork in this forum post:

Reverse Engineer / Script out your EXISTING database mail settings?

let us know how it goes!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

Thanks Lowell!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

Lowell (1/18/2012)
for the scripting of jobs and linked servers, look in the script section here; there's several that do a great job there;i haven't done it in a while, so i can't recommend a specific script.
for sp_help_revlogin, i believe there are a couple of versions, all directly from microsoft, depending on if you are extracting from 2000,2k5 or 2k8 and above. here's the linky for sql2005: http://support.microsoft.com/kb/918992
for mail settings, i did the legwork in this forum post:
Reverse Engineer / Script out your EXISTING database mail settings?
let us know how it goes!

Lowell,

The link is for transferring Logins from SQL Server 2005 to SQL Server 2008.

I'm going to a SQL Server 2005 instance.

Thanks for responding.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 13

you are all set buddy; the link and version i sent you works for 2005 and above, so that includes a 2005 to another 2005, if you were not upgrading to a higher version.

there are other versions for SQl 7 and SQL2000 respectively; i think i just did a knee jerk reaction and assumed there might be a 2008-specific version, but there is not.

just one version for 2005/2008/2008R2 ( and presumably 2012?).

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

I just wanted to make sure.

Thanks Lowell.:cool:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/