How to copy a database to a new name

Question

How to copy a database to a new name

DanG

SSC Eights!

Points: 951
More actions
May 21, 2002 at 11:40 am

#157722

Hello,
I would like to take a copy of my database as it is on one server, and copy it to another server, but with a different name. Can this be done easily?
Dan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Yes. If you do as a file backup and you restore it you can specify the name of the database when restored. You will find this generally easier to do in EM but suppose you want to restore a DB originally named Test1 to a new DB Test2 then goes like this

RESTORE DATABASE Test2

FROM DISK='Drive:\Path\Test1BackuupFile.ext'

WITH

REPLACE

Note: If file need to be moved to a specific location as opposed to what they originally were you will need to use MOVE in th with. See SQL BOL RESTORE for more details.

Now if you want to do this via detach DB (sp_detach_db) or copy of the DB files not in backup using sp_attach_db, you will attach with the original name. Once done then you can issue

sp_renamedb [ @dbname = ] 'old_name' ,

[ @newname = ] 'new_name'

Note: With previous two method you will need to use sp_change_user_login with autofix to align accounts

quote:
Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are the intended outcome. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended.
user must be a valid user in the current database, and login must be NULL, a zero-length string (''), or not specified.

To change the name as you want it to be.

And also of course you can use Import Wizard on the new database or export wizard on the old database to transfer all SQL objects to accomplish that way. However first two way are a bit better at ensuring all items are transfered.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Edited by - antares686 on 05/21/2002 12:24:41 PM

DanG SSC Eights! Points: 951 More actions · Answer 2

Thanks for all of the options!

I used the sp_renamedb, and it looks as though it worked great.

Thanks again,

Dan

DanG SSC Eights! Points: 951 More actions · Answer 3

Now that I have the database renamed, is there an easy way to rename the physical .MDF?

Dan

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Don't believe it will let you as it has internal pointers to itself. I will try later to see if I can get around, I have an idea. However the last method for changing the name would have been able to allow for this.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

DanG SSC Eights! Points: 951 More actions · Answer 5

I was able to change the physical file name by using the "Copy Database Wizard". In there they allow you to set the destination file name.

I think I am all set now.

Thanks for the help.

Dan

Frank Marzan Valued Member Points: 58 More actions · Answer 6

All these can actually be done in the RESTORE DATABASE command see BOL.

restore database new_name from disk = 'c:\sql\backup\d_database_name.bak' with

move 'logical_filename_data' to 'physical_OS_new_name'

etc...

-¢ödêmån-