Steve Jones - Editor (8/25/2010)
A differential backup is an incremental. Please use that terminology or it becomes hard to talk about this. As Jason mentioned, the more changes that occur, the further (in time) from the full, the more time and larger this backup will be.To restore the most amount of data, you always need:
- a full backup
- The last differential since that full backup
The schedule of your differential backups should be made to limit data loss, according to your business/environment/needs.
I'm not sure what you mean with "daily biases".
If you use the maintenance plans, you can have it create new files each day, with the timestamp in the name. If you only want to keep one day's worth of files, and if you are sure you are backing things up to tape every night, that might be OK. I don't recommend it, and I have EVERY backup with a unique file name.
There are lots of scripts here: http://www.sqlservercentral.com/search/?q=SQL+2000+backup&t=s
Thank you for your reply, what I mean about daily baises is that I have to back them up daily hence why I need to do differential backup.
I have looked at the link that you have sent me and I was going to use the following script:
CREATE procedure sp_Differentialbackup as
DECLARE @BackupFile varchar(255), @DB varchar(50)
DECLARE @BackupDirectory nvarchar(200), @Name varchar(50)
DECLARE backup_cursor cursor
FOR SELECT name FROM master.dbo.sysdatabases WHERE name not in ('master','model','msdb','tempdb','northwind','pubs')
OPEN backup_Cursor
FETCH next FROM backup_Cursor INTO @DB
WHILE @@fetch_status = 0
BEGIN
--SET @name = @DB + '-'+CONVERT(varchar(50), CURRENT_TIMESTAMP ,112) + '.diff'
select @name = @DB +'\'+ @DB + '_'+convert(char(4),datepart(yyyy,getdate()) )+
replicate('0',2 - len(convert(varchar(2),datepart(mm,getdate())))) + convert(varchar(2),datepart(mm,getdate())) +
replicate('0',2 - len(convert(varchar(2),datepart(dd,getdate())))) + convert(varchar(2),datepart(dd,getdate())) +
replicate('0',2 - len(convert(varchar(2),datepart(hh,getdate())))) + convert(varchar(2),datepart(hh,getdate())) +
replicate('0',2 - len(convert(varchar(2),datepart(mi,getdate())))) + convert(varchar(2),datepart(mi,getdate())) +
'.diff'
SELECT @BackupDirectory = '(Backup Path)' ----Insert Desired Backup Path
SET @Backupfile = @BackupDirectory + @name
--print @name
--print @BackupDirectory
--print @Backupfile
backup database @DB to disk = @Backupfile WITH NAME = @Name, differential
FETCH next FROM backup_Cursor INTO @DB
END
CLOSE Backup_Cursor
DEALLOCATE Backup_Cursor
but obviously I will need to do a full backup first so I will have to perform a full backup first then the next night I will start doing Differential backup. It might be an obvious answer but would I have to replace the following line to full or do I just take out the differential blank:
Backup database @DB to disk = @Backupfile WITH NAME = @Name, differential