backing up and restoring using the wizards

  • meridius10

    Ten Centuries

    Points: 1350

    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?

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    IF you don't want the old DB replace it

    or

    use the WITH MOVE option

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • John Mitchell-245523

    SSC Guru

    Points: 148667

    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

  • meridius10

    Ten Centuries

    Points: 1350

    OK - thanks. I just found something on youtube and tested it out, so perhaps this is also another way of doing things?

    http://www.youtube.com/watch?v=XMOh_FRIAFM

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    meridius10 (10/20/2011)


    OK - thanks. I just found something on youtube and tested it out, so perhaps this is also another way of doing things?

    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/

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    meridius10 (10/20/2011)


    OK - thanks. I just found something on youtube and tested it out, so perhaps this is also another way of doing things?

    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/

  • John Mitchell-245523

    SSC Guru

    Points: 148667

    muthukkumaran Kaliyamoorthy (10/20/2011)


    Read my post Step by step backup/restore using T-SQL

    [/url]

    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

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    John Mitchell-245523 (10/20/2011)


    muthukkumaran Kaliyamoorthy (10/20/2011)


    Read my post Step by step backup/restore using T-SQL

    [/url]

    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/

  • paul.knibbs

    SSCoach

    Points: 15270

    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.

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    paul.knibbs,

    I love to use t-sql as always. And the easiest way is t-sql.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • meridius10

    Ten Centuries

    Points: 1350

    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.

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    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/

  • meridius10

    Ten Centuries

    Points: 1350

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • meridius10

    Ten Centuries

    Points: 1350

    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 21 total)

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