October 20, 2011 at 2:41 am
I have done the following in this order:
- copied an existing database as a test.
- used the backup wizard to backup the database to an external drive.
- deleted the existing database on SQL Server.
- created a new empty database on the existing db with the same name (but with no data in it).
- then tried to restore from the external drive.
however, I am getting the msg "Restore failed for Server...The backup set holds a backup of a database other than the existing "nameofdb" database.
What I am trying to do is to backup the data so it can be installed on a new installation of SQL Server after the operating system is reinstalled and am just testing this process to see if it works properly...
Any ideas?
October 20, 2011 at 2:49 am
IF you don't want the old DB replace it
or
use the WITH MOVE option
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
October 20, 2011 at 2:50 am
Does the message go on to say anything else? I think you can use the REPLACE option to overwrite the existing database. I would advise you to use T_SQL instead of the GUI when backing up and restoring, since it's repeatable and you know exactly what you're getting. If you still prefer to use the wizard, I imagine there's a tick box somewhere that's the equivalent of specifying the REPLACE option.
John
October 20, 2011 at 3:22 am
OK - thanks. I just found something on youtube and tested it out, so perhaps this is also another way of doing things?
October 20, 2011 at 3:29 am
meridius10 (10/20/2011)
http://www.youtube.com/watch?v=XMOh_FRIAFM%5B/quote%5D
Read my post Step by step backup/restore using T-SQL
[/url]
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
October 20, 2011 at 3:38 am
meridius10 (10/20/2011)
http://www.youtube.com/watch?v=XMOh_FRIAFM%5B/quote%5D
This video is not backup/restore it’s detach/attach. I suggest you to use backup restore. No need to detach it and attach it back.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
October 20, 2011 at 3:43 am
Actually, your article doesn't cover use of the REPLACE option. And the Microsoft page you link to is about backup, not restore.
By the way, you don't "have to" change the logical name of the database files, even though you can. They'll play perfectly well without being renamed.
John
October 20, 2011 at 4:02 am
John Mitchell-245523 (10/20/2011)
Actually, your article doesn't cover use of the REPLACE option. And the Microsoft page you link to is about backup, not restore.
By the way, you don't "have to" change the logical name of the database files, even though you can. They'll play perfectly well without being renamed.
John
Yes John agreed but sometime it does create confusion if one or more DB have same logical name.
Thanks john i will update the link. And i haven't cover all the option in that post. I have written another post it covers.
Thanks john 🙂
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
October 21, 2011 at 1:27 am
It's certainly possible to use the GUI to restore a database backup created on a different machine to a new one, but you have to ensure "Overwrite" is checked on the Advanced tab. If you've created an empty database first you will also have to manually edit the file locations in the same tab, because they're unlikely to be the same. It's actually easier if you DON'T create the database first--just do the restore, type a name for the database, and the GUI will automatically name the files appropriately; you'd only need to edit this if you wanted the log files to be located somewhere different than the data files.
October 21, 2011 at 3:09 am
paul.knibbs,
I love to use t-sql as always. And the easiest way is t-sql.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
October 21, 2011 at 5:21 am
I have reinstalled my OS, as well as using the instructions in the video to detach/attach the db, and this appears to have been a relatively straightforward process using the GUI.
October 21, 2011 at 6:07 am
meridius10 (10/21/2011)
I have reinstalled my OS, as well as using the instructions in the video to detach/attach the db, and this appears to have been a relatively straightforward process using the GUI.
No no detach/attach is not good option to move the database from one server to another server. It has lots of disadvantages.
Better you will go backup/restore. You can also do the same (restore) via GUI if you not familiar with t-sql.
Go Google learn the backup/restore using GUI and T-sql. If you need any help ask me.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
October 22, 2011 at 1:11 pm
What's the actual objection to detach/attach?
From what I can see if the .mdf and .log files are copied in to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data, and the user uses the Attach GUI, then all appears OK.
I tested the AdventureWorks installer package and it appears to automate this process as I have done above.
October 22, 2011 at 1:15 pm
Why are you creating a new database and then restoring over it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2011 at 3:38 pm
I am not. All I did was to detach the mdf and log files before wiping my HDD. I then used the attach facility to place them on SQL Server in my new OS.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply