Is it possible , without using the installation CD , to create a new
INSTANCE of SQL Server 2005 from an existing INSTANCE on the same server?
--
___________________________________
Need an IT job? http://www.ITjobfeed.comNo. (Sorry.)
RLF
"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:7d-dnT4Z_5Cy-0DbnZ2dnUVZ8v6dnZ2d@.bt.com...
> Is it possible , without using the installation CD , to create a new
> INSTANCE of SQL Server 2005 from an existing INSTANCE on the same server?
>
> --
>
> ___________________________________
> Need an IT job? http://www.ITjobfeed.com
>
>
>|||No Jack. You must start setup from CD\DVD again and choose another name for
your new instance. It's totally a new installation except for some common
services (SSIS etc.)
--
Ekrem Önsoy
"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:7d-dnT4Z_5Cy-0DbnZ2dnUVZ8v6dnZ2d@.bt.com...
> Is it possible , without using the installation CD , to create a new
> INSTANCE of SQL Server 2005 from an existing INSTANCE on the same server?
>
> --
>
> ___________________________________
> Need an IT job? http://www.ITjobfeed.com
>
>
>
Showing posts with label existing. Show all posts
Showing posts with label existing. Show all posts
Thursday, March 29, 2012
creating an existing db schema baseline
What is the best method of creating schema creation scripts that can be
stored into a version control system. The process of using em to
generate a script is not an appealing option. I am still learning the
MS Sql sys tables and have not found a useful list of all the codes &
types to join the tables etc.
stored into a version control system. The process of using em to
generate a script is not an appealing option. I am still learning the
MS Sql sys tables and have not found a useful list of all the codes &
types to join the tables etc.
mike
--
Posted via http://dbforums.comwukie <member30544@.dbforums.com> wrote in message news:<3242331.1060980041@.dbforums.com>...
> What is the best method of creating schema creation scripts that can be
> stored into a version control system. The process of using em to
> generate a script is not an appealing option. I am still learning the
> MS Sql sys tables and have not found a useful list of all the codes &
> types to join the tables etc.
>
> mike
I don't like the fact that all source code versioning systems are
using proprietary files instead of proven relational databases
(SourceSafe is not exception from this). The reasons for this are
probably RDBMS licensing costs in the past.
Database schema can be exported also as XML file, which can be further
manipulated. If you and your team have serious schema versioning needs
I suggest you to evaluate Meta Data Services in SQL Server 2000 and
XML. One article about this has been published in the MSDN Magazine:
http://msdn.microsoft.com/msdnmag/i...es/default.aspx
Metadata Repository can be created not only through Enterprise Manager
but also programmatically using Meta Data API. Further information
with examples can be found in Meta Data Services SDK 3.0, which can be
downloaded for free.
Sinisa Catic|||found what I was looking for...
in EM > Tools > Generate SQL Scripts. THis will create the total schema
of the existing database.
mike
any known issues with this tool??
--
Posted via http://dbforums.com
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.
Thursday, March 22, 2012
Creating a table from a view
Does anyone know a good way to create a table from an existing view...maybe
some jiggery pokery with sp_columns or something. Im tring to create a set
of tables from a set of views, and do the whole thing in batch so im hoping
to carry out the table creation dynamically
Thanks
GerardGenerally views are created from base tables. If you want to create a base
table from a view for some strange reason, perhaps you can use SELECT...
INTO.
However such tables, while can be handy in temporary data processing, in
general may be lacking many data integrity constraints.
Anith|||IF i use SELECT..INTO that will give me a temporary table, but is there
anyway to basically make a view into a physical table? i.e. in Oracle
"CREATE TABLE T AS SELECT * FROM AVIEW"
Thanks
Gerard
"Anith Sen" wrote:
> Generally views are created from base tables. If you want to create a base
> table from a view for some strange reason, perhaps you can use SELECT...
> INTO.
> However such tables, while can be handy in temporary data processing, in
> general may be lacking many data integrity constraints.
> --
> Anith
>
>|||Gerard, The SELECT...INTO will not give you a temporary table, it will give
you a physical table. I think what Anith was saying was that it might be
convenient to do this but not a best practice.
"Gerard" wrote:
> IF i use SELECT..INTO that will give me a temporary table, but is there
> anyway to basically make a view into a physical table? i.e. in Oracle
> "CREATE TABLE T AS SELECT * FROM AVIEW"
> Thanks
> Gerard
> "Anith Sen" wrote:
>|||Ah right, well it does what i want and thats all that matters, i know its no
t
best practice, but i just need a set of tables for reporting to be create
from a set of views. Cheers for your help guys
Gerard
"ZNICHTER" wrote:
> Gerard, The SELECT...INTO will not give you a temporary table, it will giv
e
> you a physical table. I think what Anith was saying was that it might be
> convenient to do this but not a best practice.
> "Gerard" wrote:
>|||Ah right well as long as it creates a physical table im happy, its just for
an ETL mechanism so it doesnt have to be too flashy. Cheers for your help
guys
Gerard
"ZNICHTER" wrote:
> Gerard, The SELECT...INTO will not give you a temporary table, it will giv
e
> you a physical table. I think what Anith was saying was that it might be
> convenient to do this but not a best practice.
> "Gerard" wrote:
>|||Znichter
Temporary tables ARE physical tables. They are just stored in the tempdb
database, and are not permanent.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"ZNICHTER" <ZNICHTER@.discussions.microsoft.com> wrote in message
news:04CFDAA9-93AF-44B6-BC57-5730BD1C0D98@.microsoft.com...
> Gerard, The SELECT...INTO will not give you a temporary table, it will
> give
> you a physical table. I think what Anith was saying was that it might be
> convenient to do this but not a best practice.
> "Gerard" wrote:
>
>|||> IF i use SELECT..INTO that will give me a temporary table,
Why do you say that? SELECT INTO doesn't behave any differently from CREATE
TABLE regarding the type
of table created:
SELECT ...
INTO #myTable
SELECT ...
INTO myTable
First example above creates a temp table, and second creates a regular norma
l table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerard" <Gerard@.discussions.microsoft.com> wrote in message
news:2B7EECE8-67D3-496F-BBBF-7FEE15F86029@.microsoft.com...
> IF i use SELECT..INTO that will give me a temporary table, but is there
> anyway to basically make a view into a physical table? i.e. in Oracle
> "CREATE TABLE T AS SELECT * FROM AVIEW"
> Thanks
> Gerard
> "Anith Sen" wrote:
>
some jiggery pokery with sp_columns or something. Im tring to create a set
of tables from a set of views, and do the whole thing in batch so im hoping
to carry out the table creation dynamically
Thanks
GerardGenerally views are created from base tables. If you want to create a base
table from a view for some strange reason, perhaps you can use SELECT...
INTO.
However such tables, while can be handy in temporary data processing, in
general may be lacking many data integrity constraints.
Anith|||IF i use SELECT..INTO that will give me a temporary table, but is there
anyway to basically make a view into a physical table? i.e. in Oracle
"CREATE TABLE T AS SELECT * FROM AVIEW"
Thanks
Gerard
"Anith Sen" wrote:
> Generally views are created from base tables. If you want to create a base
> table from a view for some strange reason, perhaps you can use SELECT...
> INTO.
> However such tables, while can be handy in temporary data processing, in
> general may be lacking many data integrity constraints.
> --
> Anith
>
>|||Gerard, The SELECT...INTO will not give you a temporary table, it will give
you a physical table. I think what Anith was saying was that it might be
convenient to do this but not a best practice.
"Gerard" wrote:
> IF i use SELECT..INTO that will give me a temporary table, but is there
> anyway to basically make a view into a physical table? i.e. in Oracle
> "CREATE TABLE T AS SELECT * FROM AVIEW"
> Thanks
> Gerard
> "Anith Sen" wrote:
>|||Ah right, well it does what i want and thats all that matters, i know its no
t
best practice, but i just need a set of tables for reporting to be create
from a set of views. Cheers for your help guys
Gerard
"ZNICHTER" wrote:
> Gerard, The SELECT...INTO will not give you a temporary table, it will giv
e
> you a physical table. I think what Anith was saying was that it might be
> convenient to do this but not a best practice.
> "Gerard" wrote:
>|||Ah right well as long as it creates a physical table im happy, its just for
an ETL mechanism so it doesnt have to be too flashy. Cheers for your help
guys
Gerard
"ZNICHTER" wrote:
> Gerard, The SELECT...INTO will not give you a temporary table, it will giv
e
> you a physical table. I think what Anith was saying was that it might be
> convenient to do this but not a best practice.
> "Gerard" wrote:
>|||Znichter
Temporary tables ARE physical tables. They are just stored in the tempdb
database, and are not permanent.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"ZNICHTER" <ZNICHTER@.discussions.microsoft.com> wrote in message
news:04CFDAA9-93AF-44B6-BC57-5730BD1C0D98@.microsoft.com...
> Gerard, The SELECT...INTO will not give you a temporary table, it will
> give
> you a physical table. I think what Anith was saying was that it might be
> convenient to do this but not a best practice.
> "Gerard" wrote:
>
>|||> IF i use SELECT..INTO that will give me a temporary table,
Why do you say that? SELECT INTO doesn't behave any differently from CREATE
TABLE regarding the type
of table created:
SELECT ...
INTO #myTable
SELECT ...
INTO myTable
First example above creates a temp table, and second creates a regular norma
l table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerard" <Gerard@.discussions.microsoft.com> wrote in message
news:2B7EECE8-67D3-496F-BBBF-7FEE15F86029@.microsoft.com...
> IF i use SELECT..INTO that will give me a temporary table, but is there
> anyway to basically make a view into a physical table? i.e. in Oracle
> "CREATE TABLE T AS SELECT * FROM AVIEW"
> Thanks
> Gerard
> "Anith Sen" wrote:
>
Monday, March 19, 2012
Creating a Push Subscription on existing database.
My subscriber database has a subset of the tables in the Publisher but,
otherwise the schema is exactly the same.
Using the Push Subscription Wizard and the Initialize Subscription screen,
one is presented with two options: (I am using transactional publication)
-Yes, initialize the schema and data
-No, the Subscriber alreday has the schema and data
If I pick the first option, the initialization will fail because it tries to
drop the tables and views and my tables have relationships and contraints on
them.
If I pick the second option, the stored procedures used to update
(synchronize) the subcriber do not get created in the subcriber data base.
If I create a new database instead, everything works as expected.
How do I create a Push subscription where the table structure is already
there; but I do need to insure the stored procedures required by Replication
get created on the subscriber database?
Bill
William R
if you are running SQL 2k above sp1 do this in your publication database
sp_addpublication 'dummy'
sp_replicationdboption 'pubs','publish','true'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor','TableNameYouAr
ePublishingAndWantToGenerateAProcFor'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor2','TableNameYouA
rePublishingAndWantToGenerateAProcFor2'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor3','TableNameYouA
rePublishingAndWantToGenerateAProcFor3'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor4','TableNameYouA
rePublishingAndWantToGenerateAProcFor4'
sp_scriptpublicationcustomprocs 'dummy'
this will generate the procs you need in the results pane, script them out
and then issue a
sp_droppublication 'dummy'
"WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
news:D46CC819-EFD7-47ED-B39B-BDD090DE4E62@.microsoft.com...
> My subscriber database has a subset of the tables in the Publisher but,
> otherwise the schema is exactly the same.
> Using the Push Subscription Wizard and the Initialize Subscription screen,
> one is presented with two options: (I am using transactional publication)
> -Yes, initialize the schema and data
> -No, the Subscriber alreday has the schema and data
> If I pick the first option, the initialization will fail because it tries
to
> drop the tables and views and my tables have relationships and contraints
on
> them.
> If I pick the second option, the stored procedures used to update
> (synchronize) the subcriber do not get created in the subcriber data base.
> If I create a new database instead, everything works as expected.
> How do I create a Push subscription where the table structure is already
> there; but I do need to insure the stored procedures required by
Replication
> get created on the subscriber database?
> Bill
> --
> William R
|||Hilary,
I was wondering if something like this would be the solution. Since there
are so many tables, I could use the create database option to create a dummy
subscriber and copy the procedures over to the real subcriber.
It seems rather odd, MS didn't think of such an option for the wizard though.
Thank you for your response.
Bill
"Hilary Cotter" wrote:
> if you are running SQL 2k above sp1 do this in your publication database
> sp_addpublication 'dummy'
> sp_replicationdboption 'pubs','publish','true'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor','TableNameYouAr
> ePublishingAndWantToGenerateAProcFor'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor2','TableNameYouA
> rePublishingAndWantToGenerateAProcFor2'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor3','TableNameYouA
> rePublishingAndWantToGenerateAProcFor3'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor4','TableNameYouA
> rePublishingAndWantToGenerateAProcFor4'
> sp_scriptpublicationcustomprocs 'dummy'
> this will generate the procs you need in the results pane, script them out
> and then issue a
> sp_droppublication 'dummy'
>
> "WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
> news:D46CC819-EFD7-47ED-B39B-BDD090DE4E62@.microsoft.com...
> to
> on
> Replication
>
>
|||That is another way, but it is more work.
I would advise you however to script out the publishing database, create a
database called pub, and a database called sub.
In pub, run the creation script. Then run your publication script (changing
the publication name), and then create and push your subscription to sub.
This way your snapshot generation time will be very very fast and the impact
on your publisher will be low.
"WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
news:AB0769C1-646E-4406-8B50-68A60BE62109@.microsoft.com...[vbcol=seagreen]
> Hilary,
> I was wondering if something like this would be the solution. Since there
> are so many tables, I could use the create database option to create a
> dummy
> subscriber and copy the procedures over to the real subcriber.
> It seems rather odd, MS didn't think of such an option for the wizard
> though.
> Thank you for your response.
> Bill
>
> "Hilary Cotter" wrote:
otherwise the schema is exactly the same.
Using the Push Subscription Wizard and the Initialize Subscription screen,
one is presented with two options: (I am using transactional publication)
-Yes, initialize the schema and data
-No, the Subscriber alreday has the schema and data
If I pick the first option, the initialization will fail because it tries to
drop the tables and views and my tables have relationships and contraints on
them.
If I pick the second option, the stored procedures used to update
(synchronize) the subcriber do not get created in the subcriber data base.
If I create a new database instead, everything works as expected.
How do I create a Push subscription where the table structure is already
there; but I do need to insure the stored procedures required by Replication
get created on the subscriber database?
Bill
William R
if you are running SQL 2k above sp1 do this in your publication database
sp_addpublication 'dummy'
sp_replicationdboption 'pubs','publish','true'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor','TableNameYouAr
ePublishingAndWantToGenerateAProcFor'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor2','TableNameYouA
rePublishingAndWantToGenerateAProcFor2'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor3','TableNameYouA
rePublishingAndWantToGenerateAProcFor3'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor4','TableNameYouA
rePublishingAndWantToGenerateAProcFor4'
sp_scriptpublicationcustomprocs 'dummy'
this will generate the procs you need in the results pane, script them out
and then issue a
sp_droppublication 'dummy'
"WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
news:D46CC819-EFD7-47ED-B39B-BDD090DE4E62@.microsoft.com...
> My subscriber database has a subset of the tables in the Publisher but,
> otherwise the schema is exactly the same.
> Using the Push Subscription Wizard and the Initialize Subscription screen,
> one is presented with two options: (I am using transactional publication)
> -Yes, initialize the schema and data
> -No, the Subscriber alreday has the schema and data
> If I pick the first option, the initialization will fail because it tries
to
> drop the tables and views and my tables have relationships and contraints
on
> them.
> If I pick the second option, the stored procedures used to update
> (synchronize) the subcriber do not get created in the subcriber data base.
> If I create a new database instead, everything works as expected.
> How do I create a Push subscription where the table structure is already
> there; but I do need to insure the stored procedures required by
Replication
> get created on the subscriber database?
> Bill
> --
> William R
|||Hilary,
I was wondering if something like this would be the solution. Since there
are so many tables, I could use the create database option to create a dummy
subscriber and copy the procedures over to the real subcriber.
It seems rather odd, MS didn't think of such an option for the wizard though.
Thank you for your response.
Bill
"Hilary Cotter" wrote:
> if you are running SQL 2k above sp1 do this in your publication database
> sp_addpublication 'dummy'
> sp_replicationdboption 'pubs','publish','true'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor','TableNameYouAr
> ePublishingAndWantToGenerateAProcFor'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor2','TableNameYouA
> rePublishingAndWantToGenerateAProcFor2'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor3','TableNameYouA
> rePublishingAndWantToGenerateAProcFor3'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor4','TableNameYouA
> rePublishingAndWantToGenerateAProcFor4'
> sp_scriptpublicationcustomprocs 'dummy'
> this will generate the procs you need in the results pane, script them out
> and then issue a
> sp_droppublication 'dummy'
>
> "WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
> news:D46CC819-EFD7-47ED-B39B-BDD090DE4E62@.microsoft.com...
> to
> on
> Replication
>
>
|||That is another way, but it is more work.
I would advise you however to script out the publishing database, create a
database called pub, and a database called sub.
In pub, run the creation script. Then run your publication script (changing
the publication name), and then create and push your subscription to sub.
This way your snapshot generation time will be very very fast and the impact
on your publisher will be low.
"WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
news:AB0769C1-646E-4406-8B50-68A60BE62109@.microsoft.com...[vbcol=seagreen]
> Hilary,
> I was wondering if something like this would be the solution. Since there
> are so many tables, I could use the create database option to create a
> dummy
> subscriber and copy the procedures over to the real subcriber.
> It seems rather odd, MS didn't think of such an option for the wizard
> though.
> Thank you for your response.
> Bill
>
> "Hilary Cotter" wrote:
Creating a project from an existing Database
I have the full blown Microsoft SQL Server Management Studio (MSSMS) installed on my workstation.
We have a number of existing databases that I'd like to manage with MSSMS and put into source control.
How do I get MSSMS to "import" or "Convert" an existing SQL server 2000 database into a project that I can manage with MSSMS? We have not used Source safe up to this point, but would like to start doing so now.
This seems like it ought to be explained well up front in any discussion of converting from SQL 2000 to SQL 2005 or installing 2005, but I can't find ANYTHING useful in the BOL or other help.
Thanks for any help you can give me.
-Rob Marmion
All what you need is to script your code (and other objects, if you'd like) into separate files and add them to the SSMS project (simply drag'n'dropping). The problem is that SSMS is not able to script your database objects into separate files, only into one, but you can use Enterprise Manager "Generate SQL Script" feature.Sunday, March 11, 2012
Creating a new server in an existing server group
(SQL 2000)
Hi,
I'm trying to create a new server in an existing server group, however there
doesn't seem to be an option to do this. The closest I can see is "New SQL
Server registration" but when I try to use this I need to pick from a list o
f
existing servers. If I type in a new server name I get a status message
saying: SQL server doesn't exist or access denied. I have sa permissions so
I
doubt it would be a permissions issue.
Any ideas as to what I might be doing wrong?
Many thanks for any ideas on this in advance
AntHi
"Ant" wrote:
> (SQL 2000)
> Hi,
> I'm trying to create a new server in an existing server group, however the
re
> doesn't seem to be an option to do this. The closest I can see is "New SQL
> Server registration" but when I try to use this I need to pick from a list
of
> existing servers. If I type in a new server name I get a status message
> saying: SQL server doesn't exist or access denied. I have sa permissions s
o I
> doubt it would be a permissions issue.
If you right click on the Server Group you wish to add to, then you can
choose the New Server Registration option and type in the name of the new
server rather than browse for it. As the server is not on the list then it
may not have the network protocols enabled. This could be the reason for you
r
error message. Try connecting to the server using Query Analyser (both
locally and on the server itself) and see if you get this message. You can
check the protocols enabled by using the Server Network Utility on the
server. You may also want to make sure that the appropriate Service Packs
have been applied to this SQL Server instance (SELECT @.@.VERSION).
> Any ideas as to what I might be doing wrong?
> Many thanks for any ideas on this in advance
> Ant
>
John|||hi
try this:
go to start->programs->Microsoft SQL->client network utility (CNU)
on the CNU create an alias using tcp protocol, and specify the port number
that the sql instance is using (default is 1433)
on the name for the alias use a friendly name, so it's easy to use.
go to the sql enterprise manager, right click on the server group where you
want to add the sql instance, and use the alias friendly name you used on th
e
CNU.
and that's it.
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
Hi,
I'm trying to create a new server in an existing server group, however there
doesn't seem to be an option to do this. The closest I can see is "New SQL
Server registration" but when I try to use this I need to pick from a list o
f
existing servers. If I type in a new server name I get a status message
saying: SQL server doesn't exist or access denied. I have sa permissions so
I
doubt it would be a permissions issue.
Any ideas as to what I might be doing wrong?
Many thanks for any ideas on this in advance
AntHi
"Ant" wrote:
> (SQL 2000)
> Hi,
> I'm trying to create a new server in an existing server group, however the
re
> doesn't seem to be an option to do this. The closest I can see is "New SQL
> Server registration" but when I try to use this I need to pick from a list
of
> existing servers. If I type in a new server name I get a status message
> saying: SQL server doesn't exist or access denied. I have sa permissions s
o I
> doubt it would be a permissions issue.
If you right click on the Server Group you wish to add to, then you can
choose the New Server Registration option and type in the name of the new
server rather than browse for it. As the server is not on the list then it
may not have the network protocols enabled. This could be the reason for you
r
error message. Try connecting to the server using Query Analyser (both
locally and on the server itself) and see if you get this message. You can
check the protocols enabled by using the Server Network Utility on the
server. You may also want to make sure that the appropriate Service Packs
have been applied to this SQL Server instance (SELECT @.@.VERSION).
> Any ideas as to what I might be doing wrong?
> Many thanks for any ideas on this in advance
> Ant
>
John|||hi
try this:
go to start->programs->Microsoft SQL->client network utility (CNU)
on the CNU create an alias using tcp protocol, and specify the port number
that the sql instance is using (default is 1433)
on the name for the alias use a friendly name, so it's easy to use.
go to the sql enterprise manager, right click on the server group where you
want to add the sql instance, and use the alias friendly name you used on th
e
CNU.
and that's it.
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
Creating a new server in an existing server group
(SQL 2000)
Hi,
I'm trying to create a new server in an existing server group, however there
doesn't seem to be an option to do this. The closest I can see is "New SQL
Server registration" but when I try to use this I need to pick from a list of
existing servers. If I type in a new server name I get a status message
saying: SQL server doesn't exist or access denied. I have sa permissions so I
doubt it would be a permissions issue.
Any ideas as to what I might be doing wrong?
Many thanks for any ideas on this in advance
Ant
Hi
"Ant" wrote:
> (SQL 2000)
> Hi,
> I'm trying to create a new server in an existing server group, however there
> doesn't seem to be an option to do this. The closest I can see is "New SQL
> Server registration" but when I try to use this I need to pick from a list of
> existing servers. If I type in a new server name I get a status message
> saying: SQL server doesn't exist or access denied. I have sa permissions so I
> doubt it would be a permissions issue.
If you right click on the Server Group you wish to add to, then you can
choose the New Server Registration option and type in the name of the new
server rather than browse for it. As the server is not on the list then it
may not have the network protocols enabled. This could be the reason for your
error message. Try connecting to the server using Query Analyser (both
locally and on the server itself) and see if you get this message. You can
check the protocols enabled by using the Server Network Utility on the
server. You may also want to make sure that the appropriate Service Packs
have been applied to this SQL Server instance (SELECT @.@.VERSION).
> Any ideas as to what I might be doing wrong?
> Many thanks for any ideas on this in advance
> Ant
>
John
|||hi
try this:
go to start->programs->Microsoft SQL->client network utility (CNU)
on the CNU create an alias using tcp protocol, and specify the port number
that the sql instance is using (default is 1433)
on the name for the alias use a friendly name, so it's easy to use.
go to the sql enterprise manager, right click on the server group where you
want to add the sql instance, and use the alias friendly name you used on the
CNU.
and that's it.
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
Hi,
I'm trying to create a new server in an existing server group, however there
doesn't seem to be an option to do this. The closest I can see is "New SQL
Server registration" but when I try to use this I need to pick from a list of
existing servers. If I type in a new server name I get a status message
saying: SQL server doesn't exist or access denied. I have sa permissions so I
doubt it would be a permissions issue.
Any ideas as to what I might be doing wrong?
Many thanks for any ideas on this in advance
Ant
Hi
"Ant" wrote:
> (SQL 2000)
> Hi,
> I'm trying to create a new server in an existing server group, however there
> doesn't seem to be an option to do this. The closest I can see is "New SQL
> Server registration" but when I try to use this I need to pick from a list of
> existing servers. If I type in a new server name I get a status message
> saying: SQL server doesn't exist or access denied. I have sa permissions so I
> doubt it would be a permissions issue.
If you right click on the Server Group you wish to add to, then you can
choose the New Server Registration option and type in the name of the new
server rather than browse for it. As the server is not on the list then it
may not have the network protocols enabled. This could be the reason for your
error message. Try connecting to the server using Query Analyser (both
locally and on the server itself) and see if you get this message. You can
check the protocols enabled by using the Server Network Utility on the
server. You may also want to make sure that the appropriate Service Packs
have been applied to this SQL Server instance (SELECT @.@.VERSION).
> Any ideas as to what I might be doing wrong?
> Many thanks for any ideas on this in advance
> Ant
>
John
|||hi
try this:
go to start->programs->Microsoft SQL->client network utility (CNU)
on the CNU create an alias using tcp protocol, and specify the port number
that the sql instance is using (default is 1433)
on the name for the alias use a friendly name, so it's easy to use.
go to the sql enterprise manager, right click on the server group where you
want to add the sql instance, and use the alias friendly name you used on the
CNU.
and that's it.
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
Creating a new server in an existing server group
(SQL 2000)
Hi,
I'm trying to create a new server in an existing server group, however there
doesn't seem to be an option to do this. The closest I can see is "New SQL
Server registration" but when I try to use this I need to pick from a list of
existing servers. If I type in a new server name I get a status message
saying: SQL server doesn't exist or access denied. I have sa permissions so I
doubt it would be a permissions issue.
Any ideas as to what I might be doing wrong?
Many thanks for any ideas on this in advance
AntHi
"Ant" wrote:
> (SQL 2000)
> Hi,
> I'm trying to create a new server in an existing server group, however there
> doesn't seem to be an option to do this. The closest I can see is "New SQL
> Server registration" but when I try to use this I need to pick from a list of
> existing servers. If I type in a new server name I get a status message
> saying: SQL server doesn't exist or access denied. I have sa permissions so I
> doubt it would be a permissions issue.
If you right click on the Server Group you wish to add to, then you can
choose the New Server Registration option and type in the name of the new
server rather than browse for it. As the server is not on the list then it
may not have the network protocols enabled. This could be the reason for your
error message. Try connecting to the server using Query Analyser (both
locally and on the server itself) and see if you get this message. You can
check the protocols enabled by using the Server Network Utility on the
server. You may also want to make sure that the appropriate Service Packs
have been applied to this SQL Server instance (SELECT @.@.VERSION).
> Any ideas as to what I might be doing wrong?
> Many thanks for any ideas on this in advance
> Ant
>
John|||hi
try this:
go to start->programs->Microsoft SQL->client network utility (CNU)
on the CNU create an alias using tcp protocol, and specify the port number
that the sql instance is using (default is 1433)
on the name for the alias use a friendly name, so it's easy to use.
go to the sql enterprise manager, right click on the server group where you
want to add the sql instance, and use the alias friendly name you used on the
CNU.
and that's it.
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
Hi,
I'm trying to create a new server in an existing server group, however there
doesn't seem to be an option to do this. The closest I can see is "New SQL
Server registration" but when I try to use this I need to pick from a list of
existing servers. If I type in a new server name I get a status message
saying: SQL server doesn't exist or access denied. I have sa permissions so I
doubt it would be a permissions issue.
Any ideas as to what I might be doing wrong?
Many thanks for any ideas on this in advance
AntHi
"Ant" wrote:
> (SQL 2000)
> Hi,
> I'm trying to create a new server in an existing server group, however there
> doesn't seem to be an option to do this. The closest I can see is "New SQL
> Server registration" but when I try to use this I need to pick from a list of
> existing servers. If I type in a new server name I get a status message
> saying: SQL server doesn't exist or access denied. I have sa permissions so I
> doubt it would be a permissions issue.
If you right click on the Server Group you wish to add to, then you can
choose the New Server Registration option and type in the name of the new
server rather than browse for it. As the server is not on the list then it
may not have the network protocols enabled. This could be the reason for your
error message. Try connecting to the server using Query Analyser (both
locally and on the server itself) and see if you get this message. You can
check the protocols enabled by using the Server Network Utility on the
server. You may also want to make sure that the appropriate Service Packs
have been applied to this SQL Server instance (SELECT @.@.VERSION).
> Any ideas as to what I might be doing wrong?
> Many thanks for any ideas on this in advance
> Ant
>
John|||hi
try this:
go to start->programs->Microsoft SQL->client network utility (CNU)
on the CNU create an alias using tcp protocol, and specify the port number
that the sql instance is using (default is 1433)
on the name for the alias use a friendly name, so it's easy to use.
go to the sql enterprise manager, right click on the server group where you
want to add the sql instance, and use the alias friendly name you used on the
CNU.
and that's it.
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
Creating a New Measure Under Existing Measure Group?
Hello,
When working with SSAS cubes, is there a way to add a new Measure to to the existing Measure Group without creating a new Measure Group? For instance, I have a Measure Group called Account with one measure in it but when I try to add a new measure to Account but it gets added to a new Measure Group called Account 1.
Thanks
You are probably trying to add new measure based on a column that is coming from another table in relational database.
Analysis Services tools dont consider this situation a good practice and suggest you create a new measure group for data coming from another table.
If you want the measure to appear in the same measure group, you can replace your original table with named query in DSV where you join 2 tables together. And then you should be able to add new measure to you measure group.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks Edward.
I am new to Analysis Services and I have been struggling with the following issue for the last two days. Here is the deal: I have two dimensions such as Gender and Semester and one measure called # Of Students. What I would like to have differences between # Of Students for 06FS and 05FS semesters. I used a [Calculated Member] measure but without much success:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Calculated Member]
AS [Measures].[Distinct # Of Students]-[Measures].[Distinct # Of Students],
VISIBLE = 1 ;
The desired column is in blue
Semester
05FS
06FS
Gender
# Of Students
Difference
Females
6000
6200
200
Males
6800
690
100
Could you give me a hand with this?
Thanks for your help!
|||Hi I have the same problem despite my measures are coming from the same table. I have created a Measure with the Sum aggregation on one column and another one with the DistinctCount Aggregation on a second colum of the same table and SSAS creates a different Measure Group for the second measure. I don't understand why. I have exactly the same structure for another table and two measures are stored under the same Measure Group.
Any idea why I can't have my two measures in the same Measure Group ?
|||This is Stupid, when I create a new Measure with an aggregation on a column from the same table than another measure it automatically creates a new measure group for this measure. But If I create a new Measure Group it automatically creates different measures depending of the types of the column from the selected table. I have just replaced the properties I needed from the different measures within the measure group. That's the only way I've found to have multiple measures in an existing measure group.
Creating a New Measure Under Existing Measure Group?
Hello,
When working with SSAS cubes, is there a way to add a new Measure to to the existing Measure Group without creating a new Measure Group? For instance, I have a Measure Group called Account with one measure in it but when I try to add a new measure to Account but it gets added to a new Measure Group called Account 1.
Thanks
You are probably trying to add new measure based on a column that is coming from another table in relational database.
Analysis Services tools dont consider this situation a good practice and suggest you create a new measure group for data coming from another table.
If you want the measure to appear in the same measure group, you can replace your original table with named query in DSV where you join 2 tables together. And then you should be able to add new measure to you measure group.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks Edward.
I am new to Analysis Services and I have been struggling with the following issue for the last two days. Here is the deal: I have two dimensions such as Gender and Semester and one measure called # Of Students. What I would like to have differences between # Of Students for 06FS and 05FS semesters. I used a [Calculated Member] measure but without much success:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Calculated Member]
AS [Measures].[Distinct # Of Students]-[Measures].[Distinct # Of Students],
VISIBLE = 1 ;
The desired column is in blue
Semester
05FS
06FS
Gender
# Of Students
Difference
Females
6000
6200
200
Males
6800
690
100
Could you give me a hand with this?
Thanks for your help!
|||Hi I have the same problem despite my measures are coming from the same table. I have created a Measure with the Sum aggregation on one column and another one with the DistinctCount Aggregation on a second colum of the same table and SSAS creates a different Measure Group for the second measure. I don't understand why. I have exactly the same structure for another table and two measures are stored under the same Measure Group.
Any idea why I can't have my two measures in the same Measure Group ?
|||This is Stupid, when I create a new Measure with an aggregation on a column from the same table than another measure it automatically creates a new measure group for this measure. But If I create a new Measure Group it automatically creates different measures depending of the types of the column from the selected table. I have just replaced the properties I needed from the different measures within the measure group. That's the only way I've found to have multiple measures in an existing measure group.
Wednesday, March 7, 2012
creating a FK on existing tables
hello
I am working with an existing database and there is no Foreign key between 2 tables
how can i create a FK after , when the tables are allready full ?
product :
product_id
report_id
name
report :
report_id
dateR
i want to create a FK on product.report_id, and ON DELETE CASCADE
thank you--Creating table with same structure (Primary key)]
CREATE TABLE [A] (
[report_id] [varchar] (10) ,
[dateR] [Datetime],
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[report_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
--Inserting data from report table
INSERT INTO A
SELECT * FROM report
--Dropping table report
DROP TABLE report
GO
--Renaming A table as report table
EXEC sp_rename 'A','report'
GO
--Caution: Changing any part of an object name
--could break scripts and stored procedures.
--This will create a FK in product table
ALTER TABLE products WITH NOCHECK
ADD CONSTRAINT exd_check FOREIGN KEY
(
[report_id]
) REFERENCES [report] (
[report_id]
) ON DELETE CASCADE|||genial !
thanks a lot
I am working with an existing database and there is no Foreign key between 2 tables
how can i create a FK after , when the tables are allready full ?
product :
product_id
report_id
name
report :
report_id
dateR
i want to create a FK on product.report_id, and ON DELETE CASCADE
thank you--Creating table with same structure (Primary key)]
CREATE TABLE [A] (
[report_id] [varchar] (10) ,
[dateR] [Datetime],
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[report_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
--Inserting data from report table
INSERT INTO A
SELECT * FROM report
--Dropping table report
DROP TABLE report
GO
--Renaming A table as report table
EXEC sp_rename 'A','report'
GO
--Caution: Changing any part of an object name
--could break scripts and stored procedures.
--This will create a FK in product table
ALTER TABLE products WITH NOCHECK
ADD CONSTRAINT exd_check FOREIGN KEY
(
[report_id]
) REFERENCES [report] (
[report_id]
) ON DELETE CASCADE|||genial !
thanks a lot
Creating a diagram with existing tables
I'd like to create a diagram for my existing tables without wiping them out
in the process. I rightclick & select New Diagram, place the various
tables, create relationships, then save. I get a dialog box asking if I
want to create these tables. No! They have data in them. Unfortunately I
don't see a way to save the relationships and the diagram without blowing
everything away.
Jeremy
The diagram utility operates directly on the underlying tables so if you
only want to add the relationships for documentation or viewing purposes and
not have them permanently saved to the database, then you will need to use
another tool.
--Brian
(Please reply to the newsgroups only.)
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>
|||Brian, thanks. The diagram tool is asking about saving tables, not the
relationships. I can understanding the need to save the relationship, but
don't see why it wants to save my already-existing tables and potentially
blow away the data.
Jeremy
"Brian Lawton" <brian.k.lawton@.redtailcreek.com> wrote in message
news:OW%23thLEnFHA.420@.TK2MSFTNGP09.phx.gbl...
> The diagram utility operates directly on the underlying tables so if you
> only want to add the relationships for documentation or viewing purposes
> and not have them permanently saved to the database, then you will need to
> use another tool.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "JeremyGrand" <jeremy@.ninprodata.com> wrote in message
> news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
>
|||I agree that the messaging in the dialog could certainly be improved however
no matter what changes you make, it will always say that it is saving the
tables. Unless you changed the table structure itself, it should only apply
the relationship constraints via ALTER TABLE syntax and not recreate your
tables. If you want to see exactly how your changes are going to be
applied, there is an option to "Save Change Script" which will generate a
SQL script with the code it plans to apply.
--Brian
(Please reply to the newsgroups only.)
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:uUygVYEnFHA.4064@.TK2MSFTNGP10.phx.gbl...
> Brian, thanks. The diagram tool is asking about saving tables, not the
> relationships. I can understanding the need to save the relationship, but
> don't see why it wants to save my already-existing tables and potentially
> blow away the data.
> Jeremy
> "Brian Lawton" <brian.k.lawton@.redtailcreek.com> wrote in message
> news:OW%23thLEnFHA.420@.TK2MSFTNGP09.phx.gbl...
>
|||By the way, DataAnalyst saves relationship discovered in its own access
file, so it won't touch your original table at all.
Download it at http://www.agileinfollc.com
Eric
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>
|||Hi Jeremy
You might want to look at this product
http://www.ag-software.com/?tabid=17
It is a diagram tool and database compare tool. All details are saved
outside of SQL Server
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>
in the process. I rightclick & select New Diagram, place the various
tables, create relationships, then save. I get a dialog box asking if I
want to create these tables. No! They have data in them. Unfortunately I
don't see a way to save the relationships and the diagram without blowing
everything away.
Jeremy
The diagram utility operates directly on the underlying tables so if you
only want to add the relationships for documentation or viewing purposes and
not have them permanently saved to the database, then you will need to use
another tool.
--Brian
(Please reply to the newsgroups only.)
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>
|||Brian, thanks. The diagram tool is asking about saving tables, not the
relationships. I can understanding the need to save the relationship, but
don't see why it wants to save my already-existing tables and potentially
blow away the data.
Jeremy
"Brian Lawton" <brian.k.lawton@.redtailcreek.com> wrote in message
news:OW%23thLEnFHA.420@.TK2MSFTNGP09.phx.gbl...
> The diagram utility operates directly on the underlying tables so if you
> only want to add the relationships for documentation or viewing purposes
> and not have them permanently saved to the database, then you will need to
> use another tool.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "JeremyGrand" <jeremy@.ninprodata.com> wrote in message
> news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
>
|||I agree that the messaging in the dialog could certainly be improved however
no matter what changes you make, it will always say that it is saving the
tables. Unless you changed the table structure itself, it should only apply
the relationship constraints via ALTER TABLE syntax and not recreate your
tables. If you want to see exactly how your changes are going to be
applied, there is an option to "Save Change Script" which will generate a
SQL script with the code it plans to apply.
--Brian
(Please reply to the newsgroups only.)
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:uUygVYEnFHA.4064@.TK2MSFTNGP10.phx.gbl...
> Brian, thanks. The diagram tool is asking about saving tables, not the
> relationships. I can understanding the need to save the relationship, but
> don't see why it wants to save my already-existing tables and potentially
> blow away the data.
> Jeremy
> "Brian Lawton" <brian.k.lawton@.redtailcreek.com> wrote in message
> news:OW%23thLEnFHA.420@.TK2MSFTNGP09.phx.gbl...
>
|||By the way, DataAnalyst saves relationship discovered in its own access
file, so it won't touch your original table at all.
Download it at http://www.agileinfollc.com
Eric
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>
|||Hi Jeremy
You might want to look at this product
http://www.ag-software.com/?tabid=17
It is a diagram tool and database compare tool. All details are saved
outside of SQL Server
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>
Friday, February 24, 2012
creating a clustered index - after the fact
Hello,
I have a few tables that I need to add clustered indexes to. However,
most of the table already have existing non-clustered indexes on them.
I understand that adding a clustered index after the other indexes is
not a good idea - but is this mostly a index creation performance issue
(in that the other indexes need to get rebuilt)? Or is there more to
the issue than this?
I can do the create after hours, so performance is not an issue. But I
am wondering should I just drop all the indexes on the tables, and
recreate them from scratch, in proper order?
Thanks
tootsuite,
It is a performance issue. For each table follow these steps in order:
1. drop all of the non-clustered indexes
2. drop the clustered index
3. create the new clustered index
4. create the new non-clustered indexes
If you drop the clustered index before the non-clustered indexes, the
non-clustered indexes are automatically re-indexed. When the new clustered
index is added the non-clustered indexes are reindexed again.
-- Bill
<tootsuite@.gmail.com> wrote in message
news:1169055466.451768.125760@.s34g2000cwa.googlegr oups.com...
> Hello,
> I have a few tables that I need to add clustered indexes to. However,
> most of the table already have existing non-clustered indexes on them.
> I understand that adding a clustered index after the other indexes is
> not a good idea - but is this mostly a index creation performance issue
> (in that the other indexes need to get rebuilt)? Or is there more to
> the issue than this?
> I can do the create after hours, so performance is not an issue. But I
> am wondering should I just drop all the indexes on the tables, and
> recreate them from scratch, in proper order?
> Thanks
>
|||thanks for the info
AlterEgo wrote:[vbcol=seagreen]
> tootsuite,
> It is a performance issue. For each table follow these steps in order:
> 1. drop all of the non-clustered indexes
> 2. drop the clustered index
> 3. create the new clustered index
> 4. create the new non-clustered indexes
> If you drop the clustered index before the non-clustered indexes, the
> non-clustered indexes are automatically re-indexed. When the new clustered
> index is added the non-clustered indexes are reindexed again.
> -- Bill
> <tootsuite@.gmail.com> wrote in message
> news:1169055466.451768.125760@.s34g2000cwa.googlegr oups.com...
I have a few tables that I need to add clustered indexes to. However,
most of the table already have existing non-clustered indexes on them.
I understand that adding a clustered index after the other indexes is
not a good idea - but is this mostly a index creation performance issue
(in that the other indexes need to get rebuilt)? Or is there more to
the issue than this?
I can do the create after hours, so performance is not an issue. But I
am wondering should I just drop all the indexes on the tables, and
recreate them from scratch, in proper order?
Thanks
tootsuite,
It is a performance issue. For each table follow these steps in order:
1. drop all of the non-clustered indexes
2. drop the clustered index
3. create the new clustered index
4. create the new non-clustered indexes
If you drop the clustered index before the non-clustered indexes, the
non-clustered indexes are automatically re-indexed. When the new clustered
index is added the non-clustered indexes are reindexed again.
-- Bill
<tootsuite@.gmail.com> wrote in message
news:1169055466.451768.125760@.s34g2000cwa.googlegr oups.com...
> Hello,
> I have a few tables that I need to add clustered indexes to. However,
> most of the table already have existing non-clustered indexes on them.
> I understand that adding a clustered index after the other indexes is
> not a good idea - but is this mostly a index creation performance issue
> (in that the other indexes need to get rebuilt)? Or is there more to
> the issue than this?
> I can do the create after hours, so performance is not an issue. But I
> am wondering should I just drop all the indexes on the tables, and
> recreate them from scratch, in proper order?
> Thanks
>
|||thanks for the info
AlterEgo wrote:[vbcol=seagreen]
> tootsuite,
> It is a performance issue. For each table follow these steps in order:
> 1. drop all of the non-clustered indexes
> 2. drop the clustered index
> 3. create the new clustered index
> 4. create the new non-clustered indexes
> If you drop the clustered index before the non-clustered indexes, the
> non-clustered indexes are automatically re-indexed. When the new clustered
> index is added the non-clustered indexes are reindexed again.
> -- Bill
> <tootsuite@.gmail.com> wrote in message
> news:1169055466.451768.125760@.s34g2000cwa.googlegr oups.com...
creating a clustered index - after the fact
Hello,
I have a few tables that I need to add clustered indexes to. However,
most of the table already have existing non-clustered indexes on them.
I understand that adding a clustered index after the other indexes is
not a good idea - but is this mostly a index creation performance issue
(in that the other indexes need to get rebuilt)? Or is there more to
the issue than this?
I can do the create after hours, so performance is not an issue. But I
am wondering should I just drop all the indexes on the tables, and
recreate them from scratch, in proper order?
Thankstootsuite,
It is a performance issue. For each table follow these steps in order:
1. drop all of the non-clustered indexes
2. drop the clustered index
3. create the new clustered index
4. create the new non-clustered indexes
If you drop the clustered index before the non-clustered indexes, the
non-clustered indexes are automatically re-indexed. When the new clustered
index is added the non-clustered indexes are reindexed again.
-- Bill
<tootsuite@.gmail.com> wrote in message
news:1169055466.451768.125760@.s34g2000cwa.googlegroups.com...
> Hello,
> I have a few tables that I need to add clustered indexes to. However,
> most of the table already have existing non-clustered indexes on them.
> I understand that adding a clustered index after the other indexes is
> not a good idea - but is this mostly a index creation performance issue
> (in that the other indexes need to get rebuilt)? Or is there more to
> the issue than this?
> I can do the create after hours, so performance is not an issue. But I
> am wondering should I just drop all the indexes on the tables, and
> recreate them from scratch, in proper order?
> Thanks
>|||thanks for the info
AlterEgo wrote:[vbcol=seagreen]
> tootsuite,
> It is a performance issue. For each table follow these steps in order:
> 1. drop all of the non-clustered indexes
> 2. drop the clustered index
> 3. create the new clustered index
> 4. create the new non-clustered indexes
> If you drop the clustered index before the non-clustered indexes, the
> non-clustered indexes are automatically re-indexed. When the new clustered
> index is added the non-clustered indexes are reindexed again.
> -- Bill
> <tootsuite@.gmail.com> wrote in message
> news:1169055466.451768.125760@.s34g2000cwa.googlegroups.com...
I have a few tables that I need to add clustered indexes to. However,
most of the table already have existing non-clustered indexes on them.
I understand that adding a clustered index after the other indexes is
not a good idea - but is this mostly a index creation performance issue
(in that the other indexes need to get rebuilt)? Or is there more to
the issue than this?
I can do the create after hours, so performance is not an issue. But I
am wondering should I just drop all the indexes on the tables, and
recreate them from scratch, in proper order?
Thankstootsuite,
It is a performance issue. For each table follow these steps in order:
1. drop all of the non-clustered indexes
2. drop the clustered index
3. create the new clustered index
4. create the new non-clustered indexes
If you drop the clustered index before the non-clustered indexes, the
non-clustered indexes are automatically re-indexed. When the new clustered
index is added the non-clustered indexes are reindexed again.
-- Bill
<tootsuite@.gmail.com> wrote in message
news:1169055466.451768.125760@.s34g2000cwa.googlegroups.com...
> Hello,
> I have a few tables that I need to add clustered indexes to. However,
> most of the table already have existing non-clustered indexes on them.
> I understand that adding a clustered index after the other indexes is
> not a good idea - but is this mostly a index creation performance issue
> (in that the other indexes need to get rebuilt)? Or is there more to
> the issue than this?
> I can do the create after hours, so performance is not an issue. But I
> am wondering should I just drop all the indexes on the tables, and
> recreate them from scratch, in proper order?
> Thanks
>|||thanks for the info
AlterEgo wrote:[vbcol=seagreen]
> tootsuite,
> It is a performance issue. For each table follow these steps in order:
> 1. drop all of the non-clustered indexes
> 2. drop the clustered index
> 3. create the new clustered index
> 4. create the new non-clustered indexes
> If you drop the clustered index before the non-clustered indexes, the
> non-clustered indexes are automatically re-indexed. When the new clustered
> index is added the non-clustered indexes are reindexed again.
> -- Bill
> <tootsuite@.gmail.com> wrote in message
> news:1169055466.451768.125760@.s34g2000cwa.googlegroups.com...
creating a clustered index - after the fact
Hello,
I have a few tables that I need to add clustered indexes to. However,
most of the table already have existing non-clustered indexes on them.
I understand that adding a clustered index after the other indexes is
not a good idea - but is this mostly a index creation performance issue
(in that the other indexes need to get rebuilt)? Or is there more to
the issue than this?
I can do the create after hours, so performance is not an issue. But I
am wondering should I just drop all the indexes on the tables, and
recreate them from scratch, in proper order?
Thankstootsuite,
It is a performance issue. For each table follow these steps in order:
1. drop all of the non-clustered indexes
2. drop the clustered index
3. create the new clustered index
4. create the new non-clustered indexes
If you drop the clustered index before the non-clustered indexes, the
non-clustered indexes are automatically re-indexed. When the new clustered
index is added the non-clustered indexes are reindexed again.
-- Bill
<tootsuite@.gmail.com> wrote in message
news:1169055466.451768.125760@.s34g2000cwa.googlegroups.com...
> Hello,
> I have a few tables that I need to add clustered indexes to. However,
> most of the table already have existing non-clustered indexes on them.
> I understand that adding a clustered index after the other indexes is
> not a good idea - but is this mostly a index creation performance issue
> (in that the other indexes need to get rebuilt)? Or is there more to
> the issue than this?
> I can do the create after hours, so performance is not an issue. But I
> am wondering should I just drop all the indexes on the tables, and
> recreate them from scratch, in proper order?
> Thanks
>|||thanks for the info
AlterEgo wrote:
> tootsuite,
> It is a performance issue. For each table follow these steps in order:
> 1. drop all of the non-clustered indexes
> 2. drop the clustered index
> 3. create the new clustered index
> 4. create the new non-clustered indexes
> If you drop the clustered index before the non-clustered indexes, the
> non-clustered indexes are automatically re-indexed. When the new clustered
> index is added the non-clustered indexes are reindexed again.
> -- Bill
> <tootsuite@.gmail.com> wrote in message
> news:1169055466.451768.125760@.s34g2000cwa.googlegroups.com...
> > Hello,
> >
> > I have a few tables that I need to add clustered indexes to. However,
> > most of the table already have existing non-clustered indexes on them.
> > I understand that adding a clustered index after the other indexes is
> > not a good idea - but is this mostly a index creation performance issue
> > (in that the other indexes need to get rebuilt)? Or is there more to
> > the issue than this?
> >
> > I can do the create after hours, so performance is not an issue. But I
> > am wondering should I just drop all the indexes on the tables, and
> > recreate them from scratch, in proper order?
> >
> > Thanks
> >
I have a few tables that I need to add clustered indexes to. However,
most of the table already have existing non-clustered indexes on them.
I understand that adding a clustered index after the other indexes is
not a good idea - but is this mostly a index creation performance issue
(in that the other indexes need to get rebuilt)? Or is there more to
the issue than this?
I can do the create after hours, so performance is not an issue. But I
am wondering should I just drop all the indexes on the tables, and
recreate them from scratch, in proper order?
Thankstootsuite,
It is a performance issue. For each table follow these steps in order:
1. drop all of the non-clustered indexes
2. drop the clustered index
3. create the new clustered index
4. create the new non-clustered indexes
If you drop the clustered index before the non-clustered indexes, the
non-clustered indexes are automatically re-indexed. When the new clustered
index is added the non-clustered indexes are reindexed again.
-- Bill
<tootsuite@.gmail.com> wrote in message
news:1169055466.451768.125760@.s34g2000cwa.googlegroups.com...
> Hello,
> I have a few tables that I need to add clustered indexes to. However,
> most of the table already have existing non-clustered indexes on them.
> I understand that adding a clustered index after the other indexes is
> not a good idea - but is this mostly a index creation performance issue
> (in that the other indexes need to get rebuilt)? Or is there more to
> the issue than this?
> I can do the create after hours, so performance is not an issue. But I
> am wondering should I just drop all the indexes on the tables, and
> recreate them from scratch, in proper order?
> Thanks
>|||thanks for the info
AlterEgo wrote:
> tootsuite,
> It is a performance issue. For each table follow these steps in order:
> 1. drop all of the non-clustered indexes
> 2. drop the clustered index
> 3. create the new clustered index
> 4. create the new non-clustered indexes
> If you drop the clustered index before the non-clustered indexes, the
> non-clustered indexes are automatically re-indexed. When the new clustered
> index is added the non-clustered indexes are reindexed again.
> -- Bill
> <tootsuite@.gmail.com> wrote in message
> news:1169055466.451768.125760@.s34g2000cwa.googlegroups.com...
> > Hello,
> >
> > I have a few tables that I need to add clustered indexes to. However,
> > most of the table already have existing non-clustered indexes on them.
> > I understand that adding a clustered index after the other indexes is
> > not a good idea - but is this mostly a index creation performance issue
> > (in that the other indexes need to get rebuilt)? Or is there more to
> > the issue than this?
> >
> > I can do the create after hours, so performance is not an issue. But I
> > am wondering should I just drop all the indexes on the tables, and
> > recreate them from scratch, in proper order?
> >
> > Thanks
> >
Sunday, February 19, 2012
Createing an instance on a Sql Server 2k installation.
I need to know if it is possible to create a Sql Server instance on top of an existing SQL Server 2k installation.
I am trying to mimic a production setup completely and the database is setup as an instance of a SQL server. By that I mean that we connect to the database by specifying <servername>\<instancename>.
I apologize if I have been ambiguous, or have used incorrect terminology. I am not a DBA and am trying to explain this the best way I know of.
Any help that anyone can provide is greatly appreciated!!
Sure, another instance can be easily installed by using the setup disk on choosing the named instance option along with the setup. If you have a default instance installed on the server, be aware that the named instance has another port than the default instance. You will have to specify the port during connection time in the syntax of ServerName\Instancename,Portnumer (whereas the instanceName is irgnored if you specified the portnumber, but for me this is for better reading and debugging).
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Thank you for the reponse!
I will give that a try.
|||This did the trick! Many thanks!Friday, February 17, 2012
Createa table like a existing table?
Hai all
I am new to SLQ server. Can anyone tell me how to create a table that
should look like a existing table fields...
thanxSSG (ssg14j@.gmail.com) writes:
> I am new to SLQ server. Can anyone tell me how to create a table that
> should look like a existing table fields...
You can do "SELECT * INTO newtbl FROM tbl WHERE 1 = 0". Beware though
that constraints, indexes and triggers are not copied. If you want that
you are better off scripting the table, which you can do by right-clicking
the table in the Object Broswer in Query Analyzer. Even better, have all
your source code under version control. Then copying is even easier.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Tue, 27 Dec 2005 08:13:25 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>SSG (ssg14j@.gmail.com) writes:
>> I am new to SLQ server. Can anyone tell me how to create a table that
>> should look like a existing table fields...
>You can do "SELECT * INTO newtbl FROM tbl WHERE 1 = 0". Beware though
>that constraints, indexes and triggers are not copied. If you want that
>you are better off scripting the table, which you can do by right-clicking
>the table in the Object Broswer in Query Analyzer. Even better, have all
>your source code under version control. Then copying is even easier.
Also note that in most cases where you would want to copy a table's design
within the same database, you'd probably be better off doing more
normalization instead.
Subscribe to:
Posts (Atom)