Database Restore Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Database Restore Problem

Author

Topic

Peter2012
Starting Member

27 Posts

Posted - 2012-10-15 : 11:25:27

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

Hommer
Aged Yak Warrior

808 Posts

Posted - 2012-10-15 : 12:41:37

Normally, sql server will pickup those backup files on its retore process.
Do you have transaction log backup, or differential backup to begin with?

Peter2012
Starting Member

27 Posts

Posted - 2012-10-15 : 20:51:02

Hi Hommer,

Thanks for your response.

Yes, I've some transaction logs.

However, I don't know which one to apply.

Could you advise?

Thanks.

Cheers,
Peter

suneeloduru1
Starting Member

3 Posts

Posted - 2012-10-16 : 11:54:57

Hi peter

Whenever the database is corrupted or any issue accrued. you just follow the below steps

first take the tail log backup of the particular database . Tail log backup generally assists to backup the tail of the transaction log i.e. the Active Log especially during DB crash situations

1)first you have to Restore full backup.
2) restore latest differential backup.
3) after differential backup what are all transaction log backups restore one by one
then finally restore tail log backup.
like
backup log database name to disk='d:\path_tail.trn' with no_truncate
restore database database name from disk='c:\path_Full.bak' with norecovery,replace
restore database Database name from disk='c:\PATH_Diff2.bak' with norecovery
restore log Database name from disk='c:\PATH_Tlog1.trn' with norecovery
restore log Database name from disk='c:\PATH_Tlog2.trn' with norecovery
restore log Database name from disk='c:\PATH_Tail.trn' with recovery

Suneel

Peter2012
Starting Member

27 Posts

Posted - 2012-10-16 : 22:37:33

Hi Suneel,

Thanks for your response and advise.

Regarding restoration of transaction logs, do I need to put 'GO' in every line?

-> restore log Database name from disk='c:\PATH_Tlog2.trn' with norecovery
-> GO

Does it make any differences with or without 'GO' ?

Thanks.

Cheers,
Peter

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-17 : 06:22:05

SQL Server interprets GO as a sign that the current batch of T-SQL statements should be sent to an instance of SQL Server. So yes, placing a GO is a good idea

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Peter2012
Starting Member

27 Posts

Posted - 2012-10-17 : 08:17:59

Hi Jack,

Thanks for your response and advise.

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

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-10-17 : 14:29:19

GO is used as a batch separator - it tells the client to send everything to the server and execute it.

You don't really need it in this situation after every restore log command. Instead, you can put a semi-colon after each restore command. The semi-colon is a statement terminator...

Peter2012
Starting Member

27 Posts

Posted - 2012-10-17 : 20:51:00

Hi jeffw8713,

Great. Thanks for the advise. :)

- Peter

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-18 : 01:52:44

@Peter2012 - my pleasure.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources