Solved

SQL Backup

Posted on 2014-09-11
2
201 Views
Last Modified: 2014-09-15
Please analyze.  SQL full backup everynight(NOT copy_only)  at 7pm.  Log file(NOT copy_only) every night at 8pm.  Log backup every 4 hours(COPY_only).  My network admin wants the copy_only log backup because it will reduce the number of logs backup files to restore in a disaster.  The full backup at 7 will create checkpoint and the log backup at 8 is regular.  My question is:  will the copy_only log backups be of any value.  on paper, the copy_only log in the afternoon should contain all transactions since the last log backup(8 pm the night before).  Am I missing something?

Thank you,
0
Comment
Question by:BUCKBERG
2 Comments
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40318697
I can't understand the option of COPY ONLY for transaction log backups. That won't truncate the logs and will be a waste of space. COPY ONLY option should be used only for special purposes (extra backup without affecting the backup chain).

Also don't understand if you have a full backup at 7PM and one hour later a tlog backup, you'll only have the transaction log truncated again 24h later.

You should review your backup strategy. If the idea is to reduce the number of log backups then you are missing a differential backup.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40323425
Agreed.  That's a very odd backup structure.

So your network admin does SQL db recoveries?  Do you not have a DBA?!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

808 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