Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transferring Databases Expand / Collapse
Author
Message
Posted Tuesday, June 17, 2008 12:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, June 17, 2008 1:37 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Tuesday, June 17, 2008 2:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, June 17, 2008 2:45 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Tuesday, June 17, 2008 2:46 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
Backup and restore could also be used.




* Noel
Post #518587
Posted Tuesday, June 17, 2008 3:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:05 PM
Points: 33,165, Visits: 15,299
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 »

Add to briefcase

Permissions Expand / Collapse