Clone new database from existing

RDuke


How do I go about creating a cloned copy of an existing database on the same server I basically want to set up a test copy of a production database, but let it reside on the same server with a slightly different name, prefixed with 'Testing_".

I typically just use Studio Express Manager and not scripts to do admin tasks. Here's what I tried that didn't work so well:

* Had full backups of production db
* Detached production database I wanted to clone
* From within Win Explorer, copy the .mdf and .ldf files within same data folder and gave them a new name, prefixing with "Testing_"
* Re-attached prodution db
* Attached "Testing_..." database

Sql Express seems confused at this point with new database I attached. It references the full path of the database I copied from (c:\program files\microsoft sql server\mssql.1\data\...". I rename the datbase, removing full path and prefixing with "Testing_".

* Using Sql authentication, changed the default database for the Login account to the new cloned copy of database.
* Did some query level data sanitizing to the cloned copy of the db. I am positive I performed this on the correct db.
* Was able to connect/authenticate to the cloned copy through a custom VB app on my dev machine, and all looked well.
* Tried to connect to the cloned db from another machine and couldn't. Went back to my dev machine and noticed the original production db looked hosed -- there were no tree nodes available to it! Was still able to connect to the cloned "Testing_" db copy, but not able to connect/authenticate anymore to the production copy.
* Detached cloned copy of "Testing_" db and moved it out of the data folder.
* Detached production db. Got an error msg indicating operation failure of some kind, but the database was gone from the tree.
* Reattached the production db but couldn't hit it from my custom vb app. Re-did the login and user accounts used by the app and was able to hit it. However, the data sanitizing I had done earlier in the cloned copy clearly was visibile in the production db!
* Had to restore production db from backup. Had problems authenticating from app, and recreated User account before the app was to connect.

Q1: What I am doing wrong Sql Express was obviously as confused as me somewhere along the line.

Q2: Is specifying a default database used by a Login account necessary for a VB app like mine. I think it was something I discovered in the past, however, this would seemingly present a problem whenever I want my app to hit a different database, established through a connection string.

TIA...

Rick

Andrea Montanari

hi Rick,

when doing this kind of "sorcery", please be carefull when "re-registering" the database..

just to cross check, usually you should:
-detach the original db;

-copy it elsewhere or rename destination files (mdf, ldf and eventual ndf) say, with your prefix.. (orig.mdf becomes clone_of_orig.mdf and the like);

-reattach the original db via

USE [master]
GO
CREATE DATABASE [myDB] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\myDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\myDB_log.ldf' )
 FOR ATTACH
GO

-the original logical db name is correct, as the original physical file(s) pointers..

-attach the cloned db via

USE [master]
GO
CREATE DATABASE [CLONE_of_myDB] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\clone_of_myDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\clone_of_myDB_log.ldf' )
 FOR ATTACH
GO

as you can see, the logical name of the db is different, as are the referenced phisical file names..

-the internal logical names of each physical file are not considered to collide with other database's logical names, so you are not in trouble here..

regards

RDuke

Hmmh... what you described is exactly what I did, with pecuilar results, except use Sql Express Manager.

- I detached original db
- Copied to new set and renamed (cloned)
- Reattached original db
- Attached cloned db

Is Sql Express Manager not up to this task

Rick

Jens K. Suessmeyer

Do you get an error message or what are you experiencing If you can¡¯t get attach / detach to run for you, you can also use the backup / restore functionality for this.

HTH, jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

RDuke

All I got was some meaningless generic "operation failed" type of error, plus, the odd behavior I described above when sanitizing (running update queries against) the cloned database.

I did try restoring the source db and specifying clone db as target, but was prevented from doing so with a message to the effect of "target database not in dataset so not allowable."

If somebody wouldn't mind trying to clone a db from the same server through Sql Express Manager, and let me know the results, I'd feel better about trying it again. When I tried it, I discovered I was simultaneously sanitizing both databases.

Rick

Arnie Rowland

The 'safest' (and easiest) method to do this is to do a backup, and then restore to a different name and/or location. Read up on RESTORE in Books Online.

RDuke

Alright, I was able to do that through Express Manager this time. Much easier and safer. Thanks, both of you.

Rick