I just got my first job doing some DBA work (junior level) in a small company using SQL Server 2000.
I would like to make a copy of the production DB and use it for development of SP's, DTS packages and views.
What is the safest way for me to copy the entire production DB, to a new DB on the same server?
Should I create a new DB, and do a restore of the production backup?
Any help would be appreciated,
Thank you.restoring from a backup is probably the most common way.|||restoring from a backup is probably the most common way.
Should I restore to a 'new database' I create, or restore and just supply the name of 'development', for example...
Thanks for the reply|||Should I restore to a 'new database' I create, or restore and just supply the name of 'development', for example...
Thanks for the reply
you can try restoring the backup via Query Analyzer. Try consulting BOL for the syntax. Based on my experience it creates the database along with the devices. You can also specify that it move it's devices to a specific path.
Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts
Sunday, March 25, 2012
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
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.
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.
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.
Monday, March 19, 2012
Creating a PowerPoint Slide/PPT from Reporting Services Output
Hello,
I am trying to copy the rendered output from Reporting Services to create a
PowerPoint presentation. I understand that it is not possible to directly
render to a PPT and I am able to do so by saving the output IMAGE stream to a
file, and then loading PPT sildes from that file. However, my server will
have numerous hits and this is a very costly operation (in terms of disk
usage and performance). Is there any way or 3rd party tool by which I can
convert the Reporting Services output to a PPT without using an intermediate
file?
Thanks,
Ajay.It would take a lot of coding, but you could write a custom rendering
extension. PowerPoint is easy to automate; its object model is quite
straightforward. But from what I hear, custom rendering extensions are very
very (very very?) difficult to write! Your idea of saving to an image and
loading the image is probably the shortest path to a PPT solution.
Alternatively, you may be able to write or find a third-party solution that
will take HTML output and convert it to PPT. Either way, you're probably
either going to do some streaming or intermediate file handling.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ata" <ajay.abraham@.wipro.com> wrote in message
news:55EEA9F0-3618-4F3A-9BF8-3AD1C50EC89C@.microsoft.com...
> Hello,
> I am trying to copy the rendered output from Reporting Services to create
> a
> PowerPoint presentation. I understand that it is not possible to directly
> render to a PPT and I am able to do so by saving the output IMAGE stream
> to a
> file, and then loading PPT sildes from that file. However, my server will
> have numerous hits and this is a very costly operation (in terms of disk
> usage and performance). Is there any way or 3rd party tool by which I can
> convert the Reporting Services output to a PPT without using an
> intermediate
> file?
> Thanks,
> Ajay.|||I just finished doing a ppt presentation using print screen to capture the
window. Then I used the cropping and drawing tools within powerpoint to clip
out areas I wished to focus on, or render the whole window if that's what I
wanted. It was a simple process. You many want something different, but
there's a lot to be said for easy.
"Ata" wrote:
> Hello,
> I am trying to copy the rendered output from Reporting Services to create a
> PowerPoint presentation. I understand that it is not possible to directly
> render to a PPT and I am able to do so by saving the output IMAGE stream to a
> file, and then loading PPT sildes from that file. However, my server will
> have numerous hits and this is a very costly operation (in terms of disk
> usage and performance). Is there any way or 3rd party tool by which I can
> convert the Reporting Services output to a PPT without using an intermediate
> file?
> Thanks,
> Ajay.|||I'm working on copying the RS output image stream to the clipboard and from
there to PPT. However, there are some format-related issues as the RS output
is binary data but PPT expects a bit more sophistication I guess. Thanks for
the information.
Regards,
Ajay.
"Jeff A. Stucker" wrote:
> It would take a lot of coding, but you could write a custom rendering
> extension. PowerPoint is easy to automate; its object model is quite
> straightforward. But from what I hear, custom rendering extensions are very
> very (very very?) difficult to write! Your idea of saving to an image and
> loading the image is probably the shortest path to a PPT solution.
> Alternatively, you may be able to write or find a third-party solution that
> will take HTML output and convert it to PPT. Either way, you're probably
> either going to do some streaming or intermediate file handling.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com|||Sorry! I'm not sure I understood you properly. Are you using the Print Screen
key on the keyboard or doing this programmatically in some way?
"CGW" wrote:
> I just finished doing a ppt presentation using print screen to capture the
> window. Then I used the cropping and drawing tools within powerpoint to clip
> out areas I wished to focus on, or render the whole window if that's what I
> wanted. It was a simple process. You many want something different, but
> there's a lot to be said for easy.
I am trying to copy the rendered output from Reporting Services to create a
PowerPoint presentation. I understand that it is not possible to directly
render to a PPT and I am able to do so by saving the output IMAGE stream to a
file, and then loading PPT sildes from that file. However, my server will
have numerous hits and this is a very costly operation (in terms of disk
usage and performance). Is there any way or 3rd party tool by which I can
convert the Reporting Services output to a PPT without using an intermediate
file?
Thanks,
Ajay.It would take a lot of coding, but you could write a custom rendering
extension. PowerPoint is easy to automate; its object model is quite
straightforward. But from what I hear, custom rendering extensions are very
very (very very?) difficult to write! Your idea of saving to an image and
loading the image is probably the shortest path to a PPT solution.
Alternatively, you may be able to write or find a third-party solution that
will take HTML output and convert it to PPT. Either way, you're probably
either going to do some streaming or intermediate file handling.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ata" <ajay.abraham@.wipro.com> wrote in message
news:55EEA9F0-3618-4F3A-9BF8-3AD1C50EC89C@.microsoft.com...
> Hello,
> I am trying to copy the rendered output from Reporting Services to create
> a
> PowerPoint presentation. I understand that it is not possible to directly
> render to a PPT and I am able to do so by saving the output IMAGE stream
> to a
> file, and then loading PPT sildes from that file. However, my server will
> have numerous hits and this is a very costly operation (in terms of disk
> usage and performance). Is there any way or 3rd party tool by which I can
> convert the Reporting Services output to a PPT without using an
> intermediate
> file?
> Thanks,
> Ajay.|||I just finished doing a ppt presentation using print screen to capture the
window. Then I used the cropping and drawing tools within powerpoint to clip
out areas I wished to focus on, or render the whole window if that's what I
wanted. It was a simple process. You many want something different, but
there's a lot to be said for easy.
"Ata" wrote:
> Hello,
> I am trying to copy the rendered output from Reporting Services to create a
> PowerPoint presentation. I understand that it is not possible to directly
> render to a PPT and I am able to do so by saving the output IMAGE stream to a
> file, and then loading PPT sildes from that file. However, my server will
> have numerous hits and this is a very costly operation (in terms of disk
> usage and performance). Is there any way or 3rd party tool by which I can
> convert the Reporting Services output to a PPT without using an intermediate
> file?
> Thanks,
> Ajay.|||I'm working on copying the RS output image stream to the clipboard and from
there to PPT. However, there are some format-related issues as the RS output
is binary data but PPT expects a bit more sophistication I guess. Thanks for
the information.
Regards,
Ajay.
"Jeff A. Stucker" wrote:
> It would take a lot of coding, but you could write a custom rendering
> extension. PowerPoint is easy to automate; its object model is quite
> straightforward. But from what I hear, custom rendering extensions are very
> very (very very?) difficult to write! Your idea of saving to an image and
> loading the image is probably the shortest path to a PPT solution.
> Alternatively, you may be able to write or find a third-party solution that
> will take HTML output and convert it to PPT. Either way, you're probably
> either going to do some streaming or intermediate file handling.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com|||Sorry! I'm not sure I understood you properly. Are you using the Print Screen
key on the keyboard or doing this programmatically in some way?
"CGW" wrote:
> I just finished doing a ppt presentation using print screen to capture the
> window. Then I used the cropping and drawing tools within powerpoint to clip
> out areas I wished to focus on, or render the whole window if that's what I
> wanted. It was a simple process. You many want something different, but
> there's a lot to be said for easy.
Thursday, March 8, 2012
Creating a local copy of a database
Hi all,
I'm having a problem right now. I'm trying to create an offline copy of a database(live) in my local machine. The database is maintained in a different server machine and I want a copy of the particular DB as an offline copy.
The problem is there's DTS feature in SQL Server 2000 but I don't find any such tool in SQL Server 2005 edition. Can anyone please guide me on how to do this process?
Thanks & Regards,
ThunderRock
hi thunder,
Dts is now known as SSIS and it is found on VS2005
for the solution.
1. you can detach the database and copy the datafiles and attach it to the destination servers
2. you can use the database import export wizrd
|||
Hi Joeydj,
Thanks for the reply.
Now I've already done that but I wanted to know if there was some other method by which the complete Db can be imported. Because if we go by that method, we don't get the stored procedures as well as triggers and functions present in the source DB. So I want to know if there is any method by which the SPs, triggers as well as functions can be imported. We can write a query script and do it manually. But is there any features provided in Sql Server 2005 by which we can do it directly?
Another thing I noticed in the new database is that the data which get imported into the local machine(i.e the target DB) doesn't reflect the dependencies of the DB Objects present in the DB. So how do we go about solving this issue also?
Can you please guide on this? Anyone who have done this before and have some idea, please guide.
Thanks,
ThunderRock
|||ThunderRock,
I just completed setting up database replication scenario that uses an SQL2005 db as a 'gold standard', and replication to a subscriber to an SqlExpress database. The subscriber db can be used offline for inquires in our case. We use the database snapshot to provide inquiry services for the subscriber when there is a network or preferred application downtime.
With replication options setup appropriately, you can have the replicated database include whatever objects you wish. Our case is only using snapshot replication, as that is all we require, but from what I understand other types of replication would work in like manner.
We get our source data from a foreign host system as a huge text file, and we use a custom app to transform it, and bulk copy it into our Sql2005 db. Then, we've setup jobs to perform the replication. On the subscriber end, we installed SqlExpress with Advanced Tools, and set it up as a subscriber to the Sql2005 db. We developed a simple windows form application to use for inquiry on the local database, and deployed the app to the subscriber desktop. It is functioning as intended and allows our users to inquire on the mission critical data during those odd periods of network or preferred application downtime.
Now, there may be some issues causing the issues you mention, there are some limitations on the amount of data, and supported memory configs, but our db has about 500,000 records, which is not huge, and not very many stored procedures at this point. So, we have not run into any showstoppers at this point.
Wednesday, March 7, 2012
Creating a fork in the road
Question the first: So my records are going along nicely, but I need them to split up (basically, I need to create a copy of the record and send one copy down one path, and another copy down another path). Any ideas how to do that?
Question the second: After I aggregate my records (down one of the paths), I need to store some columns as xml. Is there a tool for this?
Thanks for all your help!
Jim Work
Jim Work wrote:
Question the first: So my records are going along nicely, but I need them to split up (basically, I need to create a copy of the record and send one copy down one path, and another copy down another path). Any ideas how to do that?
Use the multicast transformation.|||For "forking" the data like that, you can use the Multicast component in the data flow tab.|||Oh, wow! Thanks a bunch!
Jim Work
Creating a DTS package programaticaly
Hi,
I want to create a DTS package programatically (preferably in
C#.net),which will copy all my tables from a oracle database to my
sql-server database.
Can anybody help me doing this?
Thanks
PatnayakBOL documents all of the DTS API:
http://msdn.microsoft.com/library/e...spapps_21rn.asp
I want to create a DTS package programatically (preferably in
C#.net),which will copy all my tables from a oracle database to my
sql-server database.
Can anybody help me doing this?
Thanks
PatnayakBOL documents all of the DTS API:
http://msdn.microsoft.com/library/e...spapps_21rn.asp
Assuming you are using SQL2000 there's an easy way to familiarise yourself
with the basics of DTS programming. Create a sample DTS package (either
using the Wizard or the Designer), open it up in the Designer and choose
Package/Save As... from the menu. Select "Visual Basic File" from the
Location dropdown and specify a file name. This will generate the Visual
Basic code to create and execute your package. You can then dissect, edit
and extend the code as required.
Inevitably there will be some work involved if you want to move the
generated code to C# but the example of how to manipulate the DTS objects
should give you a helpful start.
--
David Portas
----
Please reply only to the newsgroup
--|||To add to David's response, you can find a cookbook and examples for SQL
Server 2000 DTS with .NET at http://www.sqldev.net/dts/DotNETCookBook.htm.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Pattnayak" <dpatnayak@.hotmail.com> wrote in message
news:a57a07c8.0401010301.3d175545@.posting.google.c om...
> Hi,
> I want to create a DTS package programatically (preferably in
> C#.net),which will copy all my tables from a oracle database to my
> sql-server database.
> Can anybody help me doing this?
> Thanks
> Patnayak
Friday, February 24, 2012
Creating a copy of a record
What is the simplest way to create a copy of a row?
The table in question has a primary key that is an identity field, which
will obviously need to have a different value, but otherwise I want to be
able to create a row that is identical to another one.
AFAIK Select Into only copies rows into a new table.
I could obviously retrieve each field from the original row (bar the primary
key) and INSERT a new record with this information, but since there are a
fair few columns, I wondered if there might be a simpler one-line SQL
instruction that will do the job...
Thanks in advance
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Think again. Why would you ever want to duplicate a row in a table?
(even with a different IDENTITY key).
In principle:
INSERT INTO YourTable (col1, col2, ...)
SELECT col1, col2, ...
FROM YourTable
WHERE /* some row */ ;
However, this ought to fail on a key violation because IDENTITY should
never be the only key of a table. Rethink your requirement and your
table design.
David Portas
SQL Server MVP
--|||Hi
CREATE TABLE #Test
(
pk INT NOT NULL PRIMARY KEY,
col CHAR(1),
col1 INT
)
INSERT INTO #Test VALUES (1,'a',20)
--Copy Row
INSERT INTO #Test
SELECT (SELECT COALESCE(MAX(pk),0)+1 FROM #test) AS pk,
col,col1 FROM #Test WHERE pk=1 --change to variable
SELECT * FROM #test
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%23cT1aMdvFHA.3688@.tk2msftngp13.phx.gbl...
> What is the simplest way to create a copy of a row?
> The table in question has a primary key that is an identity field, which
> will obviously need to have a different value, but otherwise I want to be
> able to create a row that is identical to another one.
> AFAIK Select Into only copies rows into a new table.
> I could obviously retrieve each field from the original row (bar the
> primary key) and INSERT a new record with this information, but since
> there are a fair few columns, I wondered if there might be a simpler
> one-line SQL instruction that will do the job...
> Thanks in advance
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127214710.180280.324430@.g43g2000cwa.googlegroups.com...
> Think again. Why would you ever want to duplicate a row in a table?
> (even with a different IDENTITY key).
> In principle:
> INSERT INTO YourTable (col1, col2, ...)
> SELECT col1, col2, ...
> FROM YourTable
> WHERE /* some row */ ;
> However, this ought to fail on a key violation because IDENTITY should
> never be the only key of a table. Rethink your requirement and your
> table design.
>
I can see where you are coming from, but I'm afraid my requirement is
genuine & valid.
The table in question hold orderlines. In this example an orderline is being
closed off, but a new copy of the line is to be added to the order.
Therefore, I hoped to copy original orderline (with a different PK
obviously) and then Update the original to close it off. [Obviously this is
a simplistic explanation).
As you can see, in this case the table design is fine. And the requirement,
IS the requirement. But how we satisfy the requirement is the question...
and hence my post.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23gxDjRdvFHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> CREATE TABLE #Test
> (
> pk INT NOT NULL PRIMARY KEY,
> col CHAR(1),
> col1 INT
> )
> INSERT INTO #Test VALUES (1,'a',20)
> --Copy Row
> INSERT INTO #Test
> SELECT (SELECT COALESCE(MAX(pk),0)+1 FROM #test) AS pk,
> col,col1 FROM #Test WHERE pk=1 --change to variable
> SELECT * FROM #test
Uri,
Thanks for this.
Q. Will this still work if my PK is an Identity field?
Chris|||> As you can see, in this case the table design is fine.
I don't see that. There are serious problems with tables that have only
an IDENTITY key. The consequences of storing duplicate data make it
very difficult (perhaps impossible in some conditions) to validate and
integrate data without logical keys. Also, there are practical
programming issues in TSQL because of the way IDENTITY values are
assigned in multiple row INSERTs.
Putting that aside, why store redundant data? If the row is going to be
modified anyway as you have said then why not insert the modified row
instead of duplicating an existing one and incurring the overhead of an
extra update? Seems like you are paying a high price in integrity and
performance for no discernable benefit.
David Portas
SQL Server MVP
--|||CJM
I think David has
already answered this question , however you are free to try it.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:u4WSrhdvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23gxDjRdvFHA.612@.TK2MSFTNGP10.phx.gbl...
> Uri,
> Thanks for this.
> Q. Will this still work if my PK is an Identity field?
> Chris
>|||> I can see where you are coming from, but I'm afraid my requirement is
> genuine & valid.
> The table in question hold orderlines. In this example an orderline is
> being closed off, but a new copy of the line is to be added to the order.
WHY? What is different about the row? Why does it need to be deleted and
re-inserted?
A|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127217457.000463.24290@.g44g2000cwa.googlegroups.com...
> I don't see that. There are serious problems with tables that have only
> an IDENTITY key. The consequences of storing duplicate data make it
> very difficult (perhaps impossible in some conditions) to validate and
> integrate data without logical keys. Also, there are practical
> programming issues in TSQL because of the way IDENTITY values are
> assigned in multiple row INSERTs.
>
Well I'll have to reserve judgement until I have digested all this... (which
I will).
What alternative would you offer instead of using an identity field?
I've not noticed any significant issues with Identity fields, but I'm always
open to improving my knowledge and my techniques...
> Putting that aside, why store redundant data? If the row is going to be
> modified anyway as you have said then why not insert the modified row
> instead of duplicating an existing one and incurring the overhead of an
> extra update? Seems like you are paying a high price in integrity and
> performance for no discernable benefit.
>
What data is redundant? The original record? No, not so. It remains an
essential part of the order. It would take too long to truly put this into
the right context, but suffice to say that the new row is NOT replacing the
old row. Both will co-exist and both are essential.
I've adapted the INSERT INTO statement that you suggested, and I now have
one command that achieves what I need. I don't see how it could get any more
efficient.|||-- if ur table has huge no of columns, u can try
select * into #t from tbl_name where ...
insert into tbl_name
select * from #t
drop table #t
Rakesh
"CJM" wrote:
> What is the simplest way to create a copy of a row?
> The table in question has a primary key that is an identity field, which
> will obviously need to have a different value, but otherwise I want to be
> able to create a row that is identical to another one.
> AFAIK Select Into only copies rows into a new table.
> I could obviously retrieve each field from the original row (bar the prima
ry
> key) and INSERT a new record with this information, but since there are a
> fair few columns, I wondered if there might be a simpler one-line SQL
> instruction that will do the job...
> Thanks in advance
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>
The table in question has a primary key that is an identity field, which
will obviously need to have a different value, but otherwise I want to be
able to create a row that is identical to another one.
AFAIK Select Into only copies rows into a new table.
I could obviously retrieve each field from the original row (bar the primary
key) and INSERT a new record with this information, but since there are a
fair few columns, I wondered if there might be a simpler one-line SQL
instruction that will do the job...
Thanks in advance
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Think again. Why would you ever want to duplicate a row in a table?
(even with a different IDENTITY key).
In principle:
INSERT INTO YourTable (col1, col2, ...)
SELECT col1, col2, ...
FROM YourTable
WHERE /* some row */ ;
However, this ought to fail on a key violation because IDENTITY should
never be the only key of a table. Rethink your requirement and your
table design.
David Portas
SQL Server MVP
--|||Hi
CREATE TABLE #Test
(
pk INT NOT NULL PRIMARY KEY,
col CHAR(1),
col1 INT
)
INSERT INTO #Test VALUES (1,'a',20)
--Copy Row
INSERT INTO #Test
SELECT (SELECT COALESCE(MAX(pk),0)+1 FROM #test) AS pk,
col,col1 FROM #Test WHERE pk=1 --change to variable
SELECT * FROM #test
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%23cT1aMdvFHA.3688@.tk2msftngp13.phx.gbl...
> What is the simplest way to create a copy of a row?
> The table in question has a primary key that is an identity field, which
> will obviously need to have a different value, but otherwise I want to be
> able to create a row that is identical to another one.
> AFAIK Select Into only copies rows into a new table.
> I could obviously retrieve each field from the original row (bar the
> primary key) and INSERT a new record with this information, but since
> there are a fair few columns, I wondered if there might be a simpler
> one-line SQL instruction that will do the job...
> Thanks in advance
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127214710.180280.324430@.g43g2000cwa.googlegroups.com...
> Think again. Why would you ever want to duplicate a row in a table?
> (even with a different IDENTITY key).
> In principle:
> INSERT INTO YourTable (col1, col2, ...)
> SELECT col1, col2, ...
> FROM YourTable
> WHERE /* some row */ ;
> However, this ought to fail on a key violation because IDENTITY should
> never be the only key of a table. Rethink your requirement and your
> table design.
>
I can see where you are coming from, but I'm afraid my requirement is
genuine & valid.
The table in question hold orderlines. In this example an orderline is being
closed off, but a new copy of the line is to be added to the order.
Therefore, I hoped to copy original orderline (with a different PK
obviously) and then Update the original to close it off. [Obviously this is
a simplistic explanation).
As you can see, in this case the table design is fine. And the requirement,
IS the requirement. But how we satisfy the requirement is the question...
and hence my post.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23gxDjRdvFHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> CREATE TABLE #Test
> (
> pk INT NOT NULL PRIMARY KEY,
> col CHAR(1),
> col1 INT
> )
> INSERT INTO #Test VALUES (1,'a',20)
> --Copy Row
> INSERT INTO #Test
> SELECT (SELECT COALESCE(MAX(pk),0)+1 FROM #test) AS pk,
> col,col1 FROM #Test WHERE pk=1 --change to variable
> SELECT * FROM #test
Uri,
Thanks for this.
Q. Will this still work if my PK is an Identity field?
Chris|||> As you can see, in this case the table design is fine.
I don't see that. There are serious problems with tables that have only
an IDENTITY key. The consequences of storing duplicate data make it
very difficult (perhaps impossible in some conditions) to validate and
integrate data without logical keys. Also, there are practical
programming issues in TSQL because of the way IDENTITY values are
assigned in multiple row INSERTs.
Putting that aside, why store redundant data? If the row is going to be
modified anyway as you have said then why not insert the modified row
instead of duplicating an existing one and incurring the overhead of an
extra update? Seems like you are paying a high price in integrity and
performance for no discernable benefit.
David Portas
SQL Server MVP
--|||CJM
I think David has
already answered this question , however you are free to try it.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:u4WSrhdvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23gxDjRdvFHA.612@.TK2MSFTNGP10.phx.gbl...
> Uri,
> Thanks for this.
> Q. Will this still work if my PK is an Identity field?
> Chris
>|||> I can see where you are coming from, but I'm afraid my requirement is
> genuine & valid.
> The table in question hold orderlines. In this example an orderline is
> being closed off, but a new copy of the line is to be added to the order.
WHY? What is different about the row? Why does it need to be deleted and
re-inserted?
A|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127217457.000463.24290@.g44g2000cwa.googlegroups.com...
> I don't see that. There are serious problems with tables that have only
> an IDENTITY key. The consequences of storing duplicate data make it
> very difficult (perhaps impossible in some conditions) to validate and
> integrate data without logical keys. Also, there are practical
> programming issues in TSQL because of the way IDENTITY values are
> assigned in multiple row INSERTs.
>
Well I'll have to reserve judgement until I have digested all this... (which
I will).
What alternative would you offer instead of using an identity field?
I've not noticed any significant issues with Identity fields, but I'm always
open to improving my knowledge and my techniques...
> Putting that aside, why store redundant data? If the row is going to be
> modified anyway as you have said then why not insert the modified row
> instead of duplicating an existing one and incurring the overhead of an
> extra update? Seems like you are paying a high price in integrity and
> performance for no discernable benefit.
>
What data is redundant? The original record? No, not so. It remains an
essential part of the order. It would take too long to truly put this into
the right context, but suffice to say that the new row is NOT replacing the
old row. Both will co-exist and both are essential.
I've adapted the INSERT INTO statement that you suggested, and I now have
one command that achieves what I need. I don't see how it could get any more
efficient.|||-- if ur table has huge no of columns, u can try
select * into #t from tbl_name where ...
insert into tbl_name
select * from #t
drop table #t
Rakesh
"CJM" wrote:
> What is the simplest way to create a copy of a row?
> The table in question has a primary key that is an identity field, which
> will obviously need to have a different value, but otherwise I want to be
> able to create a row that is identical to another one.
> AFAIK Select Into only copies rows into a new table.
> I could obviously retrieve each field from the original row (bar the prima
ry
> key) and INSERT a new record with this information, but since there are a
> fair few columns, I wondered if there might be a simpler one-line SQL
> instruction that will do the job...
> Thanks in advance
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>
creating a blank verion of the database
Hi all,
I need to create a blank version of my database. That is, all tabes are
empty ready for input. But also copy acroos all the triggers and sp's. This
on the same SQL server.
The purpose, is that I neede a completely fresh start for testing purposes
with different departments
Whats the best way to achieve this.
Thanks
RobertOne way is to generate DDL script from a database:
http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robert Bravery" <me@.u.com> wrote in message news:OCT9QLMWGHA.3800@.TK2MSFTNGP03.phx.gbl...[
color=darkred]
> Hi all,
> I need to create a blank version of my database. That is, all tabes are
> empty ready for input. But also copy acroos all the triggers and sp's. Thi
s
> on the same SQL server.
> The purpose, is that I neede a completely fresh start for testing purposes
> with different departments
> Whats the best way to achieve this.
> Thanks
> Robert
>[/color]|||if you're on SQL 2000, right click on your database, select all tasks
-> generate SQL script, then click show all, then go nuts with the
checkboxes, tick everything. This should give you a script that when
you run it will create a new database (so you'll have to change the
name it creates the db as if you're using the same server
Cheers
Will|||Script the database and execute the script for another database name.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Obtain the full script for all the object bearing on mind the collation and
go on.
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jens" wrote:
> Script the database and execute the script for another database name.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
I need to create a blank version of my database. That is, all tabes are
empty ready for input. But also copy acroos all the triggers and sp's. This
on the same SQL server.
The purpose, is that I neede a completely fresh start for testing purposes
with different departments
Whats the best way to achieve this.
Thanks
RobertOne way is to generate DDL script from a database:
http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robert Bravery" <me@.u.com> wrote in message news:OCT9QLMWGHA.3800@.TK2MSFTNGP03.phx.gbl...[
color=darkred]
> Hi all,
> I need to create a blank version of my database. That is, all tabes are
> empty ready for input. But also copy acroos all the triggers and sp's. Thi
s
> on the same SQL server.
> The purpose, is that I neede a completely fresh start for testing purposes
> with different departments
> Whats the best way to achieve this.
> Thanks
> Robert
>[/color]|||if you're on SQL 2000, right click on your database, select all tasks
-> generate SQL script, then click show all, then go nuts with the
checkboxes, tick everything. This should give you a script that when
you run it will create a new database (so you'll have to change the
name it creates the db as if you're using the same server
Cheers
Will|||Script the database and execute the script for another database name.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Obtain the full script for all the object bearing on mind the collation and
go on.
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jens" wrote:
> Script the database and execute the script for another database name.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
Tuesday, February 14, 2012
create view using data in seperate severs
Hello:
I'd like to create a view on server x which references tables on an
entirely seperate server. Is this possible? Is seems
strange to have to copy the tables over just to create a view. In
the view wizard I can't seem to 'browse' to the tables on the other
server.
The code I'm working with would conceptually be something like this:
select server name.database instance.owner.table.field
from server name.database instance.owner.table
where <field name> like 'xxxx%'
or something along those lines.
Any help would be appreciated!
Thanks.Thats right, you will not be able to browse for them. If you use SQL Server 2005, you can first create synonyms which are browsable in the designer or use the four part name syntax which will make them accessible for the designer to pull your joins together (if you want to). So Doing something like this:
Select * from LinkedServer.DatabaseName.SchemaOrOwner.ObjectName
will bring you the linked object in the design view.
HTH; Jens K. Suessmeyer.
http://www.sqlserver2005.de|||I'm using SQL 2000.
Below is the conceptual code. I continue to get errors with this approach.
SELECT *
FROM servertable.databaseinstance.owner.table INNER JOIN
servertable.databaseinstance.owner.table ON
databaseinstance.owner.table.field = databaseinstance.owner.table.field
INNER JOIN
servertable.databaseinstance.owner.table ON
databaseinstance.owner.table.field = databaseinstance.owner.table.field
INNER JOIN
servertable.databaseinstance.owner.table ON
databaseinstance.owner.table.field = databaseinstance.owner.table.field
INNER JOIN
servertable.databaseinstance.owner.table ON
databaseinstance.owner.table.field = databaseinstance.owner.table.field
Any thoughts on what I might be doing wrong? The server is a linked server. The tables have select permission.
Thanks.|||
I don′t know what servertable means, but it should be something like this here (using Aliases) which makes reading more straight forward.
SELECT * From
SomeLocalTable LT
INNER Join LinkedServername.databasename.owner.table LST
ON LST.Col1 = LT.Col1
(...and so on...)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||kubmg wrote:
I'm using SQL 2000. Below is the conceptual code. I continue to get errors with this approach.
SELECT *
FROM servertable.databaseinstance.owner.table INNER JOIN
servertable.databaseinstance.owner.table ON databaseinstance.owner.table.field = databaseinstance.owner.table.field INNER JOIN
servertable.databaseinstance.owner.table ON databaseinstance.owner.table.field = databaseinstance.owner.table.field INNER JOIN
servertable.databaseinstance.owner.table ON databaseinstance.owner.table.field = databaseinstance.owner.table.field INNER JOIN
servertable.databaseinstance.owner.table ON databaseinstance.owner.table.field = databaseinstance.owner.table.fieldAny thoughts on what I might be doing wrong? The server is a linked server. The tables have select permission.
Thanks.
kindly post the error your getting
|||BTW, servertable is the server name, sorry.When I run the above code I get the following error:
Server: Msg 117, Level 15, State 2, Line 3
The number name 'servername.databaseinstance.owner.table' contains more than the maximum number of prefixes. The maximum is 3.
If I run a simplified query like below I get returned values:
select *
from servername.databaseinstance.owner.table
where field = 'xxxxxxxx'
If I add to the select query the following (which I would like to do in the view), I get the following:
select servername.databaseinstance.owner.table.fieldname
from servername.databaseinstance.owner.table
where field = 'xxxxxxxx'
Server: Msg 117, Level 15, State 2, Line 1
The number name 'servername.databaseinstance.owner.table' contains more than the maximum number of prefixes. The maximum is 3.
The joining that occurs on the tables were defined in the view
design. I suppose they are correct as I do get valid
results.|||
Hi,
select servername.databaseinstance.owner.table.fieldname
from servername.databaseinstance.owner.table
where field = 'xxxxxxxx'
Why don′t you use an alias here or no alias, as you don′t use the full declaration in where part either.
select fieldname
from servername.databaseinstance.owner.table
where field = 'xxxxxxxx'
Or
select t1.fieldname
from servername.databaseinstance.owner.table t1
where t1.field = 'xxxxxxxx'
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)