Solved

SQL Server disaster recovery

Posted on 2014-03-20
13
234 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
  • 6
  • 4
  • 3
13 Comments
 
LVL 142

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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39941662
0
 

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 12

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 12

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 12

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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 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 12

Accepted Solution

by:
Koen Van Wielink earned 300 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now