Copy Database wizard issue

Despite the fact that both the package and the model db have the file locations set to e:\data\nnn, the SSIS package is creating / copying the files into C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

This is running as a job - could it be a security issue? I don't see any warning / error messages that would indicate an issue.Update...

This is sounding more and more like a bug.

I changed the model db file locations to the c:\ drive (diff location but known access).

no change

I changed the destination file location in the wizard-created SSIS package to the c:\ drive.

no change

No matter what I do, the wizard insists on putting the files into the program files directory listed above.

Both DB's live on the same physical server but different instances. I've combed through the log file and see no errors or warnings of any sort. The account the package is using (again according to the log) is an admin-level account.

I don't see anything on the feedback / bug site related to this.|||Given the deafening silence on this post, I've submitted a bug report. Feedback # is 236131|||

You were right to submit this as a defect. For others reading this thread, the link to the bug is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=236131

We will investigate the issue and get back to you through the Connect site.

Regards,

Copy Database wizard issue

Despite the fact that both the package and the model db have the file locations set to e:\data\nnn, the SSIS package is creating / copying the files into C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

This is running as a job - could it be a security issue? I don't see any warning / error messages that would indicate an issue.Update...

This is sounding more and more like a bug.

I changed the model db file locations to the c:\ drive (diff location but known access).

no change

I changed the destination file location in the wizard-created SSIS package to the c:\ drive.

no change

No matter what I do, the wizard insists on putting the files into the program files directory listed above.

Both DB's live on the same physical server but different instances. I've combed through the log file and see no errors or warnings of any sort. The account the package is using (again according to the log) is an admin-level account.

I don't see anything on the feedback / bug site related to this.|||Given the deafening silence on this post, I've submitted a bug report. Feedback # is 236131|||

You were right to submit this as a defect. For others reading this thread, the link to the bug is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=236131

We will investigate the issue and get back to you through the Connect site.

Regards,

Copy a database with copy the .mdf file and attaching it with a new name?

Hello,

if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.

Or is the datebase name part of the .mdf file?

Regards
Markus

Hi,

I had no problems doing just that and the documentation only mentions to detach the database before taking the database file (or files) to copy them for later attaching. So I guess you should not just stop sql server and copy the files, but detach db, copy db files, reattach db.

--
SvenC

|||

hi Markus,

Markus Sch. wrote:

Hello,

if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.

please be carefull not to overwrite existing database files... but the "scenario" is viable...

Or is the datebase name part of the .mdf file?

Regards
Markus

only User Instances use the Database Name ( and path) to dynamically build database names..

regards

|||

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

There is a much easiere way that I finally figured out:

- right click on the database you want to copy

- select "Tasks" and subsequently select "Backup" and do a backup.

- right click on "Databases"

- select "Restore database"

- Enter the name of a new database (your copy target) and select the database to be copied as source database

- click "OK" (SQL Server creates a new database from the backup which means that you actually copied the original database)

If you only wanted the database design and not the content, well ... Delete from <tablename> ...

|||

hi,

rf wrote:

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

I said, to be carefull ..

There is a much easiere way that I finally figured out:

- right click on the database you want to copy

- select "Tasks" and subsequently select "Backup" and do a backup.

- right click on "Databases"

- select "Restore database"

- Enter the name of a new database (your copy target) and select the database to be copied as source database

- click "OK" (SQL Server creates a new database from the backup which means that you actually copied the original database)

If you only wanted the database design and not the content, well ... Delete from <tablename> ...

yes, this works as well, but, again, you have to be carefull with the actual physical database file as well.. you have to "move" them according to your needs not overwriting existing database files...

regards

|||

rf571786 wrote:

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

Hello rf,

if you detach a database with Management Studio Express, copy the .mdf and .ldf file, and then try to attach the copied files with Databases - Attach, then in the Attach Dialog the Attach As and Current File Path textboxes still have the old name.

The values are not changed to the new .mdf filename.

You have to type in the new database name in the Attach As textbox and the new file names in the Current File Path textbox. Then it works fine.

Or use a Sql Script. The InfoItems.mdf and Infotems_log.ldf files where copied to InfoItems_Test.mdf and Infotems_Test_Log.ldf and then attached with this Sql Script.

But the logical filename in Database Properties - Files - LogicalName still is InfoItems? It should be InfoItems_Test because the Name Clause was specified in the Sql statement?

Regards Markus

USE [master]
GO
CREATE DATABASE [InfoItems_Test] ON
(NAME = InfoItems_Test,
FILENAME = N'E:\InfoItems_Dev\Development\....\Data\DB\InfoItems_Test.mdf' ),
(NAME = InfoItems_Test_Log,
FILENAME = N'E:\InfoItems_Dev\Development\....\Data\DB\InfoItems_Test_log.ldf' )
FOR ATTACH
GO
if not exists (select name from master.sys.databases sd where name = N'InfoItems_Test'
and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [InfoItems_Test].dbo.sp_changedbowner @.loginame=N'....\....', @.map=false
GO

Copy a database with copy the .mdf file and attaching it with a new name?

Hello,

if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.

Or is the datebase name part of the .mdf file?

Regards
Markus

Hi,

I had no problems doing just that and the documentation only mentions to detach the database before taking the database file (or files) to copy them for later attaching. So I guess you should not just stop sql server and copy the files, but detach db, copy db files, reattach db.

--
SvenC

|||

hi Markus,

Markus Sch. wrote:

Hello,

if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.

please be carefull not to overwrite existing database files... but the "scenario" is viable...

Or is the datebase name part of the .mdf file?

Regards
Markus

only User Instances use the Database Name ( and path) to dynamically build database names..

regards

|||

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

There is a much easiere way that I finally figured out:

- right click on the database you want to copy

- select "Tasks" and subsequently select "Backup" and do a backup.

- right click on "Databases"

- select "Restore database"

- Enter the name of a new database (your copy target) and select the database to be copied as source database

- click "OK" (SQL Server creates a new database from the backup which means that you actually copied the original database)

If you only wanted the database design and not the content, well ... Delete from <tablename> ...

|||

hi,

rf wrote:

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

I said, to be carefull ..

There is a much easiere way that I finally figured out:

- right click on the database you want to copy

- select "Tasks" and subsequently select "Backup" and do a backup.

- right click on "Databases"

- select "Restore database"

- Enter the name of a new database (your copy target) and select the database to be copied as source database

- click "OK" (SQL Server creates a new database from the backup which means that you actually copied the original database)

If you only wanted the database design and not the content, well ... Delete from <tablename> ...

yes, this works as well, but, again, you have to be carefull with the actual physical database file as well.. you have to "move" them according to your needs not overwriting existing database files...

regards

|||

rf571786 wrote:

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

Hello rf,

if you detach a database with Management Studio Express, copy the .mdf and .ldf file, and then try to attach the copied files with Databases - Attach, then in the Attach Dialog the Attach As and Current File Path textboxes still have the old name.

The values are not changed to the new .mdf filename.

You have to type in the new database name in the Attach As textbox and the new file names in the Current File Path textbox. Then it works fine.

Or use a Sql Script. The InfoItems.mdf and Infotems_log.ldf files where copied to InfoItems_Test.mdf and Infotems_Test_Log.ldf and then attached with this Sql Script.

But the logical filename in Database Properties - Files - LogicalName still is InfoItems? It should be InfoItems_Test because the Name Clause was specified in the Sql statement?

Regards Markus

USE [master]
GO
CREATE DATABASE [InfoItems_Test] ON
(NAME = InfoItems_Test,
FILENAME = N'E:\InfoItems_Dev\Development\....\Data\DB\InfoItems_Test.mdf' ),
(NAME = InfoItems_Test_Log,
FILENAME = N'E:\InfoItems_Dev\Development\....\Data\DB\InfoItems_Test_log.ldf' )
FOR ATTACH
GO
if not exists (select name from master.sys.databases sd where name = N'InfoItems_Test'
and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [InfoItems_Test].dbo.sp_changedbowner @.loginame=N'....\....', @.map=false
GO

Copy a database with copy the .mdf file and attaching it with a new name?

Hello,

if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.

Or is the datebase name part of the .mdf file?

Regards
Markus

Hi,

I had no problems doing just that and the documentation only mentions to detach the database before taking the database file (or files) to copy them for later attaching. So I guess you should not just stop sql server and copy the files, but detach db, copy db files, reattach db.

--
SvenC

|||

hi Markus,

Markus Sch. wrote:

Hello,

if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.

please be carefull not to overwrite existing database files... but the "scenario" is viable...

Or is the datebase name part of the .mdf file?

Regards
Markus

only User Instances use the Database Name ( and path) to dynamically build database names..

regards

|||

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

There is a much easiere way that I finally figured out:

- right click on the database you want to copy

- select "Tasks" and subsequently select "Backup" and do a backup.

- right click on "Databases"

- select "Restore database"

- Enter the name of a new database (your copy target) and select the database to be copied as source database

- click "OK" (SQL Server creates a new database from the backup which means that you actually copied the original database)

If you only wanted the database design and not the content, well ... Delete from <tablename> ...

|||

hi,

rf wrote:

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

I said, to be carefull ..

There is a much easiere way that I finally figured out:

- right click on the database you want to copy

- select "Tasks" and subsequently select "Backup" and do a backup.

- right click on "Databases"

- select "Restore database"

- Enter the name of a new database (your copy target) and select the database to be copied as source database

- click "OK" (SQL Server creates a new database from the backup which means that you actually copied the original database)

If you only wanted the database design and not the content, well ... Delete from <tablename> ...

yes, this works as well, but, again, you have to be carefull with the actual physical database file as well.. you have to "move" them according to your needs not overwriting existing database files...

regards

|||

rf571786 wrote:

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

Hello rf,

if you detach a database with Management Studio Express, copy the .mdf and .ldf file, and then try to attach the copied files with Databases - Attach, then in the Attach Dialog the Attach As and Current File Path textboxes still have the old name.

The values are not changed to the new .mdf filename.

You have to type in the new database name in the Attach As textbox and the new file names in the Current File Path textbox. Then it works fine.

Or use a Sql Script. The InfoItems.mdf and Infotems_log.ldf files where copied to InfoItems_Test.mdf and Infotems_Test_Log.ldf and then attached with this Sql Script.

But the logical filename in Database Properties - Files - LogicalName still is InfoItems? It should be InfoItems_Test because the Name Clause was specified in the Sql statement?

Regards Markus

USE [master]
GO
CREATE DATABASE [InfoItems_Test] ON
(NAME = InfoItems_Test,
FILENAME = N'E:\InfoItems_Dev\Development\....\Data\DB\InfoItems_Test.mdf' ),
(NAME = InfoItems_Test_Log,
FILENAME = N'E:\InfoItems_Dev\Development\....\Data\DB\InfoItems_Test_log.ldf' )
FOR ATTACH
GO
if not exists (select name from master.sys.databases sd where name = N'InfoItems_Test'
and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [InfoItems_Test].dbo.sp_changedbowner @.loginame=N'....\....', @.map=false
GO