Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server disaster recovery

Posted on 2014-03-20
13
Medium Priority
?
257 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Detach & Attach 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.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

636 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