September 15, 2011 at 4:19 am
Hi
I am new to SQL Server. I am busy studying for the 70-450 exam and are working in a complete different field. My goal is to find work as a junior DBA.
I have setup SQL Server 2008 R2 on my laptop with the Adventureworks databases to practice certain things constantly. I had it installed on a virtual machine, but that kills my resources and makes it hard to jump between the host and the vm. Therefore I installed SQL on windows 7.
I scheduled 3 jobs for backup in full recovery
1. Full backup to occur once a week, Mondays @ 9:00am. The job ran on Monday and succeeded
2. Differential backup to occur every day @ 11:00am.
3. Transaction log backup to occur every 60mins starting at 8:00am running until 5:00pm.
The differential backup gives a error every day when it runs:
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AdventureWorks2008R2.bak'
If I run the job manually it succeeds. I am just guessing, but could this be because the backup device is in use by the transaction log backup? If I look at the log of the Transaction Log backup job, it also failed on Monday @ 9:00am, when the full backup was running.
If this is the case, I have a question or two:
1. If the Full backup succeeded but the T-Log failed, does it mean the Full backup gets priority?
2. If question one = yes, then why do Transaction log win over differential backup?
3. If the statement I made is correct that the backup device is in use, how do I get passed this problem without changing the schedule?
Thanks,
Pierre
September 15, 2011 at 4:27 am
I strongly, strongly recommend that you do not back everything up to the same backup file - put in a timestamp as part of your backup name and have a new file for each backup. I'd advise checking out Ola Hallengren's excellent scripts rather than try to re-invent the wheel:
Nothing "has priority", it's all a matter of timing in terms of which schedule gets there first and there's no magic fix other than make sure the schedules don't conflict with each other
September 15, 2011 at 4:31 am
Ok cool, thanks. So best is then that I create different backup devices for:
1. Full backup
2. Differential backup
3. T-Log backup
I will check it the link you posted
September 15, 2011 at 4:39 am
The link posted above contains an extremely useful set of scripts suitable for regular maintainance.
However, since you are trying to learn things, you should write your own scripts and practise.
Also, as rightly pointed out, keep all backups in separate files. A good way to do is append timestamp at the end of the file name.
something like
'myserver_mydb_full_'+convert(varchar,getdate(),112)+'.bak'
September 15, 2011 at 4:41 am
pierrelessing (9/15/2011)
Ok cool, thanks. So best is then that I create different backup devices for:1. Full backup
2. Differential backup
3. T-Log backup
No. Ignore backup devices and write each backup to its own file. Far easier to find when you need them.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply