backup maintenance plan

Question

Hi,

   I set a backup maintenance plan for my database on the server. I am doing daily: transaction log backups, weekly: full backup.

I assume with the overwrite option I can never go back two weeks in time in case of a disaster, where as with the append exising version I can go back two weeks in time. Am I right? What do you recommend overwrite or append?

Answer 1

better create a database maintenance plans which create transaction log files in a general manner i mean with out append and over write

create maintencace plan as below

1) FUll Backup scheduled Weekly

2)transaction log backup daily in a simple manner which creates new transaction log files,

3) clean up jobs which deletes backup sets which are older than 2 weeks,

Reason:-

append:-

if the appended file is corrupted you will be in a position with out a single position

overwrite:

this is a good  option but consumes CPU Resources while overwritting  the transaction  log backup

suggestion ;- make a  differentail backup  job which helps you to recover ASAP when any thing goes wrong

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 2

You always have to choose either append or overwrite, there is no third option.

How can I do it without append and overwrite?

Answer 3

when a maintenance plan is create

backup files are create as

database name+backup+year+month+day

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 4

my backup files are created with a single name, can you tell me how to include the year month and day? the Add button used when browsing for the file does not let me do that.

Answer 5

HI Follow this for solution

http://www.mssqltips.com/sqlservertip/1318/automating-transaction-log-backups-for-all-sql-server-databases/

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 6

Okay

Transaction_logs will be done daily in different files dated year-month-day.  (With a  clean-up task once a month deleting files one month old)

Full back up once a week with overwrite.

Differential daily with overwrite.

Does that sound reasonable to you, do I still need to generate the .bak files with year-month-day as well?

Assuming my fullbackup is damaged and differential is damaged too, can I restore a database from a transaction_log.

Answer 7

Assuming my fullbackup is damaged and differential is damaged too, can I restore a database from a transaction_log.

Hi,

You cannot restore a database only from transaction log backup. Before you can restore a particular transaction log backup, you must first restore the following previous backups without rolling back uncommitted transactions, that is WITH NORECOVERY:

• The full database backup and the last differential backup, if any, taken before the particular transaction log backup. Before the most recent full or differential database backup was created, the database must have been using the full recovery model or bulk-logged recovery model.
• All transaction log backups taken after the full database backup or the differential backup (if you restore one) and before the particular transaction log backup. Log backups must be applied in the sequence in which they were created, without any gaps in the log chain.

For more details, please refer to this article: Restore a Transaction Log Backup (SQL Server).

Best Regards,
Ray Chen