SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Restore Problem


Database Restore Problem

Author
Message
Peter2012
Peter2012
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 157
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
Vegard Hagen
Vegard Hagen
SSChasing Mays
SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)

Group: General Forum Members
Points: 617 Visits: 387
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)


Peter2012
Peter2012
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 157
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
Roshan Jospeh
Roshan Jospeh
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1629 Visits: 2087
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
Peter2012
Peter2012
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 157
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
Abu Dina
Abu Dina
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1267 Visits: 3323
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
stuart-leaf-node
stuart-leaf-node
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
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

Peter2012
Peter2012
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 157
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
stuart-leaf-node
stuart-leaf-node
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
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

Roshan Jospeh
Roshan Jospeh
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1629 Visits: 2087
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search