Recovering a database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Recovering a database

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-10-12 : 01:06:11

question, is there any way to run read-only quries when when in the middle of recoveries...?

if you an example, i have a database where an user deleted records on accident... they gave me an approx. of the time it happened (a 40 minute windows)

so basically i want to restore the most recent full, the the most recent difs, and then the most recent logs.... but once i get close to the time, i want to run some counts to narrow down if i'm on the proper log file or not (tlogs backup every 15 minutes)

thanks!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-12 : 02:01:58

Restore with standby

--
Gail Shaw
SQL Server MVP

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-10-12 : 09:16:56

An elaboration on Gails advice:

RESTORE DATABASE [myDB] ... WITH NORECOVERY
GO
RESTORE LOG [myDB] FROM DISK='d:\backupfolder\mydb_log.bak' 
  WITH 
    NORECOVERY, 
    STANDBY='d:\standbyfolder\mydb_standby.ldf_standby.LDF'
GO

This will leave your database readable.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-10-12 : 12:13:18

and i can still do more restores afterwords?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-12 : 12:36:07

Yes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-10-12 : 17:52:41