Solved

Backup and Restore on SQL Server

Posted on 2014-09-16
13
288 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 45

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
 

Author Comment

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

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 45

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 45

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 45

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

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, …
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.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

14 Experts available now in Live!

Get 1:1 Help Now