Link to home
Start Free TrialLog in
Avatar of Damian Gardner
Damian Gardner

asked on

SQL Server backups not appending

We are taking full backups of a SQL DB and the transaction logs.  Both are set to append.  Both jobs have been running since Monday. When we check the restore options for the DB and the logs, the logs show multiple restore points, while the DB only shows the most recent backup.  they both appear to be set the same, so why the difference?
SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Damian Gardner
Damian Gardner

ASKER

Thanks for your reply Arnold.  It is a SQLServer 2008 server on Windows 2008 R2, single standalone SQL system.  Using SQL Server jobs to do the backups, through the Studio.  So if I read your comments right, you're saying that APPENDING is not advisable, and that we should instead set it to over write?  strange thing is that the transaction backups show up as unique backups, while the full db backup does not.
I am not sure what or how you are scheduling the job.  Often , the name of the backup file should be randomized based on the date/time when the backup runs.

It seems your transaction log is so setup, but your full backup seems to be using a backup device which has a static filename based on the device.
if you are using management plan, for transaction, make sure the same destination is used.

if you are manually creating the job, you need to use the convert and combination of date format 112 for yyyymmdd and the hhmmss from format 114,108

http://vadivel.blogspot.com/2011/09/convert-datetime-into-yyyymmddhhmmss.html
ok - I will try.  thank you
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sorry guys - forgot about this one.  there has been a space problem for the last week, and so there have been no successful job runs recently.  I need to clear the space and let it run tonight to show a screen shot.  standby.  thanks
Well - now I only have the option to restore the full database from the most recent backup (last night) - the "Transaction Log" option is greyed out in spite of having run 5 hourly log backups since yesterday.  when I created this post, I was seing the option to restore any one of the various hourly log backups. not sure why now.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Damian_Gardner, do you still need help with this question?
I've requested that this question be deleted for the following reason:

lost track and got bogged down with other issues.
thanks for your help guys.  I turned off APPEND and am using a script to juggle the backu8ps around so I don't lose them.  thanks again