Migrating Databases to Another 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 2008 Forums

SQL Server Administration (2008)

Migrating Databases to Another Server

Author

Topic

scabral7
Yak Posting Veteran

57 Posts

Posted - 2012-04-01 : 09:35:25

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
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-04-01 : 11:19:30

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-01 : 14:27:37

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

scabral7
Yak Posting Veteran

57 Posts

Posted - 2012-04-01 : 16:04:46

including master and msdb databases or should i not move those?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-01 : 16:46:21

Move everything with my method.

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

Subscribe to my blog

scabral7
Yak Posting Veteran

57 Posts

Posted - 2012-04-01 : 17:42:46

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-01 : 19:20:59

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

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2012-04-06 : 05:54:31

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

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2012-04-10 : 09:14:07

No one can answer my question?
Any idea?

Franco

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-10 : 12:22:39

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-10 : 12:24:40

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

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2012-04-10 : 13:32:36

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources