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