Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Database Restore Problem Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 1:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 27, 2014 3:00 AM
Points: 21, Visits: 52
Hi Gurus,

We've a DR server which is using VM machine, running Windows 2008 R2 (Standard). Had configured MS SQL Server 2008 R2 and restored the recent backup of PRD database (.bak file) into the DR server, for instance: backup of 14 Oct 2012.

We're using the following restore options:
- Most recent possible
- Overwrite existing database (WITH REPLACE)
- Leave database ready .... (RESTORE WITH RECOVERY)

After the DB restoration is completed successfully, we've checked the number of records and found they aren't tallied with our production records.

Is that true that we also need to restore the require transaction logs to fix this problem?

If yes, please advise on how to check what are the required transaction logs.

Appreciate for any of your help and advise.

Thanks.


- Peter
Post #1373059
Posted Tuesday, October 16, 2012 1:31 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:50 AM
Points: 517, Visits: 346
Hello.

Yes, you will need to restore transaction log backups (or a diff) if you are restoring to a point in time which is more recent than your latest full backup. As for identifying the appropriate log backups, most tools will assist you or actually do this automatically. Which tool are you using to do the restore?




Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

"It is better to light a candle than to curse the darkness."
(Chinese proverb)

Post #1373064
Posted Tuesday, October 16, 2012 1:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 27, 2014 3:00 AM
Points: 21, Visits: 52
Hi,

Thanks for your response.

We're using Microsoft SQL Server Management Studio (SQL Server 2008 R2) to perform the restore.

Is there a way to check what are the required transaction files (.TRN) for the restore?

Thanks.


- Peter
Post #1373069
Posted Tuesday, October 16, 2012 2:26 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:08 AM
Points: 617, Visits: 1,273
You can query msdb to see the backups available for a database.

I had written a script long back for the same

http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/67296/

The data is not synch because you have not applied all the backups or your backups are old


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1373074
Posted Tuesday, October 16, 2012 3:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 27, 2014 3:00 AM
Points: 21, Visits: 52
Hi,

Thanks for your help on the SQL query.

I've executed the query on my DR server where I've restored the DB, but there is no output.

The message is only showing:
Command(s) completed successfully.

Please kindly advise.

Thanks.


- Peter
Post #1373090
Posted Tuesday, October 16, 2012 3:54 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
The script creates a stored procedure.

You need to run

exec USP_DBA_RECENTBACKUPS 'YourDBName' for backups for one database

or exec USP_DBA_RECENTBACKUPS by itself for backups of all DBs on the server you're running this on.


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


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1373100
Posted Tuesday, October 16, 2012 5:35 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 2, 2014 11:08 AM
Points: 16, Visits: 142
Peter2012 (10/16/2012)
Hi,

I've executed the query on my DR server where I've restored the DB, but there is no output.



Hi,
This is the problem. SQL Server records the backup information in the msdb database on the server where the backups were done. So this information isn't available to you on the DR server.

Assuming you have access to all trn files taken post the backup you've restored you'll need to create a list of SQL statements to restore them all. The exact format will depend on:

If you want it to the most recent transaction, you'll need to restore them all, but only the last one will need WITH RECOVERY.

If you want it to a particular point in Time or LSN, then you'll need to postfix each with RECOVERY, STOPAT or RECOVERY STOPATMARK depending on which - http://msdn.microsoft.com/en-us/library/ms186858%28v=sql.90%29.aspx

Depending on how many there are I normaly do some cut and pasting in Excel from DIR output, or wrap some powershell around it to build them up.



blog | napalmgram@Twitter


Training cats makes SQL Server look easy
Post #1373159
Posted Tuesday, October 16, 2012 8:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 27, 2014 3:00 AM
Points: 21, Visits: 52
Hi Grasshopper,

Thanks for your response and advise.

I've tried to execute the script on my actual server where the backup is taken, but there is still no output.

Anything that I've missed out?

Thanks.


- Peter
Post #1373602
Posted Wednesday, October 17, 2012 2:51 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 2, 2014 11:08 AM
Points: 16, Visits: 142
Interesting.

If you run:

select * from msdb.dbo.backupset where database_name='<insert you database name here>'

on the original box are you getting anything returned?

That table stores the backup 'run' information. So you should get a row in there for every full, differential or transaction backup run by that server on a database. If it's empty it's very unusual. But if it's got some data in it then it will tell you what you need.


blog | napalmgram@Twitter


Training cats makes SQL Server look easy
Post #1373685
Posted Wednesday, October 17, 2012 3:02 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:08 AM
Points: 617, Visits: 1,273
You need o run this query in th eserver you take the backups. ie the original server.


Make sure you give the correct database name. look at the databasename available in backupset table in msdb.

Select distinct database_name from backupset


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1373691
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse