October 16, 2012 at 1:13 am
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
October 16, 2012 at 1:31 am
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, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
October 16, 2012 at 1:46 am
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
October 16, 2012 at 2:26 am
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
October 16, 2012 at 3:24 am
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
October 16, 2012 at 3:54 am
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[/url]
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
October 16, 2012 at 5:35 am
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.
Training cats makes SQL Server look easy
October 16, 2012 at 8:42 pm
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
October 17, 2012 at 2:51 am
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.
Training cats makes SQL Server look easy
October 17, 2012 at 3:02 am
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
October 17, 2012 at 6:47 pm
Hi All,
Thanks for your responses.
Please see my comments below.
select * from msdb.dbo.backupset where database_name='MOR'
-> Returns: 76 rows
select distinct database_name from backupset
-> Msg 208, Level 16, State 1, Line 1
-> Invalid object name 'backupset'.
-> How to execute the script in this URL?
-> http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/67296/
Thanks.
- Peter
October 17, 2012 at 6:48 pm
...
October 17, 2012 at 7:02 pm
Peter2012 (10/17/2012)
Hi All,Thanks for your responses.
Please see my comments below.
select * from msdb.dbo.backupset where database_name='MOR'
-> Returns: 76 rows
Good, you've found your backups then. Hopefully you should find the ones you need among these 76.
select distinct database_name from backupset
-> Msg 208, Level 16, State 1, Line 1
-> Invalid object name 'backupset'.
This is because unlike the first query, this one doesn't specify which database to look in, it simply says 'backupset', which will only work if you're actually in the msdb database when running it.
This one will work regardless of your current db context:
select distinct database_name from msdb.dbo.backupset
-> How to execute the script in this URL?
-> http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/67296/
The script creates the stored procedure USP_DBA_RECENTBACKUPS on the server.
The procedure can later be executed like this:
exec USP_DBA_RECENTBACKUPS
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
October 17, 2012 at 11:49 pm
Hi Vegard Hagen,
Great. It's much clearer now.
Thanks a lot for your explainations.
Have a nice day 🙂
- Peter
October 17, 2012 at 11:53 pm
Hi Gurus,
I've 1 more query. Sorry 🙂
For instance:
-> restore log Database name from disk='c:\PATH_Tlog2.trn' with norecovery
-> GO
Is there a way to put 'restore log' and 'GO' together in 1 line? This is because I've quite a number of transaction logs to be restored.
Also, will it cause any problem if 'GO' is not used?
Please advise.
Thanks.
- Peter
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply