Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a db lets call it 'mysite_db' on a server and i have created another copy in my own pc which is also 'mysite_db', now i would like to attach the db (from my own pc) to the main sql server which already has this db name. meanning i have to change my local db's name (i guess i also need to change the physicall file) but im lost with how to do that, can u please guide me how?

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

share|improve this question
add comment

migrated from stackoverflow.com Sep 20 '12 at 12:45

This question came from our site for professional and enthusiast programmers.

2 Answers

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
2. Rename mdf and ldf files
3. 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)
4. 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

5. 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

share|improve this answer
add comment

When you attach the database give it the name "myNewSite_db"

enter image description here

If you save the file in a different directory you don't even need to change the file name.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.