June 24, 2009 at 10:49 am
I have a SQL SERVER 2008,which I take full backup on the weekends and take differential back up from mon-fri,I noticed that although when I am taking differential backup,in reality it is taking full backup(by checking the size of back up file),by checking the database I noticed that recovery mode of database is simple,so I changed it to full hoping that this will solve the problem.But still when I am taking Differential backup,it is taking full backup instead.
June 24, 2009 at 10:57 am
Differential backups don't depend on a recovery model. They work in full or simple. A diff contains the data that has changed since the last full backup. Is it possible that most of the data in the database changes within a week and hence it looks like the diff is backing up everything?
What's the command that you're running to do the diff backups?
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
June 24, 2009 at 11:00 am
One, by changing your recovery model to full, you may soon find you transaction log file taking all your disk space. You now also need to start running transaction log backups.
Two, could you please post the code for your full and differential backups? If we see the code, we may be able to tell you what's wrong.
June 24, 2009 at 11:21 am
The problem is that,I have another server which has the same database(Identical) but on that machine the size of differential back up is gorwing normally.But for the problematic machine,the size of differential backup is exactly the same as full backup for each day.
declare @dbname varchar(100)
declare @backupdb varchar(100)
declare @logevent varchar(150)
declare @physicaldevicename varchar(200)
Create table #dblist
(Dbname varchar(100) null,
dbsize int null,
remarks varchar(255) null)
insert into #dblist
exec sp_databases
declare dbnamecursor cursor for
select dbname from #dblist
where dbname not in('tempdb','northwind','model','pubs','_Dummy','master','msdb')
open dbnamecursor
fetch next from dbnamecursor into @dbname
while @@fetch_status=0
begin
select top 1 @physicaldevicename=physical_device_name
from msdb..backupmediafamily
where physical_device_name like '%'+@dbname+'_Full%'
order by media_set_id desc
if not exists
(select physical_device_name
from msdb..backupmediafamily
where physical_device_name=@physicaldevicename)
begin
set @backupdb='\\sql_backups\'+@@servername+'\'+@dbname+'_Full_'+convert(varchar(20), getdate(),112)+'.bak'
backup database @dbname to disk=@backupdb
end
else
begin
backup database @dbname to disk=@physicaldevicename with differential
end
fetch next from dbnamecursor into @dbname
end
drop table #dblist
close dbnamecursor
deallocate dbnamecursor
June 24, 2009 at 11:33 am
if not exists
(select physical_device_name
from msdb..backupmediafamily
where physical_device_name=@physicaldevicename)
begin
set @backupdb='\\sql_backups\'+@@servername+'\'+@dbname+'_Full_'+convert(varchar(20), getdate(),112)+'.bak'
backup database @dbname to disk=@backupdb
end
else
begin
backup database @dbname to disk=@physicaldevicename with differential
end
Have you tested and confirmed that the expected branch of the IF statement is been taken?
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
June 24, 2009 at 11:35 am
Are you rebuilding all indexes before this? That would cause substantial changes and make the diff approach the size of the full.
If you run a full, and then run a diff after immediately, what are the sizes?
June 24, 2009 at 11:47 am
I checked and it goes to correct branch of IF statment(It goes to differential backup), and before taking differential back up I do not do any re-indexing.and also the sise of differential backup and full backup are the same.
June 24, 2009 at 1:47 pm
Looking at the logic you have here, it appears to me that you are appending the differential backup to the last full backup.
Since each differential backup would add to the size, my guess is that you don't have any activity at all on that database and therefore the backup is not growing.
I personally don't like appending backups to the same file. I put all backups in a folder with different names. It's just easier for me to look at a folder and see if all the files I expect actually exist.
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
June 24, 2009 at 5:38 pm
I checked even when I took the differential backup in seperate files,I noticed the size of differential backup and full backup is the same.
Should I do any extra steps?I thought the "differential backup" with take back up only the changes that has been done after full back up
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply