We help IT Professionals succeed at work.

SQL 2008 backup and transaction log backup assistance

SQL 2008 Database Backup.

I'm having a problem with Transaction file backups.
Here is how I am currently setup.
VEEAM Backup Server:
I use VEEAM to backup my SQL2008 Server every day at 8:30 PM and it finishes at about 10:30 PM.
SQL 2008 Server:
I have 3 databases that are for our document management server

I run a maintenance task that
Reorganizes 3 databases at 9:00
Rebuild the index of these 3 databases at 9:30
Update statistics on these 3 databases at 10:00
then backs up these 3 databases at 11:00 PM to:
C:\Program Files (x86)\Microsoft SQL Server\Backup\"Database name 1"
C:\Program Files (x86)\Microsoft SQL Server\Backup\"Database name 2"
C:\Program Files (x86)\Microsoft SQL Server\Backup\"Database name 3"

I run a separate maintenance task that backs up the transaction logs dtabases of the 3 above databases, it backs up evey 2 hours and backs up to this location:
D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup

I run another plan that shrinks the transactiol log evey 4 hours at 1:00 5:00 9:00 PM and 1:00 5:00 9:00 PM .


My problem is two fold:
1. I do not know much about SQL and what is needed in order to restore a database. I've started reading a little bit but was hoping for someone to just tell me in laymans terms how the backup and restorte is supposed to work and what is needed.
2a. The transaction log shrinking never runs  - it always fails.
2b. the transaction log backup task fails  often it actually only succesfully runs at 10:00 PM 12:00 AM 2:00 AM and 4:00 AM - then at 6:00 AM and evey 2 hrs until 10:00 PM it fails.
I see the failures in the log file viewer - I can attach a picture if needed.
Let me know what information to post and as long as I can find it , I'll provide the info.
Thanks for you assistance
Comment
Watch Question

Sr. BI  Developer
Commented:
Well, I'm sure you know how to google it around and find some links ,that's why i'm not gonna provide you with back and restore links, it's all over the internet...
and there's many strategies, such as when,what, how and where to backup.

LINK

You need to adopt what suites your budget, considering software or native sql server backup solutions, remote locations and space for storage.

you don't have to shrink the log as it should shrink itself after a successful backup.
that will help you  LINK

Yep, it would be nice to attached the error msg from your error log.
Commented:
Here is how backup and restore works.

Full backups contain all the pages in a database that is backed up and it contains it to a certain point, since data is usually always changing.  It records the last LSN (Log Sequence Number) that it has in its backup.

Log backups work by backing up the transaction logs to a certain LSN as well.  The idea of a Transaction log is that if you backup transaction logs forever they will always be sequential and never miss an LSN.  The transaction log is a circular log so if you have 1 GB of TLog, SQL will use the tlog until it hits the end of the log file and then wraps around to the front. (virtual log files are important to know about, but you can read about it on sqlskills.com blogs, I won't go that deep here).  The reason I bring up Virtual Log files is that when you shrink a tlog (which you should really not do) it can only shrink to the point to last virtual log file that is not marked active.  So this may be why you cannot shrink the tlog on the schedule.

Now, things seem to be blocking you from getting your other tlog backups, but here is what I recommend.

You should do a Full at a time when less activity like rebuilding indexes, etc. is happening because of the I/O bottleneck that happens.  You should also avoid tlog backups during those times because the tlog is being hit hard when you rebuild the indexes, or stats.

You can have a 2 hour log backup, but you should spread the other activities out so that they do not EVER overlap the schedule of the backup.  A backup is more important and should not be impacted, because that lengthens the amount of data you may lose if you cannot recover that last backup.

If you have more questions about what I have written, please feel free to ask.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
1)
A db restore requires a full backup of the db.  ALL SQL Server restores *must* start from a full db backup.  [The command in SQL Server is BACKUP DATABASE.  I'm not familiar with VEEAM backup, so don't know if it takes backups that SQL Server can use to restore a db.  I doubt it, but I can't say for sure.  You'd have to take to the vendor or other VEEAM support.]  

Restoring the full backup gets your db back to what it looked like at the time of the backup.

But that might be several hours (or even one or more days) ago.  So, SQL also gives you the option of applying log backups after the full backup is restored to recover the db past the full backup time.  If you do this, you *MUST* apply *ALL* log backups *IN ORDER*.

For example, say you take a full db backup at 8PM.  You take log backups every 2 hrs, so at 10PM, 12AM, 2AM, etc..

At 9:43AM, your entire db gets wiped out (including the current log file).  You can restore the backup from 8PM.  You could run from there, and everything from 8PM would be lost.

Or you can apply log(s), beginning with 10PM log, then 12AM log, then 2AM log, then 4AM log, then 6AM log and then 8AM log, and now the db looks at it did at 8AM.  All activity between 8AM and 9:43AM is lost, and you cannot it recover it.

Note that you could stop at, say, 2AM if you wanted.  In order to do that, you MUST still apply the 10PM, 12AM logs first, then the 2AM log.  Then, the final db looks like it did at 2AM; all db activity between 2AM and 9:43AM is lost.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
2)
Short answer:
Don't allow the TRUNCATE job to run.  That is not even supported in SQL 2008.  That will very likely correct the log backup failures.

More descriptive answer:

Because there are different requirements for db recoverability, SQL gives you a choice.

If you have a db that you don't need to recover, you can "tell" SQL that, and it will not retain log activity for that db.  This allows SQL to improve overall performance for that db (but at the cost of recoverability).

SQL calls this the "recovery model" (or "recovery mode", same thing).  

"FULL" recovery model means SQL keeps all log activity, you can backup and apply the logs and more fully recover your db.  All log activity is kept until it is backed up.  If you don't backup the log, it will keep growing until you run out of disk.  Prior to SQL 2008, you could force SQL to throw away the log activity even if it was not backed up (which was called "log truncation").  SQL 2008 does not allow that.

"SIMPLE" recovery model means SQL does not keep log activity beyond the minimum necessary to operate the db.  Earlier activity on tThe log will constantly be overwritten.  Log backups are not allowed.  Log truncation is not needed, because SQL is constantly truncating the log as soon as it can.

To see/verify the recovery model of all your databases, you can run this query in SSMS:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM master.sys.databases

The last column will tell you if SQL can't clear the log for some other reason, such as replication (if it's being used), etc..

Author

Commented:
Thank you, My errors seem to be gone now.  
I shifted my VEEAM backup job to a little earlier in the day to make sure it never overlapped the SQL backup job.  I also removed the shrink transaction log task.  
My thought is the removal of the Shrink transaction log database is what fixed my issue
Either way - Thank you all