How Do Log and DB Backups Work?

I am trying to understand how the backup of db and tlogs works and if my scheme is a good one, and how a restore process would work with it.

I have ALL DBs set to backup once per week, FULL, all going to individual bak files per db. Expiry is set to 31 days.

I have ALL DBs set to backup once per DAY, DIFFERENTIAL, all going to individual bak files per db, TO THE SAME directory as the Full backup above. Expiry is set to 31 days.

Half of my DBs are set to FULL logging, which I think I need. I have ALL DBs set to backup TLogs every 4 hours, 1 file for each log, going to a differnt directory than the bak files. Expiry is set to 7 days.

The backups are being stored on a different physical disk than where the MDF and LDF files exist. I am verifying integrity in all 3 backup operations.

1. Is this a good scheme?
2. Do the Diff backups append to the same files as the Full backups since I have them pointing to the same directory?
3. Is 31 days of retention for bak and 7 for trn adequate?
4. Say the worst happened, what would be the restore process? Would I just choose to restore a db and sql server would display all my full, diff and tlog backups history and I would just check off the ones that I wanted to restore or is the process different?
5. Am I missing anything else that I need to do to ensure a proper backup scenario?

Thanks
advlgxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Faiga DiegelSr Database EngineerCommented:
1. Is this a good scheme? Thats a pretty nice backup strategy.
2. Do the Diff backups append to the same files as the Full backups since I have them pointing to the same directory? No. It has its own backup file.
3. Is 31 days of retention for bak and 7 for trn adequate? Depends, I have previously experience when my boss asked me to restore the database 6 months ago to check something temporarily.
4. Say the worst happened, what would be the restore process? Would I just choose to restore a db and sql server would display all my full, diff and tlog backups history and I would just check off the ones that I wanted to restore or is the process different? Latest full backup + latest differential backup + all trasaction log back up after the diff backup
5. Am I missing anything else that I need to do to ensure a proper backup scenario? Test it! See of it works!

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
advlgxAuthor Commented:
Also, I am getting errors on the TLog backups. They say somthing like:

Failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

BUT, when I set up the backup, there was a note that said SIMPLE logged DBs would be skipped. Why do these errors occur?

AND the following error occurs in the job history as well

Executing the query "declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'master' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'master' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''master'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'e:\\Backup\\master_backup_200802071200.trn' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
" failed with the following error: "Cannot open backup device 'e:\\Backup\\master_backup_200802071200.trn'. Operating system error 2(The system cannot find the file specified.).
VERIFY DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Does this just mean that the VERIFY command could not execute as the trn file didn't exist because it's backup failed in an earlier step/action?
0
Anthony PerkinsCommented:
>>I have ALL DBs set to backup TLogs every 4 hours<<
You cannot backup log transactions for databases set to Simple Recovery model.
0
Faiga DiegelSr Database EngineerCommented:
Set your database to Full recovery mode (unless your databse dont need to be recovered upto the point of failure)

alter database DadatabaseName
set recovery full
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.