The Ramblings of Two Microsoft .NET Developers, TFS, and Visual Studio ALM Guys --- "Yes, we are both named Ed."

SQL Restore



So have you ever had the need to restore a SQL database because someone hit the delete button on a table or the whole Database? Well, my friend I have seen this happen several times. Here is some code to restore your database back to a certain point in time.

Start Server in single user mode :
sqlservr -m

RESTORE DATABASE YourDB

FROM YourBackUpDevice
  WITH NORECOVERY
GO
 
-- If you have diff backups here is where you can add, since we are using WITH NORECOVERY
-- Remeber to restore them in order :)

RESTORE LOG YourDB
   FROM YourBackUpDevice
   WITH RECOVERY, STOPAT = 'Jul 12, 2006 10:00 AM'   <------- what ever time before the mishap
GO
 
Of course you can do all this fun stuff via the GUI (and have the EM "not responding") but keep it real and use SQL statements in QA; if you really want to keep it real, use osql to run your statements:)
 
Ed K.
AKA
Eddie Kisinger
Edward Kisinger
CodeMonkey
 
Posted in


Name
E-mail
(will show your gravatar icon)
Home page

Comment (Some html is allowed: a@href@title, b, blockquote@cite, em, i, strike, strong, sub, sup, u) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview