Cloning a table

In my .NET code, with SQL Server 2000 as the backend, I need to create an
exact copy, without data, of an existing table in the same database, that
the user selects from a combo box. Any ideas on how to do this?You can create a copy without constraints or indexes using SELECT ... INTO.
Specify a WHERE clause to exclude data too:

SELECT * INTO MyNewTable FROM MyTable WHERE 1 = 0

Note that the user will need CREATE TABLE permissions. This isn't the kind
of thing one usually does in database applications though.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Michael Jackson" <michaeldjackson@.cox.net> wrote in message
news:%MsXd.6532$Wy.1516@.okepread02...
> In my .NET code, with SQL Server 2000 as the backend, I need to create an
> exact copy, without data, of an existing table in the same database, that
> the user selects from a combo box. Any ideas on how to do this?

cloning a database

What is the process that would allow us to create a copy of our production
database that we could use for testing puproses.
Don
Hi
Backup the database and restore it on your test server.
Regards
Mike
"don" wrote:

> What is the process that would allow us to create a copy of our production
> database that we could use for testing puproses.
> --
> Don
|||This is also a good test of the backup procedures that you should have in
place. Knowing how to restore from a backup is important!
Keith
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:6C2998EA-E002-4919-954E-335060B62641@.microsoft.com...[vbcol=seagreen]
> Hi
> Backup the database and restore it on your test server.
> Regards
> Mike
> "don" wrote:
production[vbcol=seagreen]
|||Thanks, found the info in the sqlserver online books 'How to restore a
database with a new name' .
"Keith Kratochvil" wrote:

> This is also a good test of the backup procedures that you should have in
> place. Knowing how to restore from a backup is important!
> --
> Keith
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:6C2998EA-E002-4919-954E-335060B62641@.microsoft.com...
> production
>

Clone SQL SERVER question

Hello Guys,
I am working in a clone of SQL server and I have some question:
NEW SQLSERVER = Copy of OLD SQLSERVER
As the new sql server "NEW SQLSERVER" has the same configuration as
the "OLD SQLSERVER" all the jobs and others setups have still the old
"OLD SQLSERVER" settings. I have already done this operation for the
backups.
update backupset
set machine_name= 'NEW SQLSERVER'
where machine_name = 'OLD SQLSERVER'
what do you advice me to do in order to set up this machine currently
Any suggestion will be appreciate
Ina
Hi
Have you scripted the jobs and changed any references to the old server? Are
there any hard coded references in Stored procedure, triggers, views etc?
DTS packages can be exported using the DTS Backup tool
http://www.sqldts.com/default.aspx?242
Are you using replication?
If the server is not on the same domain or if you had local accounts for
logins, you will have to change them see
http://support.microsoft.com/kb/246133/. It may be necessary to also change
orphaned users http://support.microsoft.com/kb/274188/
John
"ina" wrote:

> Hello Guys,
> I am working in a clone of SQL server and I have some question:
> NEW SQLSERVER = Copy of OLD SQLSERVER
> As the new sql server "NEW SQLSERVER" has the same configuration as
> the "OLD SQLSERVER" all the jobs and others setups have still the old
> "OLD SQLSERVER" settings. I have already done this operation for the
> backups.
> update backupset
> set machine_name= 'NEW SQLSERVER'
> where machine_name = 'OLD SQLSERVER'
> what do you advice me to do in order to set up this machine currently
> Any suggestion will be appreciate
> Ina
>
|||Thanks John,
I haven't done anything concerning replication, views and triggers in
the old server, I had only setup up backups and maintenance plans.
Thanks for this documents I will get through.
Ina
Thank you a lot for all
John Bell wrote:[vbcol=seagreen]
> Hi
> Have you scripted the jobs and changed any references to the old server? Are
> there any hard coded references in Stored procedure, triggers, views etc?
> DTS packages can be exported using the DTS Backup tool
> http://www.sqldts.com/default.aspx?242
> Are you using replication?
> If the server is not on the same domain or if you had local accounts for
> logins, you will have to change them see
> http://support.microsoft.com/kb/246133/. It may be necessary to also change
> orphaned users http://support.microsoft.com/kb/274188/
>
> John
> "ina" wrote:

Clone SQL SERVER question

