July 27, 2011 at 10:39 am
I need to move SQL Server 2005 Standard from one virtual server (Win Server 2003) to another (Win Server 2008). I would like to move the system databases so I don’t have to recreate many logins, roles, scheduled jobs, etc.
These are the steps I tried:
Backup system databases ( and all user databases)
Install SQL Server 2005 Std. & sp3 on new server.
Detach all user databases & copy all .mdf & .ldf files to new location
On new server, shut down SQL Server, agent and anything else sql related.
Start new SQL Server with –m
Run SQLCmd
Restore database msdb from disk=’locationof.bak’
With move ‘msdb’ to ‘new location.mdf’,
Move ‘msdblog’ to ‘new location.ldf’,
Replace;
Restore database master from disk=’locationof.bak’
With move ‘master’ to ‘new location.mdf’,
Move ‘mastlog’ to ‘new location.ldf’,
Replace;
After restoring master, SQL shuts down. I understand that. When I try to start it backup, it immediately shuts down again.
What am I leaving out or missing?
Thanks,
Tom
July 27, 2011 at 10:59 am
I used to do this with prod -> qa/dev machines, but I always built the qa/dev machines with the exact same sql version and drive paths as the production unit that they were coming from. What I mean is that if the prod database files were at D:\SQLData then the qa and dev were ALSO at D:\SQLData.
I would stop SQL on prod for about 60 seconds, just long enough to copy the data files for master/model/msdb/tempdb, we didn't do master every time. Then I would place these files on the new server in the exact same locations they were at on the prod machine, when sql was started it couldn't find the user databases and thought it was the old server name. I would then change the server name and do a restart, at that point I could start restoring database backups for the user databases.
One important key, in SQL 2005 you need to backup the server master key from prod and restore it on any of the servers receiving the system databases.
Keep in mind this is not a supported methodology and YMMV.
After the databases were restored I would run some data-cleansing scripts to get rid of some data that I didn't want in non-prod environments and change passwords for many of the accounts for dev/qa.
I did this with SQL 2000 servers and SQL 2008 servers.
CEWII
July 27, 2011 at 11:43 am
restore master db with different name masterfromold .. run sp_hexalogin scripts ..it will generate all login script and then just execute that script on new server.
July 28, 2011 at 11:25 am
I like Elliott's suggestions.
If you are going to move master, move that first. Get that restored and SQL restarted before you mess with msdb, or user databases.
As far as SQL shutting down. Have you checked the error log or the Windows security, system, application logs.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 28, 2011 at 1:05 pm
Its likely having trouble finding either the log for master or one of the tempdb files. I believe that you can start a SQL server with as little as master and tempdb. Model is only needed for new databases and msdb is needed for a lot of ancillary functions but nothing that keeps the engine from running.
CEWII
July 29, 2011 at 4:56 am
Elliott Whitlow (7/28/2011)Model is only needed for new databases
tempdb is created anew every time SQL Server is started, so you need the model database to be there or else SQL server won't be able to create tempdb and won't start!
July 29, 2011 at 9:35 am
Right, but the location must already exist to put the file there. Which is why I follow the process I detailed above.
So, master, model, and tempdb.
CEWII
July 29, 2011 at 12:49 pm
Thanks for all the replies and time you spent.
Elliot:
My current/old server has all my data in c:\mssql. This sql2005 was an upgrade from 2000 (and maybe sql 7). When I install sql2005 on the new server, no matter where I put the data files, they’re in a mssql.1 folder. So, keeping everything the same might take a few steps.
‘backup the server master key …’ sorry, but I don’t have a clue what you mean. Nothing I’ve read anywhere else mentions this.
SSC-Enthusiastic:
Interesting strategy. Sp_hexalogin? Never heard of it. I know there are many examples of how to generate a script of logins. I'm trying to avoid that.
Steve:
The application log has zillions of messages, but this seems to be the problem:
"FCB::Open: Operating system error 3(The system cannot find the path specified.) occurred while creating or opening file 'c:\MSSQL\data\mssqlsystemresource.mdf'. "
I think this means that when I restore master to a new location, SQLServer looks for the resource db where restored master says it should be. ‘c:\mssql\data\...’ the OLD location. No such location on the new server.
So here’s my strategy:
Move the newly installed master & resource databases to a location that matches the old server (c:\mssql\data). (http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx)
Restore the old master backup
Move the newly restored master & resource dbs back to the ‘correct’ location for this instance (C:\mssql.1\mssql\data)
Restore msdn, model db’s
Restore user db’s.
July 29, 2011 at 1:27 pm
This might help with moving resourcedb - http://www.mssqltips.com/tip.asp?tip=1544
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 29, 2011 at 1:34 pm
Do a google search on: BACKUP SERVICE MASTER KEY
Your passwords and other critical information stored in master will not be decryptable without doing this step.
CEWII
July 29, 2011 at 1:46 pm
You don't say if the new server will have the same name?
unless you have a large number of SSIS packages stored in msdb and scheduled as jobs, recreating logins, jobs, roles etc is going to be a lot easier than moving the system databases. You have already hit problems and lost time trying to move the system databases.
any maintenance plans you have will no longer work and will be a pain to get rid of.
the local accounts created by the SQL install will be orphaned, giving you directory access problems
any encryption keys will be invalid
any server name hardcoding will be incorrect
there is probably more but thats all I can think of right now...........
---------------------------------------------------------------------
July 29, 2011 at 1:46 pm
In your situation, I would recommend that you not do it this way.
Instead, build the new VM with Windows Server 2008 R2 - then, install SQL Server 2005 on the new server specifying the layout you want on the new server. In fact, I would suggest that this is a good opportunity for you to upgrade to SQL Server 2008 R2 also.
Then, take the time to move the logins, jobs and other objects that you need to move.
Download and create sp_help_revlogin to build the logins on the new server. Script out the jobs you want to create, rebuild maintenance plans (if needed), and re-deploy any SSIS packages.
You are going to have to do that for maintenance plans and SSIS packages anyways - as those have hard-coded connections to the old server and are not updatable. The only way to fix those is to re-deploy the packages (if they are stored in MSDB) and rebuild the maintenance plans.
Agent jobs are easily scriptable - just right-click and script. You can even script all of them at one time.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
November 9, 2011 at 1:04 am
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply