SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Migrating Databases to Another Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scabral7
Yak Posting Veteran

57 Posts

Posted - 04/01/2012 :  09:35:25  Show Profile  Reply with Quote
Hi,

we currently have a SQL 2008 R2 database on a Win 2003 32bit box with 4MB RAM and 300GB hardrive.

We just installed SQL Server 2008 R2 on Win 2008 Server with 32MB RAM and 2TB SAN Storage

I have about 10-15 databases that i need to migrate over to the new server. What is the best way to migrate the datbases, logins, SQL Server jobs, etc..? Is there a good side that has step by step instructions or best practices to follow?

thanks
Scott

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 04/01/2012 :  11:19:30  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Jobs and logins I'd use SSIS for. It has transfer logins and transfer jobs tasks, among others.
For the DBs, backup, copy backups, restore.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 04/01/2012 :  14:27:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
Are you migrating everything over? If so, if you can use the same exact paths, you can simply follow these steps:

1. Stop SQL on both machines
2. Copy over *all* database files (mdf, ldf, ndf) from old to new (system and user dbs)
3. Start SQL on new machine

Voila!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

scabral7
Yak Posting Veteran

57 Posts

Posted - 04/01/2012 :  16:04:46  Show Profile  Reply with Quote
including master and msdb databases or should i not move those?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 04/01/2012 :  16:46:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
Move everything with my method.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

scabral7
Yak Posting Veteran

57 Posts

Posted - 04/01/2012 :  17:42:46  Show Profile  Reply with Quote
tara,

once i move the mdf and ldf files to the new server and start the service, do i still need to go into management studio and create all the databases?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 04/01/2012 :  19:20:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
No, they will all be there because you copied master and all of the databases. Everything will be there, logins, jobs, etc... Anything external to SQL Server won't be there, but everything internal to it will be there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

franco
Constraint Violating Yak Guru

Switzerland
255 Posts

Posted - 04/06/2012 :  05:54:31  Show Profile  Reply with Quote
Sorry if I come into this discussion.
I need to do the same exact thing except that the old server is a physical SQL Server 2000 Cluster (MSCS) and the new server will be a virtual standalone SQL Server 2000 under vmware.
Do you think I can use the same approach?

Does SQL Server know in some way that it was on a Cluster?

Thank you very much.


Franco

Edited by - franco on 04/06/2012 08:24:29
Go to Top of Page

franco
Constraint Violating Yak Guru

Switzerland
255 Posts

Posted - 04/10/2012 :  09:14:07  Show Profile  Reply with Quote
No one can answer my question?
Any idea?

Franco
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 04/10/2012 :  12:22:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
I don't think you can use this approach to go from a cluster to a standalone as yes SQL does know about it being on a cluster. The installation of SQL Server is different than on a standalone server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 04/10/2012 :  12:24:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
franco, do you have a test system you can use to test it out? The only part we are copying over are the database files, which are not cluster-related files. This could work now that I think about it, but you must test the theory first.

What's important is that the path to the databases are the same on both systems.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

franco
Constraint Violating Yak Guru

Switzerland
255 Posts

Posted - 04/10/2012 :  13:32:36  Show Profile  Reply with Quote
Tara thank you very much for your reply.
Of course we have a test system to use before going for real.
Everything will be the same, same path, same disks, same server name, same everything.
My only concern is about the fact that all the databases resides on a cluster.
I hope that users db and system db are not aware of this fact.

Best Regards.


Franco
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000