Hello Guys,
I am working in a clone of SQL server and I have some question:
NEW SQLSERVER = Copy of OLD SQLSERVER
As the new sql server "NEW SQLSERVER" has the same configuration as
the "OLD SQLSERVER" all the jobs and others setups have still the old
"OLD SQLSERVER" settings. I have already done this operation for the
backups.
update backupset
set machine_name= 'NEW SQLSERVER'
where machine_name = 'OLD SQLSERVER'
what do you advice me to do in order to set up this machine currently
Any suggestion will be appreciate
InaHi
Have you scripted the jobs and changed any references to the old server? Are
there any hard coded references in Stored procedure, triggers, views etc?
DTS packages can be exported using the DTS Backup tool
http://www.sqldts.com/default.aspx?242
Are you using replication?
If the server is not on the same domain or if you had local accounts for
logins, you will have to change them see
http://support.microsoft.com/kb/246133/. It may be necessary to also change
orphaned users http://support.microsoft.com/kb/274188/
John
"ina" wrote:
> Hello Guys,
> I am working in a clone of SQL server and I have some question:
> NEW SQLSERVER = Copy of OLD SQLSERVER
> As the new sql server "NEW SQLSERVER" has the same configuration as
> the "OLD SQLSERVER" all the jobs and others setups have still the old
> "OLD SQLSERVER" settings. I have already done this operation for the
> backups.
> update backupset
> set machine_name= 'NEW SQLSERVER'
> where machine_name = 'OLD SQLSERVER'
> what do you advice me to do in order to set up this machine currently
> Any suggestion will be appreciate
> Ina
>|||Thanks John,
I haven't done anything concerning replication, views and triggers in
the old server, I had only setup up backups and maintenance plans.
Thanks for this documents I will get through.
Ina
Thank you a lot for all
John Bell wrote:
> Hi
> Have you scripted the jobs and changed any references to the old server? Are
> there any hard coded references in Stored procedure, triggers, views etc?
> DTS packages can be exported using the DTS Backup tool
> http://www.sqldts.com/default.aspx?242
> Are you using replication?
> If the server is not on the same domain or if you had local accounts for
> logins, you will have to change them see
> http://support.microsoft.com/kb/246133/. It may be necessary to also change
> orphaned users http://support.microsoft.com/kb/274188/
>
> John
> "ina" wrote:
> > Hello Guys,
> >
> > I am working in a clone of SQL server and I have some question:
> >
> > NEW SQLSERVER = Copy of OLD SQLSERVER
> >
> > As the new sql server "NEW SQLSERVER" has the same configuration as
> > the "OLD SQLSERVER" all the jobs and others setups have still the old
> > "OLD SQLSERVER" settings. I have already done this operation for the
> > backups.
> >
> > update backupset
> > set machine_name= 'NEW SQLSERVER'
> > where machine_name = 'OLD SQLSERVER'
> >
> > what do you advice me to do in order to set up this machine currently
> >
> > Any suggestion will be appreciate
> >
> > Ina
> >
> >

Clone SQL SERVER question

Hello Guys,
I am working in a clone of SQL server and I have some question:
NEW SQLSERVER = Copy of OLD SQLSERVER
As the new sql server "NEW SQLSERVER" has the same configuration as
the "OLD SQLSERVER" all the jobs and others setups have still the old
"OLD SQLSERVER" settings. I have already done this operation for the
backups.
update backupset
set machine_name= 'NEW SQLSERVER'
where machine_name = 'OLD SQLSERVER'
what do you advice me to do in order to set up this machine currently
Any suggestion will be appreciate
InaHi
Have you scripted the jobs and changed any references to the old server? Are
there any hard coded references in Stored procedure, triggers, views etc?
DTS packages can be exported using the DTS Backup tool
http://www.sqldts.com/default.aspx?242
Are you using replication?
If the server is not on the same domain or if you had local accounts for
logins, you will have to change them see
http://support.microsoft.com/kb/246133/. It may be necessary to also change
orphaned users http://support.microsoft.com/kb/274188/
John
"ina" wrote:

> Hello Guys,
> I am working in a clone of SQL server and I have some question:
> NEW SQLSERVER = Copy of OLD SQLSERVER
> As the new sql server "NEW SQLSERVER" has the same configuration as
> the "OLD SQLSERVER" all the jobs and others setups have still the old
> "OLD SQLSERVER" settings. I have already done this operation for the
> backups.
> update backupset
> set machine_name= 'NEW SQLSERVER'
> where machine_name = 'OLD SQLSERVER'
> what do you advice me to do in order to set up this machine currently
> Any suggestion will be appreciate
> Ina
>|||Thanks John,
I haven't done anything concerning replication, views and triggers in
the old server, I had only setup up backups and maintenance plans.
Thanks for this documents I will get through.
Ina
Thank you a lot for all
John Bell wrote:[vbcol=seagreen]
> Hi
> Have you scripted the jobs and changed any references to the old server? A
re
> there any hard coded references in Stored procedure, triggers, views etc?
> DTS packages can be exported using the DTS Backup tool
> http://www.sqldts.com/default.aspx?242
> Are you using replication?
> If the server is not on the same domain or if you had local accounts for
> logins, you will have to change them see
> http://support.microsoft.com/kb/246133/. It may be necessary to also chang
e
> orphaned users http://support.microsoft.com/kb/274188/
>
> John
> "ina" wrote:
>

Clone SQL DB (using VB?)

Hi all,
I'm having a SQL Server database called X. For testing and training, I
would like to make a copy of the entire DB to Y. This copy should
include all data, properties, indexes, views, etc. Unfortunately, all
my attempts to automate this failed...
Server = MSDE2a
Original database is a database marked for replication so I can't make
an easy copy...
App = VB6
- SQL job: fails on certain tables due to large 'memo' fields (ntext >
10Mb).
- DAO: trying to copy the tables from dbX to dbY fails while
getting/setting properties. Even when getting the fields (for each
srcField in srcTable.Fields; set destField = srcField) gives errors...
- ADOX: Haven't been able to get all the properties and indexes across.
ADOX doesn't seem to have all methods available to accomplish that.
Current idea is to take a script from the DB, adjust the paths and
names and use that to generate the DB. Afterwards, loop through all
tables and copy the contents from dbX to dbY.
While I haven't looked into it, I might run into a problem because
probably not all tools are installed on the application PC and without
them so I can't run SQL scripts from the command line... Not sure if I
can distribute all tools (MSDE) to make that working.
Another thing might be that I need to find out what the relative path
is to the server in order to create the files at the correct locations.
They are different because the app is on PC1 and the SQLserver is on
PC2. So I need to update the script (that wouldn't be the big problem)
but I'm not sure on how this would be interpreted in the above
scenario...
Is this the correct way to copy a DB including indexes and properties
(like default values, etc.)? Or is there something easier?
Many thanks,
GB
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>
Couldn't you simply backup the database on the source server and the copy
the backup file to a share on the destination server and do a restore?'
Jim
"Gijs Beukenoot" <Gijs.DOT.Beukenoot@.AT@.Wanadoo.DOT.nl> wrote in message
news:mn.ba4e7d52bd68e489.23024@.Wanadoo.DOT.nl...
> Hi all,
> I'm having a SQL Server database called X. For testing and training, I
> would like to make a copy of the entire DB to Y. This copy should include
> all data, properties, indexes, views, etc. Unfortunately, all my attempts
> to automate this failed...
> Server = MSDE2a
> Original database is a database marked for replication so I can't make an
> easy copy...
> App = VB6
> - SQL job: fails on certain tables due to large 'memo' fields (ntext >
> 10Mb).
> - DAO: trying to copy the tables from dbX to dbY fails while
> getting/setting properties. Even when getting the fields (for each
> srcField in srcTable.Fields; set destField = srcField) gives errors...
> - ADOX: Haven't been able to get all the properties and indexes across.
> ADOX doesn't seem to have all methods available to accomplish that.
> Current idea is to take a script from the DB, adjust the paths and names
> and use that to generate the DB. Afterwards, loop through all tables and
> copy the contents from dbX to dbY.
> While I haven't looked into it, I might run into a problem because
> probably not all tools are installed on the application PC and without
> them so I can't run SQL scripts from the command line... Not sure if I can
> distribute all tools (MSDE) to make that working.
> Another thing might be that I need to find out what the relative path is
> to the server in order to create the files at the correct locations. They
> are different because the app is on PC1 and the SQLserver is on PC2. So I
> need to update the script (that wouldn't be the big problem) but I'm not
> sure on how this would be interpreted in the above scenario...
> Is this the correct way to copy a DB including indexes and properties
> (like default values, etc.)? Or is there something easier?
> Many thanks,
> GB
> --
> Your eyes are weary from staring at the CRT. You feel sleepy. Notice how
> restful it is to watch the cursor blink. Close your eyes. The opinions
> stated above are yours. When I snap my fingers, you cannot imagine why you
> ever felt otherwise. <snap>
>
|||Jim Young drukte met precisie uit :
> Couldn't you simply backup the database on the source server and the copy the
> backup file to a share on the destination server and do a restore?'
> Jim
>
<<cut>>
Jim,
Both databases are on the same machine so that makes it a little easier
to do that. And, I've done that a few times (using Enterprise Manager
from another machine). The only problems I saw then is in the (odd)
naming of the databasefiles. I can't remember exactly, but I remember
the filenames where correct (dbY) but in some of the properties, the
internal name was still <path>\dbX. I'll admit, so far, this seems to
be the easiest way to accomplish all this. If I code it, the users
shouldn't be able to mess things up (like using EM to do this; it
really makes you read everything twice before you restore it the wrong
way...)
I'll guess I'll hop along that path then, thanks
GB
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>

Clone new database from existing

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

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

|||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|||

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

|||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|||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.|||Alright, I was able to do that through Express Manager this time. Much easier and safer. Thanks, both of you.

Rick