Friday 8 May 2009

How to find the Database Restore Details in SQL Server 2008

It is often useful to know the details from where the database is restored.The restore history of the database is stored in the msdb table "restorehistory".I recently came across this query which gives the complete information of restore history including the Restored Login , From & To Location of the Files

SELECT
rsh.destination_database_name AS [Database],
rsh.user_name AS [Restored By],
CASE WHEN rsh.restore_type = 'D' THEN 'Database'
WHEN rsh.restore_type = 'F' THEN 'File'
WHEN rsh.restore_type = 'G' THEN 'Filegroup'
WHEN rsh.restore_type = 'I' THEN 'Differential'
WHEN rsh.restore_type = 'L' THEN 'Log'
WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
WHEN rsh.restore_type = 'R' THEN 'Revert'
ELSE rsh.restore_type
END AS [Restore Type],
rsh.restore_date AS [Restore Started],
bmf.physical_device_name AS [Restored From],
rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id


You can apply filter criteria such as the restore date , Database by addding a where clause to the existing query as shown below

WHERE
rsh.restore_date >= DATEADD(dd, "No of Past Days" , GETDATE())
AND destination_database_name = ISNULL( "DB Name", destination_database_name)
ORDER BY rsh.restore_history_id DESC


37 comments:

Anonymous said...

generic xanax how long does generic xanax last - xanax side effects recreational

Anonymous said...

xanax online xanax side effects on liver - xanax side effects overdose

Anonymous said...

buy tramadol online tramadol overdose damage - buy tramadol online no prescription usa

Anonymous said...

buy tramadol online buy tramadol cod next day delivery - order tramadol with money order

Anonymous said...

buy tramadol online tramadol 750 - tramadol dose get high

Anonymous said...

buy tramadol online tramadol on drug test - buy tramadol online with american express

Anonymous said...

buy tramadol online tramadol for dogs and side effects - tramadol overdose in canine

Anonymous said...

xanax online xanax dosage strengths - xanax and alcohol more drug_interactions

Anonymous said...

tramadol online no prescription tramadol repeat order - tramadol klonopin

Anonymous said...

carisoprodol no prescription carisoprodol in urine test - carisoprodol wikipedia

Anonymous said...

buy tramadol online tramadol hcl high erowid - tramadol withdrawal ease

Anonymous said...

buy tramadol online tramadol dosage 70 lb dog - tramadol hcl high erowid

Anonymous said...

buy tramadol online no prescription buy tramadol online forum - tramadol for dogs fever

Anonymous said...

xanax online xanax side effects pregnancy - xanax and alcohol heath ledger

Anonymous said...

cialis no prescription overnight buy cialis online consultation - generic cialis versus cialis

Anonymous said...

xanax online xanax high description - withdrawal short term xanax use

Anonymous said...

order cialis online canada cheap cialis fast - cialis vs levitra

Anonymous said...

xanax online xanax side effects withdrawal - generic xanax capsule

Anonymous said...

buy tramadol online with mastercard buy tramadol online no prescription usa - tramadol dosage slow release

Anonymous said...

http://landvoicelearning.com/#62431 buy tramadol online for cheap - cheap tramadol eu

Anonymous said...

buy tramadol online no prescription tramadol hcl 50 mg how many can i take - tramadol 50mg tablets used

Anonymous said...

where to buy tramadol online tramadol online usa - buy tramadol online 100mg

Anonymous said...

buy tramadol 100mg tramadol hcl user reviews - can you buy tramadol online

Anonymous said...

buy tramadol with cod tramadol bargain discount code - tramadol hcl 50mg tab mylan side effects

Anonymous said...

buy tramadol why can you buy tramadol online - tramadol for dogs high

Anonymous said...

buy ativan online ativan high bluelight - ativan abuse

Anonymous said...

Cheap Louis Vuitton Bags 43302 Cheap Chanel Bags 888914 Burberry Handbags 390140 http://hermesbirkinsale.cabanova.com/

Anonymous said...

Chanel Handbags on sale 980898 Louis Vuitton Bags sale 947850 discount christian louboutin 165148 http://cloutlets.webs.com/

Anonymous said...

cheap christian louboutin shoes 221588 Burberry Outlet 406241 Louis Vuitton Handbags 194699 http://chaneloutletonline.cabanova.com/

Anonymous said...

christian louboutin outlet online 367306 Chanel Handbags on sale 914640 Burberry Bags 778086 http://guccioutletonline.cabanova.com/

Anonymous said...

christian louboutin outlet store 865242 Cheap Louis Vuitton Bags 955979 Louis Vuitton Bags outlet 358281 http://hermesbirkinsale.cabanova.com/

Anonymous said...

Burberry Handbags 19991 cheap christian louboutin 539515 Louis Vuitton Handbags outlet 515909 http://guccioutletonline.cabanova.com/

Anonymous said...

buy tramadol online tramadol causes high blood pressure - tramadol 50 mg cheap

Anonymous said...

[url=http://www.win7license.com]windows 7 ultimate upgrade key[/url] Obama is doing what he has to in order to have a fighting chance against the old fat white evangelical republicans that get sick to their stomach when they have to say Mr. [url=http://www.win8activationkey.com]windows 7 professional product key[/url] Cxaesozxw [url=http://www.windows7pro.co.uk]win 7 ultimate key[/url]
pnkicy 876736 [url=http://www.robesenligne.com/]robe de cocktail[/url] 998776 [url=http://www.vestidostienda.com/]vestidos de novia[/url]

Unknown said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Unknown said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

Unknown said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03