Copy & rename db so I can attach both copies

I have a db called 'mysite_db' on a server and I have created a copy on my local machine which is also 'mysite_db'. I would like to attach the db (from my local machine) to the server which already has this db name. Meaning I have to change my local db's name (I guess I also need to change the physical file). I'm lost with how to do that. Can you please guide me?

My goal is to load the 2 databases on the server for example: 'mysite_db' and 'myNewSite_db'

If You want to use the same directory for your mdf\ldf files You will have to (physically) rename them.

1. Detach files from local PC

1. Rename mdf and ldf files

1. Copy\Move the files to the server directory where the original Database is stored.

(In My example I used D:\Data for mdf and E:\Logs for ldf, change the path before running the script)

1. Use this code:

USE [master]
GO
CREATE DATABASE [myNewSite_db] ON 
( FILENAME = N'D:\Data\myNewSite_db.mdf' ),
( FILENAME = N'E:\Logs\myNewSite_db_log.ldf' )
FOR ATTACH
GO

1. If You want to change the logical file name to be the same as the physical one use:

(Assuming the original logical file name was "mysite_db")

USE [myNewSite_db]
GO
ALTER DATABASE [myNewSite_db] 
    MODIFY FILE (NAME=N'mysite_db', NEWNAME=N'myNewSite_db')
GO
ALTER DATABASE [myNewSite_db] 
    MODIFY FILE (NAME=N'mysite_db_log', NEWNAME=N'myNewSite_db_log')
GO

Good luck,

Roi

StackExchange Database Administrators Q#24610, answer score: 20