December 21, 2011 at 3:24 am
Hello,
Being new to SQL and SQL Server, it has often been difficult to grasp some of the ways in which things work and sometimes the books online explanations do not tell you some of the subtleties that would help in understanding things.
My questions - or lack of proper understanding to put it more accurately - relates to the SQL Server backup and restore functionality.
All questions below relate to my test DB which is set to the "Sinple" recovery model and using "Full" backup mode to a disk file.
I would greatly appreciate any advice or answers to any or all of the following please.
1. What is the difference between a "Copy-only backup" and a "Full"
backup that is set to
overwrite the existing media set?
2. When I am appending to a media set and upon each backup I can
see that the "Position" column shown
on the restore page is incremented (as I'd expect). Why do :
a) I have to use the "Device" panel to point to the backup disk file
to be able to select an earlier backup set to restore.
b) Why should I be allowed in the above scenario to select
multiple backups to restore and not
be restricted to any one for restore purposes.
3. Does it make any difference if I place my backup tasks in the
maintenance plan
or as a job. Is there a "best practice" for choosing where such
a task should go.
4. If a backup set has an expiry period set, does this mean that the
backup file will have the embedded
backup set removed once its expiry period is reached - will
this "shrink" the backup file
accordingly and therefore keep backup file sizes in check.
5. I've noticed that when restoring I can select a "Files and filegroups"
mode. When I do this I am
presented with a history of all my backups. As I remove my backup
file for testing purposes and
start backups from scratch, the history is not consistent with the
actual backup data I have. Is there a
way of clearing this history.
I have also observed that the SQL backup appears to be only backing up the "used" part of a database file. I.e I recently truncated a large table and left the database file un-shrunk which left about a 1Gb+ unused area. When this was backed up the size of the backup file reflected the actual used size of the database file as subsequent shrinking of the DB file proved.
This is a useful thing to know, but as far as I can tell is not mentioned in the Books online page.
Apologies for the multiple questions, but I feel that the experience of those in this forum will provide better insights into the above questions than pointing to a technical article somewhere - unless, of course, if the article is written for dummies like me to understand! 🙂
Many thanks for your time and patience.
Regards
Steve
December 21, 2011 at 4:48 am
First of all, BACKUP (Transact-SQL) has a lot of useful information.
1. A Copy only backup does not interfere with your backup strategy. A differential or a log backup will never use a copy only backup as a base.
3. Maintenance plans use SQL Server jobs as well. Maintenance plans is a great tool for creating a "workflow" for your backup strategy, but personally I tend to have scripts for it instead.
4. Expiredate specifies when the backup can be overwritten.
5. Yes. This information is stored in msdb.dbo.backupset and related tables.
December 21, 2011 at 5:13 am
Thanks for the info, but if I may, could I please seek clarification on the following points:
1. I understand that a "Copy-only" backup is independant of other backups - particularly when you're using a "Full" or "Bulk Logged" recovery model for the DB, but how then is such a copy-only backup different from a Full backup on a "simple" recovery mode DB?
4. So, the "append to existing media set" option will actually overwrite an earlier backup set if that one has expired?
5. Are you "free" to delete rows from this backup history? Are there any negative implications to doing so?
Thanks once again 🙂
December 21, 2011 at 7:26 am
raotor (12/21/2011)
1. I understand that a "Copy-only" backup is independant of other backups - particularly when you're using a "Full" or "Bulk Logged" recovery model for the DB, but how then is such a copy-only backup different from a Full backup on a "simple" recovery mode DB?
A normal full backup resets the differential base - the base for any future differential backups. A copy only backup does not. Nothing to do with log backups or recovery models (when it's a copy_only full backup), as full backups don't interfere with log backups anyway.
See http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/
5. Are you "free" to delete rows from this backup history? Are there any negative implications to doing so?
Yes. Management studio uses that to generate the backup lists in the restore dialogs, so no backup history and you'll have to do that yourself. That's about it though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2011 at 1:11 pm
raotor (12/21/2011)
4. So, the "append to existing media set" option will actually overwrite an earlier backup set if that one has expired?
No - these options are really only valid when dealing directly with tape backups. If you are backing up to files (recommended) and use these options the file will just continue to grow until it fills the drive.
5. Are you "free" to delete rows from this backup history? Are there any negative implications to doing so?
The maintenance plan Cleanup History task will take care of this for you. It can be configured to remove history for backups, maintenance plans and agent jobs.
SQL Server agent can also be configured to manage history by keeping xxxx number of rows in the history. I normally disable this option and rely on the Cleanup History task to manage how much history is available.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply