?
Solved

SQL Server disaster recovery

Posted on 2014-03-20
13
Medium Priority
?
253 Views
Last Modified: 2014-03-26
I have a production and a backup server. SQL Daily backup is run on the production server and then copy to the backup server. Afterwards, the file is restored into the backup server.

In case the production server has probelm, I should able to copy the log file and then restore on the backup server to fully recover the missing transaction for the day. However, I can't find this option in MS SQL Management Studio. Should I carry this in GUI ? How can I archieve it ?

Tks
0
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39941637
you cannot just copy the .ldf (log) file and work with that one.

the correct process is this:
* full backup (db.bck) + regular transaction log backups (db.trn)
* full restore without recovery (using the db.bck), the db remains in "recovery" status (RESTORE DATABASE ... WITH NORECOVERY)
* apply each and every transaction log backup (db.trn) as they come in, keeping the db in recovery status (RESTORE LOG ... WITH NORECOVERY).


when you want then to put the backup server "life", you take the last log file backup, and apply it WITH RECOVERY instead, or, if you don't have any more, you do:
RESTORE DATABASE dbname WITH RECOVERY

see also:
http://technet.microsoft.com/en-us/library/ms187495.aspx
0
 

Author Comment

by:AXISHK
ID: 39941658
Can I do that through GUI, or need to go the the command base ?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39941662
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Author Comment

by:AXISHK
ID: 39941673
Sorry, still can't identify the setting ? Where can I select "regular transaction log backups" and "apply each and every transaction log backup (db.trn) as they come in" through the GUI ? Can you guide me through screen dump ?

Tks
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39941675
You would probably want to use MS SQL's log shipping feature to achieve Guy's method explained above. It starts with the restore of a backup in norecovery mode, after which the production server "ships" the log backups to the backup server at regular intervals, which are then restored automatically. How often you want to ship a log depends on your needs (how much data loss is acceptable in case of a disaster, how much network bandwidth do I have available, etc).
You can check here for more information and how to set it up:

http://technet.microsoft.com/en-us/library/bb895393(v=SQL.105).aspx
0
 

Author Comment

by:AXISHK
ID: 39941683
Sure, will take a look on this.

But can we backup and restore trx log through GUI  ??

Tks
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39941699
The log shipping method does this on your behalf. Once it's set up, you don't have to worry about the backup and restore, only the first setup has to be done this way. It can be done through the GUI, but with limited settings. It's better to do the initial restore of the database and logs through the Query analyser instead.
I had to do this myself not too long ago without any previous experience, and it's not too hard. Here's what I did:

- Start the log shipipng on the primary server database as described in the steps. This will start to create logs.
- The following day, take your nightly backup and restore it on the secondary server with norecovery.
- On the primary server, run the attached script called "create log restore commands". This script generates a restore command for each of the log backups created AFTER the last full backup, which is what you need to get your secondary database up to date. Make sure to replace yourDBname with your actual database name, leaving the single quotes.
- Go to the secondary server where your backup is restored and run the log restore commands generated in the previous step. This shoudl restore all the logs from the primary server to the secondary server.
- Enable the log shipping to the secondary server as described in the link I sent earlier.
- Keep track of the log shipping for a while through the log shipping monitor. You can get there by right clicking on the server name in the management studio, then going to reports - standard reports - transaction log shipping status.

It might take some time for the database to be fully in sync depending on the size of your log backups and backup/restore intervals. In some cases it took the better part of a day for me, initially thinking I made a mistake but the following day discovering that the logs were being restored successfully.
Good luck, it's not as hard as it looks.
Create-log-restore-commands.sql
0
 

Author Comment

by:AXISHK
ID: 39956251
When backing up the transaction log, there is an option "Back up the tail of the log, and leave the database in the restoring state  "
Actually, how does it affect the source and target database ? During the recovery on the target, I can select "RESTORE with NORECOVERY" and the it will also leave in norecovery state. What's that option for ? Tks
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39956270
Not quite sure to be honest. I didn't manually back up the transaction log. I started the log shipping on the primary first, then restored a full database backup from the night after. At that point you can use the periodic log backups created by the log shipping to bring the target server up to date. The script I attached can be used to create the relevant restore commands.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 39956300
>When backing up the transaction log, there is an option "Back up the tail of the log, and leave the database in the restoring state  "

I think you confused some of the options.
when you do a FULL backup, you can include the tail of the log.
however, this has nothing to do with the db being in restoring state... you can only have that option while RESTOREing, not while BACKUPing ...
0
 

Author Comment

by:AXISHK
ID: 39956353
No, this option is available when backing up the transaction log on the source database (see attached), Tks
SQL-Backup.png
0
 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 1200 total points
ID: 39956501
Pretty sure you can just use the standard option of truncating the log. Just make sure you have the log shipping started on the source database before you take the backup otherwise you'll have a gap between the full backup and the start of the log backups.
0
 

Author Closing Comment

by:AXISHK
ID: 39957890
Tks
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question