copy database

I have copied a Database from one server to another server by detaching and attaching.Everything looks fine but i didn't get the logins associated with the DB onto new server.
How can i get those logins into the database on new server.
Thanks.Search this forum and www.sqlteam.com for sp_help_revlogin. You can run that script and it will produce all the logins with the correct SIDs.|||where should i run this script?on the source server or destination server?|||Run it on the source server. It will give you a permissions script to run on the destination server.

copy database

I wanted copy a database from one server to another server.So far I'm doing this is by detaching and attaching the DB.But when i tried to restore the DB on the new server its giving some error. Is't a good process to detach and attach.PLease let me know if there is any better way of doing this.BACKUP DATABASE database_name
TO DISK = '\\server\share\folder\whatever.bak'

Copy file

RESTORE DATABASE database_name
FROM DISK = '\\server\share\folder\whatever.bak'|||Thanks for ur information.So, is't a bad practice to attach and detach the DB ...|||Thanks for ur information.So, is't a bad practice to attach and detach the DB ...

No, it's ok...and fatser...

But the destination db must already exists, and already be detached...

The restore will create one where one doesn't exist|||Even I have tried to do the same with copy database wizard but it was generating some problems.So i was doing this with attach and detach.However my aim is to copy this database to another server and to have the database available on both the servers.

Thanks.|||Yeah...I have a scheduled job that does that every night...it executes this sproc

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Restore_Production]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_Restore_Production]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create Proc usp_Restore_Production as

ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE TaxReconDB_Prod
FROM DISK = 'D:\Tax\BackUp\TaxReconDB.dmp'
WITH MOVE 'TaxReconDB_Data' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\TaxReconDB_Prod_Data.MDF'
, MOVE 'TaxReconDB_Log' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\TaxReconDB_Prod_Log.LDF'
, REPLACE

ALTER DATABASE TaxReconDB_Prod SET READ_WRITE

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
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

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