Post reply

Restores for dummies

  • March 31, 2012 at 4:46 pm

    #259342

    Hello!

    Ive been given the task of doing some basic SQL tasks for work (completely new to SQL).

    We have a database that is regularly sent to a 3rd party (create a full backup and send them that) i then put the database in read only mode and wait for them to update it. Then i have to do a restore.

    Ive been doing the restore by deleting the original database and doing a restore to the name of the database i just deleted.

    Is that the best way to do it?

    I was worried that if i did a restore without deleting. If they had made changes like removing a column in a table. Or deleting some rows the restore would only show new data (like if they added a new table. Or new column row etc)

    Thanks for any help.

    The accidental DBA

  • March 31, 2012 at 4:51 pm

    #1467360

    You can restore without dropping the existing database, and it saves the cost of recreating the files. A restore always leaves the restored database exactly how it was at the time of backup, regardless of whether you restore a new DB or over an existing one.

    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

  • March 31, 2012 at 5:04 pm

    #1467363

    Hi Gail,

    Thanks, that should speed things up and good to know its not wrong.. Just a time waste!

    Do i need to tick any of the restore options (in options) to ensure the data is correct (That's the bit that scared me and made me do it the above way!)

    there's options like

    Overwrite the existing database (WITH REPLACE)

    Is the restricted user mode the same as Single user mode?

    Thanks for the quick reply!

    Accidental DBA

  • March 31, 2012 at 5:07 pm

    #1467364

    and to achieve that say you want to overwrite the database if using the gui, or use the 'replace' clause if using TSQL.

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

  • March 31, 2012 at 5:09 pm

    #1467365

    restricted mode means only members of the db_owner role or the actual owner of the database (in this case the person who restored it) can access the database, so not the same as single user mode

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

  • March 31, 2012 at 5:09 pm

    #1467366

    and to achieve that say you want to overwrite the database if using the gui, or use the 'replace' clause if using TSQL.

    Hello George.

    Sorry what do you mean achieve?

    Thanks for the help!

    Accidental DBA

  • March 31, 2012 at 5:12 pm

    #1467367

    staggerlee011 (3/31/2012)

    and to achieve that say you want to overwrite the database if using the gui, or use the 'replace' clause if using TSQL.

    Hello George.

    Sorry what do you mean achieve?

    Thanks for the help!

    Accidental DBA

    the posts crossed, I was actually adding to gails post, yours was not there when I started typing it.

    so you just need to select the overwrite option, no others

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

  • March 31, 2012 at 5:14 pm

    #1467368

    🙂 Thanks!

    Out of interest what would happen if i didn't tick that box then?

  • March 31, 2012 at 5:25 pm

    #1467369

    I would hope it would fail. 🙂

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

  • March 31, 2012 at 5:31 pm

    #1467370

    Really?

    So it would fail? Or it would be so bad that i wish it had failed!?

    Thanks for the help

  • March 31, 2012 at 5:41 pm

    #1467371

    it would fail, as in it wont even attempt to start the restore, thus preserving your database. Set up a test database, back it up and then try and restore it from that backup without the option to prove it to yourself.

    If a restore fails you still have your restore file and you can just restart it. I presume you also have your original backup file you sent to the vendor so you also can use that to restore from.

    and its can't be worse than deleting the database then finding you cannot restore.

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

  • March 31, 2012 at 5:44 pm

    #1467372

    and its can't be worse than deleting the database then finding you cannot restore.

    :blink: I never thought of that!

    Thanks for the help. I will set up a test as you say!

  • March 31, 2012 at 6:02 pm

    #1467373

    george sibbald (3/31/2012)

    it would fail, as in it wont even attempt to start the restore, thus preserving your database. Set up a test database, back it up and then try and restore it from that backup without the option to prove it to yourself.

    That will work if the database is in simple recovery model or the tail of the log has been backed up. What won't work without the REPLACE option is this

    Create database t1

    backup database t1

    drop database t1

    create database t2

    restore the backup of t1 over t2. (restore database t2 from disk = <location of the backup of t1)

    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

Viewing 13 posts - 1 through 12 (of 12 total)

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