What is the best methode to move Database server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

What is the best methode to move Database server

Author

Topic

sqldba_ind
Starting Member

22 Posts

Posted - 2007-10-23 : 11:49:57

Hi all

We have a plan to move SQL Server from Old server ot New Server.
for above we prepared a check list.

Here I have couple of Douts
1.Can I take Backup of master.msdb database and Restore in Destination Server.If I follow above what are the Scenario can I follow.

Please let me know which is the best scenario Backup/restore or attach deatch

2.If I move Master and MSDB is it neccessary to move again JOB,ALERT,OPERATOR also or those will come with MSDB Restoration.

3.According above scenario what I have to about DTS,still shall I Export/Import DTS or Opening and saving in Text file and moving in to destination Server

Please some body help on this issue,
I greatefull to you all

Thanks&Regards
SUSRI

sqldba_ind
Starting Member

22 Posts

Posted - 2007-10-23 : 11:52:09

Hi all

I want attach the document what I prepared,but I did n't find any link to up load the document.

Thanks&Regards
SUSRI

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-23 : 11:57:16

Try to be sure your new server is identical to your old server. Make sure the drives, directories, server name, all that are the same (to make it easier). Then you just backup all your dbs and restore them to your new server. I'd restore Master last. All your Jobs, Alerts, Operators will be restored with MSDB. Also, your DTS packages will be restored with MSDB (that's where they are unless you have them saved to the file system and run them from cmd files).

What I've done to make it a bit easier is to make sure the server drives and all are the same. Then stop the old server, copy over the directories, then just restore master and msdb. That seems to work very well although I don't think it's supported to do it that way.

sqldba_ind
Starting Member

22 Posts

Posted - 2007-10-23 : 12:28:31

Hi Van
This is the checklist I prepared.

Check list moving Databases between the Servers.
* DS Destination Server,
* SS Source Server.

1.Verify the SS and DS Servers OS versions, Version of DS must to be match or Must be higher level Version then SS

2.Verify the SS and DS Servers SP Versions, Version of DS must to be match equal or Must be higher level Version then SS.

3.Verify the SS and DS Servers SQL Server versions, Version of the DS must to be match equal

4.Verify the SS and DS for SQL Server 2000 SP versions, SP Version of the DS must to be match or Higher then SS.

5.Make and Verify a Networking shared drive which is accessible from both DS and SS and allocate sufficient space in Network Drive.
6.Verify the Space in DS and Storage Space should higher then SS
7.Move the master database Data file, Log file and Store in Network Drive.

8.Move the master database Data file, Log file and Store in Network Drive.

**9.Attach the Master database in SS

**10.Attach the MSDB database in SS

11.Move the alert ,Jobs, alert and Operator to SS

**12.Move the DTS packages from DS to SS using DTS Export.
13.Move all Scripts.

14.Backup User Databases and Store in Network Drive,
15.Restore UDB’s in to SS

16.Verify the Logins

** 9 & 10. Moving master, msdb Database is some how Critical Job, It is better to move data files and Log files using Detach and Attach method.

** 12. Move the all DTS Package to DS, but make sure that the path must same with SS

Thanks&Regards
SUSRI

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 12:40:12

If both servers have identical Service Pack and the Drive and Folder locations are identical then this you can:

Stop the SQL Service on the Old Server (and the New Server)
Rename the MDF and LDF files on the New Server (just in case!) - e.g. master/msdb/model/etc
Copy all the MDF and LDF files from Old to New
Re-start the SQL Service on New Server

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 12:41:38

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Move%20database,Moving%20to%20a%20new%20sql%20server,Fix%20Orphaned%20Users

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-24 : 09:52:06

Yea, that's kinda what I was talking about. Just stop SQL and copy over everything. As long as the drive letters and all match...everything should work.

sqldba_ind
Starting Member

22 Posts

Posted - 2007-10-24 : 09:53:28

Right now our plan got changed and we are planning to move only UDB's

Action Plan:

1.Change all Source Server Databases Database Recovery Modes in Full and Set Transaction Log backup

2.Take full Backup all Databases and Restore in to destination Server WITH RECOVERY

**** Here I want to confirm all Oraphan user are fixed and DTS,alerts and Job working fine,so that I am Restoring Database WITH RECOVERY
to performing 3,4,5,6,7 steps I am making Database Operation mode

>>>Is it right decission

3.Move the ALERTS,JOBS,OPERATORS

4.Move the DTS packages

5.Make Sure that every Database is Moved

6.Fix Login in Destination Server(Orphan Users).
(what every application using /Database using)

7.Again Backup all Databases and Restore in to destination Server. WITH NORECOVERY

>>>>Once Logins are fixed,If I store Database again Is there any change of Orphane Users.

8.Apply the Transactional Backup’s WITH NORECOVERY Up to Databases server are in Sync

9.Once Databases are in Sync Apply last transactional backup WITH RECOVERY
Please give you suggestion.what every you feel.

can some body help on this issue.

Thanks&Regards
SUSRI

Kristen
Test

22859 Posts

Posted - 2007-10-24 : 10:31:52

"Just stop SQL and copy over everything."

However, for mega-large MDF/LDF files the copy-over can take a while, and if downtime must be minimal doing RESTORE of FULL backups (with NORECOVERY) and then disabling access to Old Server [e.g. set to DBO only] and taking final DIFF and restoring those on New Server may be quicker.

Ideally first copy over the master/msdb/model so that the permissions don't have to be created from scratch - just make sure no new permissions are added after the master/msdb/model are copied over.

"Right now our plan got changed and we are planning to move only UDB'"

I still reckon you are making the job look hard. Why two sets of restores? Fixing the Orhap Users at (6) won't help with the subsequent restore at (7&8)

Why put databases in FULL Recovery Model? You are making changes to your normal working [assuming databases not ALREADY in FULL Recovery Model] which may have side effects of their own - like the disk on Old Server filling up, or the new/revised backup procedures failing because they are untested.

Kristen

sqldba_ind
Starting Member

22 Posts

Posted - 2007-10-24 : 10:53:19

Hi Kristen

In Step 6 I am fixing Orphan Users at Destination Server because I want test exported DTS,ALERTS,to Fix Orphan user I think I need to restore Database in WITH RECOVERY (or with out "WITH RECOVERY),I mean make Database in Operational mode. that is the my plan.

Is it ok or any advices.

Thanks&Regards
SUSRI

Kristen
Test

22859 Posts

Posted - 2007-10-24 : 11:02:18

OK, in that case you need to resolve the Orphans again after step (9)

Kristen

sqldba_ind
Starting Member

22 Posts

Posted - 2007-10-24 : 11:11:24

Thanks Kristen.

I have one more dout

Is it possible keeping Database READONLY MODE(I mean 8th step) and Testing Job,DTS Packages and fixing the Orphan users,So that avoid so many steps.

Or

Atleast Is it possible keeping Database READONLY MODE(I mean 8th step) and Testing Job,DTS Packages ,So that avoid so many steps.

Please help in this regards

Thanks&Regards
SUSRI

Kristen
Test

22859 Posts

Posted - 2007-10-24 : 12:37:04

Might be, but what you probably want is DBO ONLY which will prevent applications connecting (well, that's assuming that they have NOT been given SA logins ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources