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.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2005-07-21 : 05:10:30
|
| I have a DTS package that runs every night and drops and recreates a database, and reinserts the test data. This is to ensure that the automated tests run against the DB always contend with exactly the same data.My problem is that we have an additional database on the same server that needs to be kept in sync with the TestDB. I want them both to generate from the same script in the DTS package. My first idea was to rename the database (exec sp_rename 'DB1','DB2') and then rerun the package. However, this does not work as although the database is renamed, the MDF and LDF files are not, hence will not allow me to create identical files. I have since then considered backing up, restoring from backup, detaching and reattaching the Dbs, etc, yet still have not yet devised a solution - all mainly due to this issue of not being able to rename the mdf and ldf files.I read somewhere that you can use xp_cmdshell to rename the physical files but have no idea what the command line syntax would be to do this. Any help would be greatly appreciated.ThankyouHearty head pats |
|
|
mapage
Starting Member
9 Posts |
Posted - 2005-07-21 : 11:41:27
|
| Could you restore the database twice, the second using the WITH MOVE... command? You should be able to rename the data files that way. |
 |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2005-07-25 : 05:15:01
|
| Hey mapageI did think of that. But would I need to actually move them to another location?? As it goes, I did come up with a solution, I used xp_cmdshell to rename the physical files, and then renamed the logical files and filegroup names, then re-executed the DTS. It is a bit messy as there are two different DTS's scheduled to execute one after the other. I would have preferred to use a single DTS, and control the flow of execution of tasks with either global variables or an ActiveX script. But I couldn't get my head around it in time. Although its a bit of a hack, at least it works. But if anyone knows of any good tutorials for using DTS (particularly programming ActiveX scripts), please post them to me!!Hearty head pats |
|
|
|
|
|
|
|
|
Regenerating 2 identical DB's from same DTS Script