Transferring Databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Transferring Databases

Transferring Databases

Rate Topic

Display Mode

Topic Options

Author

Message

madireddy

Posted Tuesday, June 17, 2008 12:47 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 15, 2008 12:53 PM
Points: 3, Visits: 95

Hi
iam trying to write a stored procedure which needs to dynamically identify the database name, detach it move the files to a different server and attach it there. I have around 30 databases to be transferred. can anyone provide me with the script for moving the databases betwwen the servers. Thanks in advance.

Post #518502

GSquared

Posted Tuesday, June 17, 2008 1:37 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 15,442, Visits: 9,572

To move the database files around in a T-SQL script, you'll need to use xp_cmdshell. That allows you to issue commands like "copy" or "move" to the file system, just like DOS commands.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #518537

madireddy

Posted Tuesday, June 17, 2008 2:44 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 15, 2008 12:53 PM
Points: 3, Visits: 95

Thanks for the answer. Can you tell me if we also need to move the data file or the log file would do

Post #518584

GSquared

Posted Tuesday, June 17, 2008 2:45 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 15,442, Visits: 9,572

Both.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #518586

noeld

Posted Tuesday, June 17, 2008 2:46 PM

SSCertifiable

Group: General Forum Members
Last Login: Thursday, June 06, 2013 9:12 AM
Points: 6,260, Visits: 1,980

Backup and restore could also be used.

* Noel

Post #518587

Steve Jones - SSC Editor

Posted Tuesday, June 17, 2008 3:12 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

You really need three scripts since they have to be run in different places.

The source does the backup or detach and needs to run on the source server.
The copy is an OS job, and it can be run on either source or destination, or a workstation, but if it's under XP_CMDSHELL, the service accounts or proxy need rights to do this. It's probably easier to manually copy data and log files (mdf/ndf/ldf)

The restore/attach is run on the destination.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #518606

« Prev Topic | Next Topic »

Permissions