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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
ok - I will try. thank you
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Damian_Gardner, do you still need help with this question?
ASKER
I've requested that this question be deleted for the following reason:
lost track and got bogged down with other issues.
lost track and got bogged down with other issues.
ASKER
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
ASKER