locked
how to restore database? RRS feed

  • Question

  • This is ridiculous - I can't find any information on [b]getting started[/b] with a database restore. Every search I run comes back with "when I try to restore..." or "After restoring..." but I'm stymied before I can even get to this stage.

    In SQL server management studio under "Database Objects" it's easy to create a backup "container", and just as easy to dump the db to it. But I've never had to restore any of the hundreds of database backups that have been created (yeah, we've been that lucky up till now). Nothing on restoring though, *anywhere*.

    So where do I start?

    Wednesday, November 17, 2010 4:53 PM

Answers

  • Hi ChrisBedford,

     

    Olaf and Hugo's suggestion is great, but I would like to suggest you refer the following links to "get started" with a database restore both through SQL Server Management Studio and T-SQL:

     

    How to: Restore a Database Backup (SQL Server Management Studio)

    RESTORE (Transact-SQL)

     

    But I noticed that you mentioned "how to restore Exchange Database",  since the Exchange database is totally different with SQL database, I would like to suggest you post this question to Exchange forum if you are really using the Exchange database.

     

    Please feel free to ask if you have any questions.

     

    Thanks,
    Weilin Qiao

     

    • Proposed as answer by Willy Taveras Monday, November 22, 2010 11:49 AM
    • Marked as answer by Alex Feng (SQL) Sunday, December 5, 2010 9:15 AM
    Monday, November 22, 2010 8:05 AM

All replies

  • Hello Chris,

    You could start in SSMS with right-mouse click on "Databases" => "Restore database ...". You can restore the backup eighter over an exitsing db or to a new database. Choose the option "From device", add a backup media and select the BAK file you like to restore.

    Have also a look at the "option" page. See also Backing Up and Restoring Databases in SQL Server and SQL Server 2000 Backup and Restore, even if you use a never version.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    • Proposed as answer by Pradeep Adiga Wednesday, November 17, 2010 6:05 PM
    Wednesday, November 17, 2010 5:33 PM
  • *blushing deep pink* Thanks Olaf... I right-clicked everything *but* on "Databases".

    I know that was dumb of me, but it seems just a little obfuscating of MS to put that option /there/ and not next to "Backup". Anyway... many thanks. I knew it had to be something really basic!

    Chris

    Wednesday, November 17, 2010 5:44 PM
  • You can start it also with a click on one database => Tasks => Restore => "Database ..." or "Filegroup ..."
    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Wednesday, November 17, 2010 6:00 PM
  • OK thanks - anything to make this easier for the user

    But I have run into another "issue" here: SSMS doesn't seem to be recognising the backups that we get from running this command

    sqlcmd -S{servername} -E -i"C:\Shared Files\SQL Backups\scripts\SQLBackup.sql"

    where the script file SQLBackup.sql contains the lines

    SELECT @sql = 'BACKUP DATABASE {dbname} TO DISK = ''C:\Shared Files\SQL Backups\{dbname}.BAK'' WITH INIT'
    EXEC (@sql)
    
    Doing something wrong?
    Wednesday, November 17, 2010 6:13 PM
  • Hi Chris,

    When you make backups through the GUI of SSMS, rows are automatically
    added to the backup history in the special msdb database. This
    information is then later used by SSMS when you need to restore a
    database.

    If you manually (or, in your case, with a command-line sqlcmd script)
    run the BACKUP statement, you get the same backups but no information
    in msdb. That means that SSMS cann't assist you with the restore, but
    it's still possible.

    In SSMS, after selecting the "Restore Database" dialog and specifying
    a database, choose "From device" as the source, then click the "..."
    button to activate the "Specify Backup" dialog. In this screen, click
    "Add", then use the file selector to navigate to the backup you want
    to restore.

    Once your current emergency is over, I advice you to read up on backup
    and restore, and set aside some time to practice some restore
    scenarios on a test server. So that next time something hits the fan,
    you are prepared and know what to do.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Wednesday, November 17, 2010 11:02 PM
  • Once your current emergency is over, I advice you to read up on backup
    and restore, and set aside some time to practice some restore
    scenarios on a test server. So that next time something hits the fan,
    you are prepared and know what to do

    Many thanks Hugo, you are a star

    And yes - you are so right - always firefighting, never building sprinkler systems...

    My biggest problem with this - er, problem - was not being able to find the requisite reading material. If you search, for instance, for "how to restore Exchange database" you will get thousands of hits and have to spend your first hour or so just decising whose method to go for. In this case I couldn't find *any* of the basic information above that you and Olaf gave me. Any suggestions where to start looking?

    Thursday, November 18, 2010 5:26 AM
  • Hi ChrisBedford,

     

    Olaf and Hugo's suggestion is great, but I would like to suggest you refer the following links to "get started" with a database restore both through SQL Server Management Studio and T-SQL:

     

    How to: Restore a Database Backup (SQL Server Management Studio)

    RESTORE (Transact-SQL)

     

    But I noticed that you mentioned "how to restore Exchange Database",  since the Exchange database is totally different with SQL database, I would like to suggest you post this question to Exchange forum if you are really using the Exchange database.

     

    Please feel free to ask if you have any questions.

     

    Thanks,
    Weilin Qiao

     

    • Proposed as answer by Willy Taveras Monday, November 22, 2010 11:49 AM
    • Marked as answer by Alex Feng (SQL) Sunday, December 5, 2010 9:15 AM
    Monday, November 22, 2010 8:05 AM
  • Thanks WeiLin

    That looks like it is the kind of resource I was looking for. Have not read it yet because the moment has pased, but I certainly will peruse when next I get a gap (I don't think I'm doing anything between 2 & 4 a.m. next Thursday) so I will bookmark it.

    I only mentioned Exchange as an *example* of the kind of resourse I was looking for, and which is easy to find, to point out the difference with SQL. I am *not* looking for help with Exchange, if I was I would certainly go to the Exchange forums, thank you.

    Chris

    Monday, November 22, 2010 6:51 PM