Solved

Backup and Restore on SQL Server

Posted on 2014-09-16
13
295 Views
Last Modified: 2014-09-17
Below is my SQL backup and recovery plan, is it correct ? Is there any similar scenario for reference

Backup SQL Server
1. Full SQL database backup at night
2. SQL Log backup in lunch time - with option "Truncate the transaction log"

Restore database on the same server when database corrupt
1. Backup SQL Log backup - with option "Back up the tail of the log, and leave database in the restoring state"
2. Restore the full SQL database backup at night
      Recovery state : RESTORE with NORECOVERY
      uncheck "Take tail-log backup before restore
3. Restore the SQL log backup in lunch time.
4. Restore the SQL tail log in #1, with RECOVERY.

Restore database on the different server
1. Backup SQL Log backup on the local server
2. Restore the full SQL database backup at night  on the other server
      Recovery state : RESTORE with NORECOVERY
      uncheck "Take tail-log backup before restore
3. Restore the SQL log backup in lunch time. on the other server
4. Restore the SQL tail log in #1, with RECOVERY on the other server
0
Comment
Question by:AXISHK
13 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 40327275
Ok. Depening on the numv=ber of transaction in a day, I would do a log backup backup every 1-2 n hours. That way the log doesn't grow too big.

Also rarely do you need to rorry bbor a tran log restor after the first 24 hours, Do the user want to lose a whole day;w wroth day's transactions?
0
 

Author Comment

by:AXISHK
ID: 40327317
So, Do the above steps correct ? Current backup is full backup at night and transaction log backup at lunch.

Supposing I need to restore the database at 5:00PM evening,

1. backup the transaction log for the current database.
2. Restore the database in NONRECOVER state
3. Restore the transaction log in lunch time
4. Restore the transaction log in #1 and make database in RECOVERY state.

Any comment ?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40327378
Don't forget that with only 1 transaction log backup by day you'll have the tlog file growing during 24h and for what I saw in another question from you, that's an issue that you don't want to have.
For reducing transaction log size you need to make tlog backups more often, depending on the business demands.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:AXISHK
ID: 40327502
Noted. Are the steps in my 1st thread correct ?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40327511
Yes, but you need a STOP AT statement so you can define the point of time that you want to recover the database. If you don't define the point of time it will restore all database so you might keep the issue with the database.
0
 

Author Comment

by:AXISHK
ID: 40327516
For "STOP AT" , is it the argument when restoring the Transaction log ?

I see an article mentioning about tail log backup and restore. Is it just the same as the normal transaction log backup and restore or I need to handle it in a different way ?


Tks
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40327520
You only need a tail backup if the last transaction log backup doesn't cover the time until you want to restore. Even it's a little bit different you handle it like a normal tlog backup.
0
 

Author Comment

by:AXISHK
ID: 40327568
To clarify my understanding, tail log backup can only be applied on the production database if the server can still be connected. ie. backup the last log change and apply it to another server. In case the whole server is crashed, I only use my stored transaction copy to restore the DB in other server, correct ?

One last question, The database and log are on different physical drive, (C:) and (D:) respectively.

Supposing that C is totally crashed, can I still backup the log file in this case as the database is not available ? How can I manually move the log file to another server and then apply the logs to get a complete database copy.

Great Tks
0
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40327572
If you can't connect to a server then you won't be able to do nothing so in these occasions you need to restore from the backups that you have in another location (server, tapes, ...).

Supposing that C is totally crashed, can I still backup the log file in this case as the database is not available ? How can I manually move the log file to another server and then apply the logs to get a complete database copy.
This is a case for using a tail log backup (data files unavailable but log files are) but you don't need to move the log file. What you do in this scenario is perform a tail log backup and restore the backup in another server with MOVE option where you set the new locations for the database files.
0
 

Author Comment

by:AXISHK
ID: 40327597
"perform a tail log backup and restore the backup in another server with MOVE option"  - where should I add the 'MOVE' option ?

Is it the command that posted in my previous question ? Is that the command to backup the tail log when database is not accessible ?

BACKUP LOG DatabaseName
   TO DISK = 'C:\ DatabaseName.TRN'
   WITH NO_TRUNCATE;
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40327602
The MOVE option should be performed in the RESTORE command, not BACKUP.
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
ID: 40327815
I recommed you do this:

1. 1 weekly full backup
2. 1 Daily differential backup
3. Transaction Log backup every 10-15 min, or if you don't need that often, and I don't see why, Brent Ozar recommends every minute(!), then you can do it once an hour.

With transaction log backup  the often you do it the less data you will loose in a database/server failure and the less size it will grow. You NEVER truncate the log file because it will grow right back as it needs anyway, which affects performance.

If you do it in a a professional and neat way I recommend you the Ola Hallegren maintenance solution where there is a backup part:

https://ola.hallengren.com/sql-server-backup.html

I recommend you to look into integrity check and especially index optimization, which is the best you can find.
0
 

Author Closing Comment

by:AXISHK
ID: 40329458
Tks
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

820 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