Link to home
Start Free TrialLog in
Avatar of Eric3141
Eric3141Flag for Afghanistan

asked on

Move all database on SQL Server 2008 db server to SQL Server 2008 R2 db server

Hello experts,

Our production db server is SQL 2008.  My management has created a virtual 2008 R2 db server and wants me to move all the databases to it -- both system and user databases.

For the SYSTEM databases is there anything involved other than backing them up then restoring them on the 2008 R2 db server?


For the user databases is there anything involved other than backing them up then restoring them on the 2008 R2 db server?

Thx in advance.

Eric
Avatar of Eric3141
Eric3141
Flag of Afghanistan image

ASKER

PS:  Are there any special considerations moving from a physical box to a virtual one?
SOLUTION
Avatar of Aeriden
Aeriden
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hey guys,

I'm getting close but hit a snag.  I have put the instance in single user mode.
But now when I double-click the SQLCMD icon then it starts and immediately the window closes with no error message showing.  In regular (non-single user) mode SQLCMD works fine.

Ideas?
OK, got past that problem.  SQLCMD working now.

The backup file Master.bak was created on a SQL 2008 instance.
I'm trying to move that Master db to a SQL 2008 R2 instance and getting error indicating version difference.

What now?
Shut down the SQL instance on target machine and physicall copy Master.mdf to it?  Will that upgrade it or cause problems?
Weird.  SQLCMD threw an error ref. version problem when doing the restore.  But the event viewer says restore successfully completed.  Master db is missing 3 tables that were on it in the old db server.  So looks like this is not going to work via SQLCMD.

Is it an option to use Generate Scripts on old db server and generate scripts to move contents of Master from old to new db server?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just realized there is nothing on the new / target 2008 R2 db server yet.  I can uninstall SQL 2008 R2, install 2008, migrate the system database and user databases, then upgrade to 2008 R2.

Sound feasible?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial