cloning servers with sql server on them

Anyone have any experience with cloning servers that have sql server
installed. I am wondering what will happen when the new virtual server comes
up using a different name. sql server takes it name from the server name.
Will it just automatically adjust or is there a process to deal with that?
Thanks.
If it's SQL Server 2005 and it has never run after installation, it will
"fix" itself upon first startup. Else, it's a simple process to
drop/register the new server name (just like in 2000). If you have other
objects in there name/server dependencies, well, that gets a little more
complicated.
joe.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:68DB416E-E2B6-4E21-996C-2A99B37D6C01@.microsoft.com...
> Anyone have any experience with cloning servers that have sql server
> installed. I am wondering what will happen when the new virtual server
> comes
> up using a different name. sql server takes it name from the server name.
> Will it just automatically adjust or is there a process to deal with that?
> Thanks.
>
|||Hello Jason,
If you rename a server with SQL 2000/2005 installed, you may want to use
sp_dropserver to drop old server name, and then run sp_addserver to add the
new server name.
303774BUG: "Renaming A Server" Topic in SQL Server Books Online is
Incomplete
http://support.microsoft.com/default.aspx?scid=kb;EN-US;303774
317241Renaming a host computer for SQL Server causes stored procedure
debug to fail
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317241
281642PRB: Error 14274 Occurs When You Update a SQL Agent Job After
Renaming Windows Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;281642
Please let me know if you have any comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

cloning servers with sql server on them

Anyone have any experience with cloning servers that have sql server
installed. I am wondering what will happen when the new virtual server come
s
up using a different name. sql server takes it name from the server name.
Will it just automatically adjust or is there a process to deal with that?
Thanks.If it's SQL Server 2005 and it has never run after installation, it will
"fix" itself upon first startup. Else, it's a simple process to
drop/register the new server name (just like in 2000). If you have other
objects in there name/server dependencies, well, that gets a little more
complicated.
joe.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:68DB416E-E2B6-4E21-996C-2A99B37D6C01@.microsoft.com...
> Anyone have any experience with cloning servers that have sql server
> installed. I am wondering what will happen when the new virtual server
> comes
> up using a different name. sql server takes it name from the server name.
> Will it just automatically adjust or is there a process to deal with that?
> Thanks.
>|||Hello Jason,
If you rename a server with SQL 2000/2005 installed, you may want to use
sp_dropserver to drop old server name, and then run sp_addserver to add the
new server name.
303774 BUG: "Renaming A Server" Topic in SQL Server Books Online is
Incomplete
http://support.microsoft.com/defaul...kb;EN-US;303774
317241 Renaming a host computer for SQL Server causes stored procedure
debug to fail
http://support.microsoft.com/defaul...kb;EN-US;317241
281642 PRB: Error 14274 Occurs When You Update a SQL Agent Job After
Renaming Windows Server
http://support.microsoft.com/defaul...kb;EN-US;281642
Please let me know if you have any comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

CLONING PACKAGES

I loved the DTS feature that allowed saving DTS package as a VB model. With a bit of coding you could generate a number of packages from one template.

Are there any analogous solutions for SSIS? I cannot find anything.

My goal is simple. I have an ETL step that transfers data from staging dimension table to the corresponding star schema table in the subject matter database. I have two types of packages for SCD type 1 and type 2. Do you have any suggestions on how I can clone packages so that I don’t have to go manually through each of them to replace certain items such as stored procedure names, etc.

Thank you!

Besides copying the .dtsx file and editing the copy to suit your needs?|||You can create a program to generate SSIS packages. If you want to reverse your current packages into C# code, take a look at http://www.ivolva.com/ssis_code_generator.html.|||You could also create a template for each type of load process and store them in the \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems folder. I think that's the correct place.|||Phil Brammer: Yes, I want to be able to change all hard-coded string dynamically. Assuming I use standard naming convention all object names will have the same structure. All I want to do is loop through table names and replace appropriate strings within the existing template package to generate new packages on the fly.|||jwelch, that's an interesting solution. I'll try to play with you and let you know if it works. But it seems it's what I need.|||

LoveDanger wrote:

Phil Brammer: Yes, I want to be able to change all hard-coded string dynamically. Assuming I use standard naming convention all object names will have the same structure. All I want to do is loop through table names and replace appropriate strings within the existing template package to generate new packages on the fly.

Using expressions, you can build just ONE package to do this though.

Provided the structures of all of the tables (both sources and destinations) are all the same... That is, you can dynamically adjust the tables the OLE DB sources and destinations go against.|||Thanks Phil, this should work. I assume the transformation part would be tricky (I'm not sure how to map all the columns dynamically), but I'll check if that could be done. Also, I believe the solution posted by jwelch (http://www.ivolva.com/ssis_code_generator.html) should work if one still wants to generate numerous packages.

Thank you all!
|||Note: My solution works if you are looping through tables that have the same structure. If they don't, then my solution won't work. Once you build the package (mappings and all) you can start using expressions to dynamically change the table names.|||Yeah, in my case we have different set of attributes for each dimension that I'm not sure how to handle. I used your approach with BCP though - use only one package to transfer data from a number of flat files to SQL Server tables. Though you still have to generate format files for each that pretty much translates your column mappings.|||

LoveDanger wrote:

I loved the DTS feature that allowed saving DTS package as a VB model. With a bit of coding you could generate a number of packages from one template.

Are there any analogous solutions for SSIS? I cannot find anything.

My goal is simple. I have an ETL step that transfers data from staging dimension table to the corresponding star schema table in the subject matter database. I have two types of packages for SCD type 1 and type 2. Do you have any suggestions on how I can clone packages so that I don’t have to go manually through each of them to replace certain items such as stored procedure names, etc.

Thank you!

Sounds to me like you want to use templates. Matt explained where to drop them elsewhere in this thread.

-Jamie

|||

Well, I am not really sure how would templates solve my problem. I will still have to go through each package task and replace the names of the objects manually.

Does template allow you to do search and replace for the entire package?

|||

LoveDanger wrote:

Well, I am not really sure how would templates solve my problem. I will still have to go through each package task and replace the names of the objects manually.

It sounds as though you'll have to do that regardless of the solution though?

LoveDanger wrote:

Does template allow you to do search and replace for the entire package?

No, not really. You could open up the package's XML (right-click on the package and select 'View Code') and do a Find-Replace that way of that's what you want to do.

-Jamie

|||Thanks Jamie, I think this would be the easiest in my case. At least it will save time on going through each task manually.

Anastasia|||

LoveDanger wrote:

Thanks Jamie, I think this would be the easiest in my case. At least it will save time on going through each task manually.

Anastasia

Cool. Take a copy of the package before you alter it

Cloning a table

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

SELECT * INTO MyNewTable FROM MyTable WHERE 1 = 0

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

--
Hope this helps.

Dan Guzman
SQL Server MVP

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

Cloning a SQL 2005 Database

hi,
I have cloned a VM of a SQL Server 2005 database to a new server with a
different name.
I have run the sp_dropserver and sp_addserver commands as well documented to
change the server name within the sql instance to the new cloned server name.
The issue i have is that the logins on the new cloned server still contains
the local group and local account details from the old machine.
I have renamed the groups and accounts within NT, but when i try to change
rename the logins within sql , i get the error.
TITLE: Microsoft SQL Server Management Studio
Unable to rename LBOPS2\SQLServer2005MSSQLUser$LBOPS2$MSSQLSERVER.
(ObjectExplorer)
ADDITIONAL INFORMATION:
Rename failed for Login 'LAOPS2\SQLServer2005MSSQLUser$LAOPS2$MSSQLSERVER' .
(Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Rename+Login&LinkId=20476[/url]
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The name change cannot be performed because the SID of the new name does not
match the old SID of the principal. (Microsoft SQL Server, Error: 15098)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2050&EvtSrc=MSSQLServer&EvtID=15098&LinkId=20476[ /url]
BUTTONS:
OK
Many Thanks
Jon
Hi Jon
"Jon Bird" wrote:

> hi,
> I have cloned a VM of a SQL Server 2005 database to a new server with a
> different name.
> I have run the sp_dropserver and sp_addserver commands as well documented to
> change the server name within the sql instance to the new cloned server name.
> The issue i have is that the logins on the new cloned server still contains
> the local group and local account details from the old machine.
> I have renamed the groups and accounts within NT, but when i try to change
> rename the logins within sql , i get the error.
> TITLE: Microsoft SQL Server Management Studio
> --
> Unable to rename LBOPS2\SQLServer2005MSSQLUser$LBOPS2$MSSQLSERVER.
> (ObjectExplorer)
> --
> ADDITIONAL INFORMATION:
> Rename failed for Login 'LAOPS2\SQLServer2005MSSQLUser$LAOPS2$MSSQLSERVER' .
> (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Rename+Login&LinkId=20476[/url]
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The name change cannot be performed because the SID of the new name does not
> match the old SID of the principal. (Microsoft SQL Server, Error: 15098)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2050&EvtSrc=MSSQLServer&EvtID=15098&LinkId=20476[ /url]
> --
> BUTTONS:
> OK
> --
>
> Many Thanks
> Jon
>
Have you looked at using the script in:
http://support.microsoft.com/kb/246133/
Then dropping the logins and re-creating new ones with the same SIDs.
John

Cloning a SQL 2005 Database

hi,
I have cloned a VM of a SQL Server 2005 database to a new server with a
different name.
I have run the sp_dropserver and sp_addserver commands as well documented to
change the server name within the sql instance to the new cloned server name.
The issue i have is that the logins on the new cloned server still contains
the local group and local account details from the old machine.
I have renamed the groups and accounts within NT, but when i try to change
rename the logins within sql , i get the error.
TITLE: Microsoft SQL Server Management Studio
--
Unable to rename LBOPS2\SQLServer2005MSSQLUser$LBOPS2$MSSQLSERVER.
(ObjectExplorer)
--
ADDITIONAL INFORMATION:
Rename failed for Login 'LAOPS2\SQLServer2005MSSQLUser$LAOPS2$MSSQLSERVER'.
(Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Login&LinkId=20476
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
The name change cannot be performed because the SID of the new name does not
match the old SID of the principal. (Microsoft SQL Server, Error: 15098)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2050&EvtSrc=MSSQLServer&EvtID=15098&LinkId=20476
--
BUTTONS:
OK
--
Many Thanks
JonHi Jon
"Jon Bird" wrote:
> hi,
> I have cloned a VM of a SQL Server 2005 database to a new server with a
> different name.
> I have run the sp_dropserver and sp_addserver commands as well documented to
> change the server name within the sql instance to the new cloned server name.
> The issue i have is that the logins on the new cloned server still contains
> the local group and local account details from the old machine.
> I have renamed the groups and accounts within NT, but when i try to change
> rename the logins within sql , i get the error.
> TITLE: Microsoft SQL Server Management Studio
> --
> Unable to rename LBOPS2\SQLServer2005MSSQLUser$LBOPS2$MSSQLSERVER.
> (ObjectExplorer)
> --
> ADDITIONAL INFORMATION:
> Rename failed for Login 'LAOPS2\SQLServer2005MSSQLUser$LAOPS2$MSSQLSERVER'.
> (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Login&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The name change cannot be performed because the SID of the new name does not
> match the old SID of the principal. (Microsoft SQL Server, Error: 15098)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2050&EvtSrc=MSSQLServer&EvtID=15098&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
> Many Thanks
> Jon
>
Have you looked at using the script in:
http://support.microsoft.com/kb/246133/
Then dropping the logins and re-creating new ones with the same SIDs.
John

Cloning a SQL 2005 Database

hi,
I have cloned a VM of a SQL Server 2005 database to a new server with a
different name.
I have run the sp_dropserver and sp_addserver commands as well documented to
change the server name within the sql instance to the new cloned server name
.
The issue i have is that the logins on the new cloned server still contains
the local group and local account details from the old machine.
I have renamed the groups and accounts within NT, but when i try to change
rename the logins within sql , i get the error.
TITLE: Microsoft SQL Server Management Studio
--
Unable to rename LBOPS2\SQLServer2005MSSQLUser$LBOPS2$MSS
QLSERVER.
(ObjectExplorer)
ADDITIONAL INFORMATION:
Rename failed for Login 'LAOPS2\SQLServer2005MSSQLUser$LAOPS2$MS
SQLSERVER'.
(Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?Prod...in&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The name change cannot be performed because the SID of the new name does not
match the old SID of the principal. (Microsoft SQL Server, Error: 15098)
For help, click:
http://go.microsoft.com/fwlink?Prod...98&LinkId=20476
BUTTONS:
OK
--
Many Thanks
JonHi Jon
"Jon Bird" wrote:

> hi,
> I have cloned a VM of a SQL Server 2005 database to a new server with a
> different name.
> I have run the sp_dropserver and sp_addserver commands as well documented
to
> change the server name within the sql instance to the new cloned server na
me.
> The issue i have is that the logins on the new cloned server still contain
s
> the local group and local account details from the old machine.
> I have renamed the groups and accounts within NT, but when i try to change
> rename the logins within sql , i get the error.
> TITLE: Microsoft SQL Server Management Studio
> --
> Unable to rename LBOPS2\SQLServer2005MSSQLUser$LBOPS2$MSS
QLSERVER.
> (ObjectExplorer)
> --
> ADDITIONAL INFORMATION:
> Rename failed for Login 'LAOPS2\SQLServer2005MSSQLUser$LAOPS2$MS
SQLSERVER'
.
> (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...in&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The name change cannot be performed because the SID of the new name does n
ot
> match the old SID of the principal. (Microsoft SQL Server, Error: 15098)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...98&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
> Many Thanks
> Jon
>
Have you looked at using the script in:
http://support.microsoft.com/kb/246133/
Then dropping the logins and re-creating new ones with the same SIDs.
John

cloning a schema?

Is there a way to clone an entire database, without the data? Thanks for
any tips.Backup / restore + Clean-up the tables through scripts '
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"Neil W." <neilw@.netlib.com> wrote in message
news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||right click on the database in enterprise manager and generate sql script,
from there you can choose your options and get as detailed as you want.
thanks,
nivek
"Neil W." <neilw@.netlib.com> wrote in message
news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||Also, you can right-click on the database in the Query Analyzer and choose
Script Object to New Window As -> Create to generate a script for the schema
.
Changing options in the Query Analyzer will allow you to modify the amount
of detail generated (Tools -> Options -> Script Tab).
"Neil W." wrote:

> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>
>|||http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Neil W." <neilw@.netlib.com> wrote in message news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...

> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||Thanks for the reply. Sorry to be dense, but which tool are you referring
to? I want to clone all aspects of the schema including triggers and
contraints.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Neil W." <neilw@.netlib.com> wrote in message
news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||The article lists several tools, quite simply. Use the one which serves your
purpose and does what
you want it to do. If you want to do this from code, for instance, consider
writing an app that uses
DMO. Etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Neil W." <neilw@.netlib.com> wrote in message news:%23WTtmWAJFHA.2852@.TK2MSFTNGP09.phx.gbl.
.
> Thanks for the reply. Sorry to be dense, but which tool are you referring
> to? I want to clone all aspects of the schema including triggers and
> contraints.
> --
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Neil W." <neilw@.netlib.com> wrote in message
> news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
>
>

cloning a db under another name

I have a database called Paris. I want to clone it but I want the new name
to be Milan. I tried creating a new database named Milan and restoring
Paris over it but Management Studio would not allow this. I had overwrite
checked. How can I do this?
Regards,
Gary Blakely
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have a database called Paris. I want to clone it but I want the new name
>to be Milan. I tried creating a new database named Milan and restoring
>Paris over it but Management Studio would not allow this. I had overwrite
>checked. How can I do this?
Don't use the GUI.
Personally I wouldn't bother creating Milan but would simply backup Paris
and
RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.

> --
> Regards,
> Gary Blakely
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
... and read up on the MOVE option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uJ3iLHElHHA.4552@.TK2MSFTNGP04.phx.gbl...
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Don't use the GUI.
> Personally I wouldn't bother creating Milan but would simply backup Paris and
> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
|||Hello Gary,
I agree Tibor that you may want to use Move option since you may want to
use a different path for the database files. You could refer to the
following article for some related information
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions on this issue. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||On May 12, 9:24 am, "GaryDean" <GaryD...@.newsgroups.nospam> wrote:
> I have a database called Paris. I want to clone it but I want the new name
> to be Milan. I tried creating a new database named Milan and restoring
> Paris over it but Management Studio would not allow this. I had overwrite
> checked. How can I do this?
> --
> Regards,
> Gary Blakely
Hi. I suggest you copy DB, that's quite straight forward, you can
choose any name from it.
|||Well, I don't INSIST on using the gui but...
Thanks, that worked great. Now I see what the badly worded error message
was trying to tell me.
Regards,
Gary Blakely
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:OMc3IRjlHHA.4120@.TK2MSFTNGP06.phx.gbl...
> GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
>
> If you insist on using the GUI to do this, you need to change the
> filenames in the Restore As column on the Options page of the restore
> task. MS is preventing you from restoring using the previous filenames as
> this would overwrite the files for the Paris database.
> Dan
>

cloning a db under another name

I have a database called Paris. I want to clone it but I want the new name
to be Milan. I tried creating a new database named Milan and restoring
Paris over it but Management Studio would not allow this. I had overwrite
checked. How can I do this?
--
Regards,
Gary Blakely"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have a database called Paris. I want to clone it but I want the new name
>to be Milan. I tried creating a new database named Milan and restoring
>Paris over it but Management Studio would not allow this. I had overwrite
>checked. How can I do this?
Don't use the GUI.
Personally I wouldn't bother creating Milan but would simply backup Paris
and
RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
> --
> Regards,
> Gary Blakely
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
... and read up on the MOVE option of the RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uJ3iLHElHHA.4552@.TK2MSFTNGP04.phx.gbl...
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>>I have a database called Paris. I want to clone it but I want the new name to be Milan. I tried
>>creating a new database named Milan and restoring Paris over it but Management Studio would not
>>allow this. I had overwrite checked. How can I do this?
> Don't use the GUI.
> Personally I wouldn't bother creating Milan but would simply backup Paris and
> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
>
>> --
>> Regards,
>> Gary Blakely
>>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>|||Hello Gary,
I agree Tibor that you may want to use Move option since you may want to
use a different path for the database files. You could refer to the
following article for some related information
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions on this issue. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||On May 12, 9:24 am, "GaryDean" <GaryD...@.newsgroups.nospam> wrote:
> I have a database called Paris. I want to clone it but I want the new name
> to be Milan. I tried creating a new database named Milan and restoring
> Paris over it but Management Studio would not allow this. I had overwrite
> checked. How can I do this?
> --
> Regards,
> Gary Blakely
Hi. I suggest you copy DB, that's quite straight forward, you can
choose any name from it.|||GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
> I have a database called Paris. I want to clone it but I want the new
> name to be Milan. I tried creating a new database named Milan and
> restoring Paris over it but Management Studio would not allow this. I had
> overwrite checked. How can I do this?
>
If you insist on using the GUI to do this, you need to change the filenames
in the Restore As column on the Options page of the restore task. MS is
preventing you from restoring using the previous filenames as this would
overwrite the files for the Paris database.
Dan|||Well, I don't INSIST on using the gui but...
Thanks, that worked great. Now I see what the badly worded error message
was trying to tell me.
--
Regards,
Gary Blakely
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:OMc3IRjlHHA.4120@.TK2MSFTNGP06.phx.gbl...
> GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
>> I have a database called Paris. I want to clone it but I want the new
>> name to be Milan. I tried creating a new database named Milan and
>> restoring Paris over it but Management Studio would not allow this. I
>> had
>> overwrite checked. How can I do this?
>
> If you insist on using the GUI to do this, you need to change the
> filenames in the Restore As column on the Options page of the restore
> task. MS is preventing you from restoring using the previous filenames as
> this would overwrite the files for the Paris database.
> Dan
>|||GaryDean wrote on Mon, 14 May 2007 16:11:25 -0700:
> Well, I don't INSIST on using the gui but...
I guess maybe I should have worded it differently. How about "use the GUI as
an alternative"? :)
> Thanks, that worked great. Now I see what the badly worded error message
> was trying to tell me.
Yeah, it's not that great a message really.
Dan

cloning a db under another name

I have a database called Paris. I want to clone it but I want the new name
to be Milan. I tried creating a new database named Milan and restoring
Paris over it but Management Studio would not allow this. I had overwrite
checked. How can I do this?
Regards,
Gary Blakely"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I have a database called Paris. I want to clone it but I want the new name
>to be Milan. I tried creating a new database named Milan and restoring
>Paris over it but Management Studio would not allow this. I had overwrite
>checked. How can I do this?
Don't use the GUI.
Personally I wouldn't bother creating Milan but would simply backup Paris
and
RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.

> --
> Regards,
> Gary Blakely
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
... and read up on the MOVE option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uJ3iLHElHHA.4552@.TK2MSFTNGP04.phx.gbl...
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:ewnkrPDlHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Don't use the GUI.
> Personally I wouldn't bother creating Milan but would simply backup Paris
and
> RESTORE DATABASE MILAN from disk='c:\paris.bak' etc etc.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>|||Hello Gary,
I agree Tibor that you may want to use Move option since you may want to
use a different path for the database files. You could refer to the
following article for some related information
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions on this issue. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||On May 12, 9:24 am, "GaryDean" <GaryD...@.newsgroups.nospam> wrote:
> I have a database called Paris. I want to clone it but I want the new nam
e
> to be Milan. I tried creating a new database named Milan and restoring
> Paris over it but Management Studio would not allow this. I had overwrite
> checked. How can I do this?
> --
> Regards,
> Gary Blakely
Hi. I suggest you copy DB, that's quite straight forward, you can
choose any name from it.|||GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:

> I have a database called Paris. I want to clone it but I want the new
> name to be Milan. I tried creating a new database named Milan and
> restoring Paris over it but Management Studio would not allow this. I had
> overwrite checked. How can I do this?
>
If you insist on using the GUI to do this, you need to change the filenames
in the Restore As column on the Options page of the restore task. MS is
preventing you from restoring using the previous filenames as this would
overwrite the files for the Paris database.
Dan|||Well, I don't INSIST on using the gui but...
Thanks, that worked great. Now I see what the badly worded error message
was trying to tell me.
Regards,
Gary Blakely
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:OMc3IRjlHHA.4120@.TK2MSFTNGP06.phx.gbl...
> GaryDean wrote on Fri, 11 May 2007 18:24:07 -0700:
>
>
> If you insist on using the GUI to do this, you need to change the
> filenames in the Restore As column on the Options page of the restore
> task. MS is preventing you from restoring using the previous filenames as
> this would overwrite the files for the Paris database.
> Dan
>|||GaryDean wrote on Mon, 14 May 2007 16:11:25 -0700:

> Well, I don't INSIST on using the gui but...
I guess maybe I should have worded it differently. How about "use the GUI as
an alternative"?

> Thanks, that worked great. Now I see what the badly worded error message
> was trying to tell me.
Yeah, it's not that great a message really.
Dan

cloning a database

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

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

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

Clone a set of rows from two-related-tables to a the same-two-related-tables

I want to implement a "cloning" feature in my application and this
involves cloning related data two.
Having this structure (simplified but representative of my problem)
CREATE TABLE Enterprise (enteid INTEGER IDENTITY PRIMARY KEY, entename
VARCHAR(30) NOT NULL)
CREATE TABLE Department (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL, enteid INTEGER NOT NULL REFERENCES Enterprise
(enteid))
CREATE TABLE Employee (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL, employeename VARCHAR(30) NOT NULL, deptid INTEGER
NOT NULL REFERENCES Department (deptid))
I want to:
Clone a complete Enterprise, having @.enteid as a parameter (the id of
the enterprise to clone ) :
1 - Copy one enterrpise row. Obtaining a @.new_enteid
2 - Copy all Departaments rows associated to @.enteid (@.deptid's).
Obtaining @.new_deptid's and associating this new rows to @.new_enteid
3 - Copy all Employee rows related to all @.deptid's and associating
this new rows to the correspondig @.new_deptid's
Is this possible to make in one SQL sentence
Best Regards
Fabio Cavassini
http://fabioon.blogspot.com/Fabio Cavassini wrote:
> I want to implement a "cloning" feature in my application and this
> involves cloning related data two.
> Having this structure (simplified but representative of my problem)
> CREATE TABLE Enterprise (enteid INTEGER IDENTITY PRIMARY KEY, entename
> VARCHAR(30) NOT NULL)
> CREATE TABLE Department (deptid INTEGER IDENTITY PRIMARY KEY, deptname
> VARCHAR(30) NOT NULL, enteid INTEGER NOT NULL REFERENCES Enterprise
> (enteid))
> CREATE TABLE Employee (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
> CHAR(10) NOT NULL, employeename VARCHAR(30) NOT NULL, deptid INTEGER
> NOT NULL REFERENCES Department (deptid))
> I want to:
> Clone a complete Enterprise, having @.enteid as a parameter (the id of
> the enterprise to clone ) :
> 1 - Copy one enterrpise row. Obtaining a @.new_enteid
> 2 - Copy all Departaments rows associated to @.enteid (@.deptid's).
> Obtaining @.new_deptid's and associating this new rows to @.new_enteid
> 3 - Copy all Employee rows related to all @.deptid's and associating
> this new rows to the correspondig @.new_deptid's
> Is this possible to make in one SQL sentence
> Best Regards
> Fabio Cavassini
> http://fabioon.blogspot.com/
Why would you ever want to duplicate data in a table? Don't you have
any other keys? Take two enterprises of the same name for example - how
will you tell them apart? Duplicating the data in the Employee table
would violate Second Normal Form.
The right answer is to design a better normalized data model. The wrong
answer is to wreck data integrity even further by using the
SCOPE_IDENTITY() function, which will allow you to fill your tables
with redundant data in exactly the way you have proposed.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The example is only representative of the problem, in fact I extracted
the data model from an old post that you posted ;)
(http://groups.google.com/group/micr...618a7b00b?hl=en)
The real data model is the following:
My enterprise works with Service_Orders, these service orders are
populated with:
Sell_Articles: This is actually what my enterprise sells to it's
customers
and
Buy_Articles: This is what my enterprise buys to it's providers to get
the Sell Articles
This is the real data model:
CREATE TABLE OrderService (osid INTEGER IDENTITY PRIMARY KEY)
CREATE TABLE Sell_Article_Type (satid INTEGER IDENTITY PRIMARY KEY,
satname
VARCHAR(30) NOT NULL)
CREATE TABLE Buy_Article_Type (batid INTEGER IDENTITY PRIMARY KEY,
batname
VARCHAR(30) NOT NULL)
CREATE TABLE Sell_Article (said INTEGER IDENTITY PRIMARY KEY, satid
INTEGER NOT NULL REFERENCES Sell_Article_Type (satid), osid
INTEGER NOT NULL REFERENCES OrderService (osid), amount DECIMAL NOT
NULL)
CREATE TABLE Buy_Article (baid INTEGER IDENTITY PRIMARY KEY, batid
INTEGER NOT NULL REFERENCES Buy_Article_Type (batid),said
INTEGER NOT NULL REFERENCES Sell_Article (said), amount DECIMAL NOT
NULL)
Now, it's very common that most clients always requires same (or very
near) ServiceOrders, so that's why I need to implement a feature to
"clone" ServiceOrders, This clone will be used as a template for the
user to modify it if needed.
What I need is the SQL Statement that allows me to "clone" a service
order including all it's relationships: Sell_Articles and Buy_Articles.
Hope you can help me
Regards
Fabio|||Fabio Cavassini wrote:
> The example is only representative of the problem, in fact I extracted
> the data model from an old post that you posted ;)
> (http://groups.google.com/group/micr...618a7b00b?hl=en)
>
You did not. You changed it by taking out the alternate keys which
makes the model useless and your requirement highly suspect.

> The real data model is the following:
> My enterprise works with Service_Orders, these service orders are
> populated with:
> Sell_Articles: This is actually what my enterprise sells to it's
> customers
> and
> Buy_Articles: This is what my enterprise buys to it's providers to get
> the Sell Articles
> This is the real data model:
>
Again all the keys are missing. IDENTITY should not be the only key of
any table, that's what UNIQUE constraints are for. You need to fix this
first.

> Now, it's very common that most clients always requires same (or very
> near) ServiceOrders, so that's why I need to implement a feature to
> "clone" ServiceOrders, This clone will be used as a template for the
> user to modify it if needed.
>
A database designer is supposed to eliminate redundancy - not design it
in. With proper design the idea of cloning data in a table would be A)
impossible because it would violate integrity constraints, B)
unecessary. If you don't understand database design principles such as
normalization then I recommend you study before you try to implement
this. Alternatively, add the missing candidate keys to your DDL and
post again so we can help you better.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>Again all the keys are missing. IDENTITY should not be the only key of
>any table, that's what UNIQUE constraints are for. You need to fix this
>first.
Can you tell me how would you change the model to insert these keys?
---
CREATE TABLE OrderService (osid INTEGER IDENTITY PRIMARY KEY)
CREATE TABLE Sell_Article_Type (satid INTEGER IDENTITY PRIMARY KEY,
satname
VARCHAR(30) NOT NULL)
CREATE TABLE Buy_Article_Type (batid INTEGER IDENTITY PRIMARY KEY,
batname
VARCHAR(30) NOT NULL)
CREATE TABLE Sell_Article (said INTEGER IDENTITY PRIMARY KEY, satid
INTEGER NOT NULL REFERENCES Sell_Article_Type (satid), osid
INTEGER NOT NULL REFERENCES OrderService (osid), amount DECIMAL NOT
NULL)
CREATE TABLE Buy_Article (baid INTEGER IDENTITY PRIMARY KEY, batid
INTEGER NOT NULL REFERENCES Buy_Article_Type (batid),said
INTEGER NOT NULL REFERENCES Sell_Article (said), amount DECIMAL NOT
NULL)
---

>A database designer is supposed to eliminate redundancy - not design it
>in. With proper design the idea of cloning data in a table would be A)
>impossible because it would violate integrity constraints, B)
>unecessary. If you don't understand database design principles such as
>normalization then I recommend you study before you try to implement
>this. Alternatively, add the missing candidate keys to your DDL and
>post again so we can help you better.
Well, I know normalization principles, but I think you missed some of
my description of the problem or maybe I should never used the world
"clon".
The idea it's not to make exact clones of set of Order Services, the
idea it's to use existing Order Services as "templates" for new ones,
but this will be different, not the same.
The idea is so simple as Word Templates are, when you use a Word
Template, the New Document you create includes the Template Code
(redundancy) and you can modify it at your desire. IMHO sometimes
redundancy it's necessary to keep a system simple
Regards
Fabio|||Fabio Cavassini wrote:
> Can you tell me how would you change the model to insert these keys?
> ---
>
For example:
CREATE TABLE Sell_Article_Type (satid INTEGER IDENTITY PRIMARY KEY,
satname VARCHAR(30) NOT NULL UNIQUE)
I don't know what the purpose of the OrderService table is. Apparently
it contains no real data. I could understand if it was just used to
generate a value used as a unique key in another table but that appears
not to be the case here.
I don't know about Sell_Article or Buy_Article either. Apparently they
represent different amounts for each type. At a guess the logical
candidate key in Sell_Article may be (satid, osid)? Possibly you have
some entities missing. It's very hard to guess the meaning just from a
list of column names.
The IDENTITY is called a *surrogate* key. That means it stands in for
some other candidate key in a foreign key reference. In your logical
model you should always be able to substitute the business key (aka
natural key or logical key) for the surrogate in the same table. To
enforce entity integrity you need to declare the business key as well
for the obvious reason that IDENTITY does not prevent duplicates in a
table - it just enumerates them.
Now do a little exercise with your logical model. Remove the IDENTITY
columns and try to normalize the schema to BCNF. The results may be
enlightening. :-)

> The idea it's not to make exact clones of set of Order Services, the
> idea it's to use existing Order Services as "templates" for new ones,
> but this will be different, not the same.
Even if this was desirable from a logical point of view (it clearly
isn't) it would be very impractical since it would prevent you from
ever declaring any unique keys. The problem is you are trying to
implement GUI functionality in the database. I suggest you persist the
real data when the user has something real to store - not before.
Creating a redundant row and then updating it isn't likely scale well.

> IMHO sometimes
> redundancy it's necessary to keep a system simple
If you seriously think that redundancy simplifies databases then just
trawl the archives of this group for the thousands of posts from those
desperately trying to eliminate redundancy from bad data models. Better
read some books on relational design too. I recommend Date's "Database
in Depth" BTW.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||To answer the question in your original post, if you look at my replies
in the link you posted you'll see two solutions for inserting the rows
to the referencing table. The solution with the alternate keys uses
single set-based INSERT statements. The solution without the keys
requires a WHILE loop, several statements and row-at-a-time INSERTs.
I'll leave you to decide which is "simpler". I know which I prefer...
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--