Database Restore Problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Database Restore Problem

17 posts, Page 1 of 2

1 2 »»

Database Restore Problem

Rate Topic

Display Mode

Topic Options

Author

Message

Peter2012

Posted Tuesday, October 16, 2012 1:13 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, January 26, 2013 6:12 PM
Points: 12, Visits: 32

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

Vegard Hagen

Posted Tuesday, October 16, 2012 1:31 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 494, Visits: 326

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

Peter2012

Posted Tuesday, October 16, 2012 1:46 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, January 26, 2013 6:12 PM
Points: 12, Visits: 32

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

joeroshan

Posted Tuesday, October 16, 2012 2:26 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 573, Visits: 1,169

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

Peter2012

Posted Tuesday, October 16, 2012 3:24 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, January 26, 2013 6:12 PM
Points: 12, Visits: 32

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

Abu Dina

Posted Tuesday, October 16, 2012 3:54 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 494, Visits: 2,158

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.

-----------------------------------
http://www.SQL4n00bs.com

Post #1373100

stuart-leaf-node

Posted Tuesday, October 16, 2012 5:35 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:14 AM
Points: 16, Visits: 136

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

Peter2012

Posted Tuesday, October 16, 2012 8:42 PM

Grasshopper

Group: General Forum Members
Last Login: Saturday, January 26, 2013 6:12 PM
Points: 12, Visits: 32

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

stuart-leaf-node

Posted Wednesday, October 17, 2012 2:51 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:14 AM
Points: 16, Visits: 136

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

joeroshan

Posted Wednesday, October 17, 2012 3:02 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 573, Visits: 1,169

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 »

17 posts, Page 1 of 2

1 2 »»

Permissions