June 17, 2008 at 12:47 pm
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.
June 17, 2008 at 1:37 pm
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
June 17, 2008 at 2:44 pm
Thanks for the answer. Can you tell me if we also need to move the data file or the log file would do
June 17, 2008 at 2:45 pm
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
June 17, 2008 at 2:46 pm
Backup and restore could also be used.
* Noel
June 17, 2008 at 3:12 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply