backing up and restoring using the wizards

  • Gail Shaw

    SSC Guru

    Points: 1004474

    From your original post:

    meridius10 (10/20/2011)


    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.

    Why create a new database then restore 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
  • Perry Whittle

    SSC Guru

    Points: 233824

    muthukkumaran Kaliyamoorthy (10/21/2011)


    No no detach/attach is not good option to move the database from one server to another server. It has lots of disadvantages.

    Such as??

    You could also take the database offline, this will shutdown the database and allow you to copy out the files to another server.

    muthukkumaran Kaliyamoorthy (10/21/2011)


    Better you will go backup/restore.

    This way has disadvantages too, the time it takes to backup and then restore the database not to mention copying the backup file between the servers!

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    Perry,

    Yep there is lots of options available. still Everything is "It depends".

    @meridius10 search here Jonathan's post 'Alter database method' and read it.

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

  • meridius10

    Ten Centuries

    Points: 1350

    OK. Well spotted. I changed things a little and just used detach/attach (i.e.did not create a new database).

  • mahesh.dasoni

    SSCommitted

    Points: 1653

    Use this command it will work for you

    Restore database Database_name from disk=' ' with

    move 'logical name of data file' to ' C:\abc.mdf'

    move 'logica name of log file' to 'C:\abc.ldf' , replace

    Logical name of data & logfile you will get from here

    restore filelistonly from disk='backup file which needs to be restored'

  • silvasea696

    Newbie

    Points: 1

    meridius10 (10/20/2011)


    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 Check Identical Files 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?

    Policy-based management and central management servers sql 2008 has some new dba focused features, one of which is the central management server for issuing multi-queries (queries against many servers at once, even down. Sql server central the sql server installation that will host the lync server 2010 central management store and databases is hosted on a supported windows server operating system.

Viewing 6 posts - 16 through 21 (of 21 total)

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