Creating a test copy of a database?

I was wondering if there is an easy way to "copy" an existing database,
complete with data, into a new one with a different name.
We have a complex SQL system that imports data from an external SQL source
(PervasiveSQL). We are in the process of upgrading that software to a much
newer version that has undergone major modifications, although I don't
_believe_ they impact the import process. I have already made a copy of the
PervasiveSQL database in the new format. What I would like to do now is copy
our SQLServer database, then run our importer against the two test databases.
Any advice?
Maury
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
>
The easiest way usually is one of the following:
Backup the source database and then restore it with a different name. This
can be done in a production environment with no downtime.
Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Use sp_attach_db to attach the copied files with a different database name.

> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hello,
You will have to BACKUP the database and Restore the database using a new
name.
1. Backup the database usuing BACKUP DATABASE command
2. Restore the database with new name specifying MOVE optiion.
take a look into BACKUP DATABASE and RESTORE DATABASE command with MOVE
option in Books online.
Thanks
Hari
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury
|||Maury Markowitz,
Take a full back of the db and restore it using a new database name and the
"with move" option. See "restore database" in BOL for more info.
AMB
"Maury Markowitz" wrote:

> I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of the
> PervasiveSQL database in the new format. What I would like to do now is copy
> our SQLServer database, then run our importer against the two test databases.
> Any advice?
> Maury
|||Backup and restore is the easiest method - see "Copying a database using
BACKUP and RESTORE" in BOL.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||"Alejandro Mesa" wrote:

> Take a full back of the db and restore it using a new database name and the
> "with move" option. See "restore database" in BOL for more info.
Thanks! I'll start working on this now.
Maury
|||> Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Personally I think it's safer to detach using sp_detach_db. Stopping SQL
Server will not necessarily correctly initiate the files for attaching to
another server (at least I've seen plenty of reports of such).
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OCCGX8BXHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Personally I think it's safer to detach using sp_detach_db. Stopping SQL
> Server will not necessarily correctly initiate the files for attaching to
> another server (at least I've seen plenty of reports of such).
You know, I've heard that it's a "bad idea".
But have actually never heard of it not working, as long as the db was
correctly shut down.
But yeah, if the attach doesn't work, I'd go back to the original server and
try that.

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||> But yeah, if the attach doesn't work, I'd go back to the original server
> and try that.
Two more reasons to use an explicit detach:
(a) you only have to take one database offline, instead of ALL databases.
(b) if you in a clustered environment, shutting down SQL Server on the
active node will only initiate a failover, and won't free up the files for
copy because now they are active on the other node.
sql

Creating a test copy of a database?

I was wondering if there is an easy way to "copy" an existing database,
complete with data, into a new one with a different name.
We have a complex SQL system that imports data from an external SQL source
(PervasiveSQL). We are in the process of upgrading that software to a much
newer version that has undergone major modifications, although I don't
_believe_ they impact the import process. I have already made a copy of the
PervasiveSQL database in the new format. What I would like to do now is copy
our SQLServer database, then run our importer against the two test databases
.
Any advice?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
>
The easiest way usually is one of the following:
Backup the source database and then restore it with a different name. This
can be done in a production environment with no downtime.
Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Use sp_attach_db to attach the copied files with a different database name.

> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hello,
You will have to BACKUP the database and Restore the database using a new
name.
1. Backup the database usuing BACKUP DATABASE command
2. Restore the database with new name specifying MOVE optiion.
take a look into BACKUP DATABASE and RESTORE DATABASE command with MOVE
option in Books online.
Thanks
Hari
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury|||Maury Markowitz,
Take a full back of the db and restore it using a new database name and the
"with move" option. See "restore database" in BOL for more info.
AMB
"Maury Markowitz" wrote:

> I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of th
e
> PervasiveSQL database in the new format. What I would like to do now is co
py
> our SQLServer database, then run our importer against the two test databas
es.
> Any advice?
> Maury|||Backup and restore is the easiest method - see "Copying a database using
BACKUP and RESTORE" in BOL.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Alejandro Mesa" wrote:

> Take a full back of the db and restore it using a new database name and th
e
> "with move" option. See "restore database" in BOL for more info.
Thanks! I'll start working on this now.
Maury|||> Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Personally I think it's safer to detach using sp_detach_db. Stopping SQL
Server will not necessarily correctly initiate the files for attaching to
another server (at least I've seen plenty of reports of such).
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OCCGX8BXHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Personally I think it's safer to detach using sp_detach_db. Stopping SQL
> Server will not necessarily correctly initiate the files for attaching to
> another server (at least I've seen plenty of reports of such).
You know, I've heard that it's a "bad idea".
But have actually never heard of it not working, as long as the db was
correctly shut down.
But yeah, if the attach doesn't work, I'd go back to the original server and
try that.

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||> But yeah, if the attach doesn't work, I'd go back to the original server
> and try that.
Two more reasons to use an explicit detach:
(a) you only have to take one database offline, instead of ALL databases.
(b) if you in a clustered environment, shutting down SQL Server on the
active node will only initiate a failover, and won't free up the files for
copy because now they are active on the other node.

Creating a test copy of a database?

I was wondering if there is an easy way to "copy" an existing database,
complete with data, into a new one with a different name.
We have a complex SQL system that imports data from an external SQL source
(PervasiveSQL). We are in the process of upgrading that software to a much
newer version that has undergone major modifications, although I don't
_believe_ they impact the import process. I have already made a copy of the
PervasiveSQL database in the new format. What I would like to do now is copy
our SQLServer database, then run our importer against the two test databases.
Any advice?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
>
The easiest way usually is one of the following:
Backup the source database and then restore it with a different name. This
can be done in a production environment with no downtime.
Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Use sp_attach_db to attach the copied files with a different database name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hello,
You will have to BACKUP the database and Restore the database using a new
name.
1. Backup the database usuing BACKUP DATABASE command
2. Restore the database with new name specifying MOVE optiion.
take a look into BACKUP DATABASE and RESTORE DATABASE command with MOVE
option in Books online.
Thanks
Hari
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury|||Maury Markowitz,
Take a full back of the db and restore it using a new database name and the
"with move" option. See "restore database" in BOL for more info.
AMB
"Maury Markowitz" wrote:
> I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of the
> PervasiveSQL database in the new format. What I would like to do now is copy
> our SQLServer database, then run our importer against the two test databases.
> Any advice?
> Maury|||Backup and restore is the easiest method - see "Copying a database using
BACKUP and RESTORE" in BOL.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Alejandro Mesa" wrote:
> Take a full back of the db and restore it using a new database name and the
> "with move" option. See "restore database" in BOL for more info.
Thanks! I'll start working on this now.
Maury|||> Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Personally I think it's safer to detach using sp_detach_db. Stopping SQL
Server will not necessarily correctly initiate the files for attaching to
another server (at least I've seen plenty of reports of such).
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OCCGX8BXHHA.2212@.TK2MSFTNGP02.phx.gbl...
>> Or, stop SQL Server, make a copy of the files and then restart SQL
>> Server.
> Personally I think it's safer to detach using sp_detach_db. Stopping SQL
> Server will not necessarily correctly initiate the files for attaching to
> another server (at least I've seen plenty of reports of such).
You know, I've heard that it's a "bad idea".
But have actually never heard of it not working, as long as the db was
correctly shut down.
But yeah, if the attach doesn't work, I'd go back to the original server and
try that.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||> But yeah, if the attach doesn't work, I'd go back to the original server
> and try that.
Two more reasons to use an explicit detach:
(a) you only have to take one database offline, instead of ALL databases.
(b) if you in a clustered environment, shutting down SQL Server on the
active node will only initiate a failover, and won't free up the files for
copy because now they are active on the other node.

Creating a normalized database

Hi. I have a project I need to complete and I really don't know the first step I should take. Basically, we have a case management system that is normalized for the most part except for one major flaw: the clients table. Whenever we add a new case, we have to add the customer's name/address/phone etc. all over again. I would like to redo this current setup so we have one table just for clients and another table just for cases, so we don't have all this double entering all the time. Is there an easy way to do this or could someone point me in the right direction? It's on a SQL Server 2000 database. Thanks for your help!4 easy steps

CREATE the client table (ClientID, ClientName, Address...)

SELECT DISTINCT Client info from the case table into the new Client Table

Build Relationship between the 2 tables (on ClientID)

DELETE the redundant columns from case table|||You can use a ClientCase table with a many to many relationship between your Clients and your Cases tables.


Clients ClientCase Case
------ ------ ------
CliNumber --> CliNumber
CaseNumber <-- CaseNumber
Cliname CaseLeadAtty
CliAddress CaseSecondAtty


etc.|||tomh53,

Just curious...
What would be the need for the intermediate table, unless
one case number can have multiple clients?|||... unless one case number can have multiple clients?if a case only and forever belongs to only one client, then yeah, you don't need the many-to-many relationship table

however, note that you can implement a one-to-many relationship using a many-to-many relationship table -- just make sure (in your app logic) that you never store more than one client per case!!

then, when the day comes, and the case rolls in which requires two clients, you're all set!!

:) :)|||tomh53,

Just curious...
What would be the need for the intermediate table, unless
one case number can have multiple clients?

How about a class-action lawsuit?|||4 easy steps

CREATE the client table (ClientID, ClientName, Address...)

SELECT DISTINCT Client info from the case table into the new Client Table

Build Relationship between the 2 tables (on ClientID)

DELETE the redundant columns from case table
Thank you! That worked like a charm. I still have a few duplicates but it beats going through all of them manually. :)

Creating a dataset

Im trying to complete my function which will allow me to insert data into a table by referencing it in the relevant pages, the following code is what i am using to take the SQL query, execute it and return the resultant dataset in destResponse. However i am getting the following error message; "Compiler Error Message:CS1026: ) expected". And it is coming from the following line;

SqlCommand command =newSqlCommand("INSERT INTO" + strTableName + strData +") VALUES (" + strData +")";

Here is my code below, please feel free to criticise it, and im sure the error above is not the only error i will be getting.

publicstaticvoid SQLExecute(string strTableName,string strData)

{

DataSet dsetResponse =newDataSet();

// create connection object

strConnection =ConfigurationManager.AppSettings["strConnectionString"];

SqlConnection conn =newSqlConnection(strConnection);

SqlCommand command =newSqlCommand("INSERT INTO" + strTableName + strData +") VALUES (" + strData +")";command.Fill(dsetResponse,"table");

conn.Close();

return dsetResponse;

}

("INSERT INTO" + strTableName + strData +" VALUES " + strData);

|||

let me know if it is ok

|||

your line should be like below

SqlCommand command =newSqlCommand("INSERT INTO " + strTableName + strData +") VALUES (" + strData +")");

|||

Missing a space after INTO.

Missing a closing parenthesis before the first semi-colon.

Missing a literal space and opening parenthesis between the strTableName and strData -- should be strTableName + ' (' + strData.

Used strData for both the column name list AND the column values.

SqlCommand command =new SqlCommand("INSERT INTO " + strTableName +" (" + strColumnList +") VALUES (" + strData +")");

|||

Hi thanks for responding, the one that worked was Motleys suggestion, however my next task is to return the results in a dataset; this is my code so far, am i on the right track?

publicstaticvoid SQLExecute(string strTableName,string strData)

{

String strConnection =null;

try

{

DataSet dsetResponse =newDataSet();

// create connection object

strConnection =ConfigurationManager.AppSettings["strConnectionString"];

SqlConnection conn =newSqlConnection(strConnection);

SqlCommand command =newSqlCommand("INSERT INTO " + strTableName +" (" + strData +") VALUES (" + strData +")");command.Fill(dsetResponse,"table");

conn.Close();

return dsetResponse;

}

}

Create View in a Select statement ?

Greetings all. I'm a complete SQL novice, and well, I'm not even using
SQL, but I have a dBase control which utilizes SQL SELECT statements to
pull a rowset from a .DBF table.
I have a small table with a few fields, but for this query I only need
to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
the SELECT statement to return a rowset with the fields NAME and EMAIL,
where each entry for EMAIL1 or EMAIL2 will be output to it's own row.
example--
Orignal Table:
NAME EMAIL1 EMAIL2
ted ted@.x.com
chris chris@.a.com chris@.b.com
Query Result:
NAME EMAIL
ted ted@.x.com
chris chris@.a.com
chris chris@.b.com
Any ideas?
Much Thanks,
Christian K.Christian K wrote:

> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
Select Name, Email1 as Email from Table
Union
Select Name, Email2 as Email from Table where Email2 IS NOT NULL
HTH,
Stijn Verrept.|||select name, email1 as email from table
union
select name, email2 as email from table
order by name
This will eliminate duplicates as for example when, let's say Ted, has the
same email in email1 and email2.
Use Union All is you don't want to eliminate duplicates.
"Christian K" <ChristianK@.HeroData.com> wrote in message
news:ua%23YskD$FHA.2040@.TK2MSFTNGP14.phx.gbl...
> Greetings all. I'm a complete SQL novice, and well, I'm not even using
> SQL, but I have a dBase control which utilizes SQL SELECT statements to
> pull a rowset from a .DBF table.
> I have a small table with a few fields, but for this query I only need
> to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
> the SELECT statement to return a rowset with the fields NAME and EMAIL,
> where each entry for EMAIL1 or EMAIL2 will be output to it's own row.
> example--
> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
>
> Much Thanks,
> Christian K.
>|||If your SQL qry component will enable you to use the keyword union, here is
a
way...
create table email (name varchar(20) ,email1 varchar(50) ,email2 varchar(50)
)
insert email
select name = 'Jeff' ,email1 = 'me@.com.com' ,email2 = null
insert email
select name = 'Dave' ,email1 = 'dave@.com.com' ,email2 = 'david@.me.com'
select name ,email = email1
from email
where email1 is not null
union
select name ,email = email2
from email
where email2 is not null
order by name
drop table email
HTH
JeffP....
"Christian K" <ChristianK@.HeroData.com> wrote in message
news:ua%23YskD$FHA.2040@.TK2MSFTNGP14.phx.gbl...
> Greetings all. I'm a complete SQL novice, and well, I'm not even using
> SQL, but I have a dBase control which utilizes SQL SELECT statements to
> pull a rowset from a .DBF table.
> I have a small table with a few fields, but for this query I only need
> to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
> the SELECT statement to return a rowset with the fields NAME and EMAIL,
> where each entry for EMAIL1 or EMAIL2 will be output to it's own row.
> example--
> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
>
> Much Thanks,
> Christian K.
>|||Thank you both, it worked.
Christian K wrote:

> Greetings all. I'm a complete SQL novice, and well, I'm not even using
> SQL, but I have a dBase control which utilizes SQL SELECT statements
> to pull a rowset from a .DBF table.
> I have a small table with a few fields, but for this query I only need
> to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
> the SELECT statement to return a rowset with the fields NAME and
> EMAIL, where each entry for EMAIL1 or EMAIL2 will be output to it's
> own row.
> example--
> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
>
> Much Thanks,
> Christian K.|||Thank you both, it worked.
Christian K wrote:

> Greetings all. I'm a complete SQL novice, and well, I'm not even using
> SQL, but I have a dBase control which utilizes SQL SELECT statements
> to pull a rowset from a .DBF table.
> I have a small table with a few fields, but for this query I only need
> to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
> the SELECT statement to return a rowset with the fields NAME and
> EMAIL, where each entry for EMAIL1 or EMAIL2 will be output to it's
> own row.
> example--
> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
>
> Much Thanks,
> Christian K.