May 19, 2005 at 8:04 am
All,
I wonder if anyone could point me in the right direction for a MS support article titled something along the line of:
"Moving SQL Server 2000 Server System Databases to a New Server with BACKUP and RESTORE"
I have managed to find the following articles:
1) "Moving SQL Server databases to a new location with Detach/Attach" http://support.microsoft.com/kb/224071
2) "HOW TO: Move Databases Between Computers That Are Running SQL Server" http://support.microsoft.com/kb/314546
3) "INF: Moving SQL Server 7.0 Databases to a New Server with BACKUP and RESTORE" http://support.microsoft.com/kb/304692/EN-US/
I am decommissioning a current Server and migrating all databases across to a new server, configurations listed below:
Old configuration: Dell 2650, Windows NT4.0 SP6, SQL Server 2000 SP3a
New configuration: Dell 2850,. Windows 2003 Server SP1, SQL Server 2000 SP3a
None of the articles above seem to explicitly tackle the process of moving SQL 2000 system databases from a server being decommissioned to a new server (new hardware, OS, same SQL installation), there seems to be the exact article i'm looking for but for SQL Server 7.0 (see article #3).
I was going to follow the steps outlined within article #1, but this specifies that it is a process for moving current database file locations, is this the acceptable best practice for what I am trying to achieve, obviously I would substitute the section about moving the master database to using a backup from the old server, again is this good practice? Personally I am not too struck on performing the steps outlined within article #2 and would feel far more confident of the final outcome if I could do a system database BACKUP and RESTORE or sp_detach_db and sp_attach_db on the all system databases, then subsequently apply user database restores?
The reason I ask is it all seems slightly too simple and i'm guessing I may have missed something out, after comments I will post my intended plan of action below and would appreciate any comments.
Many Thanks in advance,
ll
May 19, 2005 at 8:56 am
Personally, when moving to a new server I prefer to just re-install SQL Server and let it recreate the new system databases. It's then simple enough to move your user databases over using either backup/restore or by using the detach and reattach methods. If you have a lot of users and/or DTS packages or whatever you can either script these out or use DTS to migrate them to the new installation. You will get orphaned users in your user databases if you do this and there's a fix for this here: http://support.microsoft.com/default.aspx?scid=kb;en-us;240872&f=11
Hope this helps.
My hovercraft is full of eels.
May 19, 2005 at 9:26 am
sswords,
Thanks for the words of advice, I am proficient with both DTS and scripting so ultimately have now problem with this, also I am far too familiar with xp_change_users_login and orphaned users. I am really just after thoughts on whether system databases (particularly master) can be successfully restored on a new cleanly built SQL Server install as I potentially see this as a far simpler and ultimately less error prone method, that is if it is good practice/proven?
ll
May 19, 2005 at 10:02 am
In that case, article #3 is probably the best way to go for you. But please note the cautions listed in that article and act accordingly. Article 1 deals with moving db files around on the same server and article #2 is primarily concerned with moving user databases between servers but doesn't deal with the system DB's which have some special considerations of their own that you must deal with. Those are covered pretty well in the third article. It goes without saying but if at all possible, practice this a couple of times before you do your final install.
Fortunately for me, most of my installations don't have a lot of customizations and the logins are manageable so that's why I almost always prefer a new installation of the system DB's on a new server - it just makes things simpler. Obviously, that doesn't always work for everyone or every installation.
Best of luck with your moves.
My hovercraft is full of eels.
May 20, 2005 at 2:26 am
Again cheers for the advice. However I am slightly concerned that article #3 explicitly states that it is not for SQL Server 2000 and only for SQL Server 7, however as you pointed out I would only be interested in the system database restore section of article #3. I assume then that no one has ever found a relevant article for SQL Server 2000? I do find it slightly strange that there seems to be no definitive best practice for achieving this.
Thanks again for your comments
ll
May 20, 2005 at 3:08 am
I agree with you that this topic is not very well covered. I found the following article quite helpful:
http://vyaskn.tripod.com/moving_sql_server.htm
May 20, 2005 at 5:31 am
Cheers Martin, I have already read this funnily enough, there truely is lack of information. However when it gets to the nitty gritty the author simply puts:
"
"
He then basically just says stop and start your services and scoots around the problem in hand. I'm getting more and more concerned.
Anyone else know the proper procedure to restore system databases (in particular master) on a new server build both systems running SQL Server 2000 sp3a?
Thanks in advance,
ll
May 20, 2005 at 6:11 am
Hi,
actually it should be easy to do it via backup and restore...
If on the new server all paths are the same as on the old server, then no sweat, you have to install the new server with exactly the same SQL Server Version (SP's and hotfixes) as the old SQL Server and (hopefully) the same server name.
On the old server you do a backup of all databases except tempdb.
Next you restore only master.db to the new server (after that SQL Server needs to restart)
Then you restore model, msdb, user databases and correct the size of tempdb.
That's all.
If the paths to model, mdsb and tempdb on the new server are different you will have to start SQL Server in emergency mode after restoring master.db to restore msdb and model and/or move tempdb (via QA).
regards karl
Best regards
karl
May 20, 2005 at 6:18 am
Hi
I have succesfully moved from an old server to a new server several times. Nothing fancy in those servers though.
Installing the new server, applying SQL Server, SP3a.
Stopping SQL on the both servers.
Rename Old server to X, rename new server to Old, copying all database files to the new server ensuring the location of the files doesn't change. Start SQL on the new server and you are running.
If you dont reuse the server name, you have to go down in msdb.sysjobs and change the values in the field originating_server.
Proper procedure??? It worked for me
Regards Ib
May 20, 2005 at 9:28 am
What about using the Copy Database Wizard via Enterprise Manager. MSSQLSERVER needs to run with a domain acct, local sys admin will not work. This can be accessed via Enterprise Manager > Tools > Wizards, expand Mgmt and select Copy Database Wizard. One of the options in here is to Copy or Move the database. The logins should copy over as well. They did in my testing.
Good Luck!
May 20, 2005 at 10:08 am
Thanks ever so much everyone for your input. Through much googling I have found the following MS article:
"HOW TO: Transfer Logins and Passwords Between Instances of SQL Server" http://support.microsoft.com/?id=246133
I have decided that I am going to build the new server as specified in my previous posts, then I am going to transfer the Logins and Passwords as per the "Create and Run Stored Procedures in the Master Database" section in the article above. After this is tested I will restore/reattach the msdb and configure the tempdb/model databases as appropriate then restore/reattach all user databases, hopefully only falling foul of a few orphaned users due to following the procedure above. Then i'm sure I will have hours of fun testing and modifying DTS packages and Jobs.
Many Thanks again to all of you again and I hope this helps to clear up a slightly grey area in SQL 2000 administration.
ll
June 9, 2005 at 7:44 am
Hi All!
I have read through this topic, and tried using several ideas in here, but am still coming up empty on moving an sql2000 database. I need to recreate the production database onto a test server without disturbing the production server. The servers' names are different, and the database files will be in different locations on the test server.
I have the exact locations of the production server's databases and logs. I have a full "backup' (not sql type backup) of those files available, and can also do an sql backup if needed. I don't need Northwind, we only have one additional database outside of the standard install databases. The production database only has one user account in it, as the database is accessed from a website.
The OS and Service Packs are identical, as is SQL and its SPs.
What would you guys recommend?
Any assistance would be greatly appreciated!
ShrewLWD.
June 9, 2005 at 9:18 am
In your situation I would prefer a new install of SQL Server on the new server. You can then do a SQL Server backup of your current production database, and copy the resulting *.bak file to your new server. Create a new database with the same name on the new server and set the size to be close to what you have on the old server (this speeds up the process as the database doesn't have to autogrow during the restore). Once the copy of the *.bak file is finished and you've created a new database, open Query Analyzer on the new server and issue a restore command similar to the following:
RESTORE DATABASE (mydatabase name)
FROM DISK = 'e:\MSSQL\BACKUP\(mydatabasefullfilename.BAK)' WITH REPLACE,
move 'mydatabase_Data' TO 'F:\MSSQL\TEST_Data.MDF',
move 'mydatabase_Log' TO 'D:\MSSQL\LOGS\TEST_Log.LDF'
Adjust the script above to reflect the exact database, filenames, and paths to the backup and data files, and take out the parenthesis. You need to use the REPLACE and move options since you're changing the path of your files on the target server. You can do the same thing through Enterprise Manager, but in these cases I usually prefer to use a script. You can then save and re-use the script when you have to refresh the database on your new server. Hope this helps.
My hovercraft is full of eels.
June 9, 2005 at 9:37 am
Hey SSwords,
Yes, that is what I had in mind (a fresh install). However, I don't seem able to re-setup the master, model and msdb databases.
I installed a fresh sql. I then used Enterprise Manager to restore pubs, OurDatabase, and model (I made sure to reset the file locations before restoring, so they matched where they need to be on the new server, not where they were on the old). I was not able to do master or msdb in enterprise. So I restarted sql using command switches, then used query analyzer to restore/replace msdb. Thinking all were OK, I then restored master, which it did. Master now thinks the rest of the databases are in their old places (I'm reading the sql logs to tell me this). So Master needs to be told where these databases are on the new server.
The reason I am doing this is that it appears master has tables that make calls to OurDatabase. If I install a fresh SQL and just move OurDatabase over, the website does not return info. (I have made sure the account it uses to query SQL is setup correctly, both as a userlogin and a sqllogin). Can I just move master's tables over to the fresh install?
I am not an sql programmer by trade (I'm a security admin), so I may be missing something obvious here. I'm assisting with this setup so that we can do some security audits and changes to see what will break.
June 9, 2005 at 9:42 am
Hi,
you just did the restore in the wrong order!
First to restore is allways master, because the path to all other databases resides in master..sysfiles and master..sysaltfiles.
After master you should restore msdb, model, userdb's.
regards karl
Best regards
karl
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply