January 14, 2009 at 4:48 am
I want to copy a database to the same server and change the bame at the same time.
How do i do that?
Copy Wizard does not work
January 14, 2009 at 4:57 am
Take a backup and restore with a different name
-Vikas Bindra
January 14, 2009 at 5:03 am
Also do not forget to rename or relocate the .mdf and .ldf files.
January 14, 2009 at 5:26 am
Maybe i'm doing it wrong but:
backup database
restore, changing name
Keeps failing cause it wants to overwrite the original .mdf .ldf files
You would thing microsoft would make this easy
January 14, 2009 at 5:37 am
As steveb mentioned, you have to change the (default) location and name of the mdf, ldf, ndf file during the restore.
if your using T-SQL then check the syntax of 'RESTORE DATABASE' command in BOL
-Vikas Bindra
-Vikas Bindra
January 14, 2009 at 8:39 am
jon.eyre (1/14/2009)
You would thing microsoft would make this easy
The do make it fairly easy but try to make things, like overwriting your database file accidently, a bit more difficult for obvious reasons.
January 14, 2009 at 11:13 pm
To my mind a simpler way of protecting original data would be to rename the mdb and ldb files to the new name given automatically. Even if you did not rename then create a *****_1 version of the files.
The way to change those file names is not immediately obvious. Using the name "options" for the tab to do that defies logic. Calling it "database details" or something would be more logical.
There is also nothing that i could find in microsoft help to show how to do this hence i ended up here.
The copy wizard does not work, it fails and says look in the event log for info. The event log may as well be written in greek for all the sense it makes.
Thankyou very much for your help, i got it working in the end after much frustration.
You guys fill the cavernous gap left by microsoft.
January 17, 2009 at 11:56 am
Here's what I would do:
1) detach your datafile (right-click database name -> tasks -> detach). This makes the .mdf file available for copy/rename.
2) copy & rename your .mdf file (you can re-attach the original if you want). Don't bother with the .ldf file.
3) use sp_attach_single_file_db (in Query analyzer, osql, whatever) to attach the re-named datafile as the re-named database. See the help on sq_attach_single_file_db for syntax.
(NOTE: the sp_attach_single_file_db will create a new .ldf file for you. To be safe, I would copy the .mdf to a new directory before attaching)
I used to do that sort of thing all the time. Backup/restore is a huge pain in comparison.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy