SQL 2k5 Database Migration

[TamedTech]

Hello Guys,

I've got a couple of remote servers, both running win2k3 and SQL 2005 Workgroup. I'm looking to migrate one of my databases from one server to the next and thought I'd come and get some advice from you on the best way to do it.

The database is currently sitting at around 7GB in size including the logs. The plan is to take the font end application offline whilst we're making the move so this isnt an issue and I can close all current connections to the database, move it, then bring the application back up again.

Does MS supply any nice tools for this kind of migration? I'm hoping it'll be a fairly simple process.

Thanks for any advice guys,

Rob

 

[mrdenny]

It is a very simple process.

1. Shut off the front end app to stop all data comming into the system.
2. Detach the database.
3. Move the data files to the new server.
4. Create any missing logins on the new server, preferbly using the same SID as the old server.
4. Attach the database.
5. Change the connection string of the front end app.
6. Turn on the front end app

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[TamedTech]

Really? Man that is sweet!

I'll give it a shot over the weekend Denny and let you know how I get one.

Thanks for the advice.

Rob

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[TamedTech]

Hi Denny,

Just to let you know, this worked great, the DB is moved accross with no problems :-D

Rob

 

[mrdenny]

Excellent, good to know.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog