SQL Server Performance Forum – Threads Archive
Transactional/Differential backup
I’m running transactional and differential backups as well as full backups on my DB. The problem I am encountering, is that the transactional/differential files are now filling up the disk. How can I fix this problem? Ideally I’d like to make a full backup today, and start new transactional/differential backups sort of like rotating a log file…. anyone care to help a newbie?the differential backup is run by the following command: BACKUP DATABASE [VMIPLive] TO DISK = N’D
B BackupVMIPLive_Differential.BAK’ WITH NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N’VMIPLive Differential’, NOSKIP , STATS = 10, NOFORMAT
the transactional backup is run by the following command:
BACKUP LOG [VMIPLive] TO DISK = N’DB BackupVMIPLive_Transaction.BAK’ WITH NOINIT , NOUNLOAD , NAME = N’VMIPLive Transaction backup’, SKIP , STATS = 10, NOFORMAT
so how do I archive/reset these and maintain backup integrity?
Move the backups to another server or backup the files to SAN on a regular basis. If you just need to rotate the files on the same server you need to implement and extra step in the backups jobs to remove old files. This could be done with say a vbscript using the Filesystem Object. Search the net and you will find scripts that remove file after X number of days etc. Note that after a full backup the chain of differential and transaction log backups must be intact. You can not remove any of these files before doing a new full backup. So find out how many full backups and accompanying diff/tran log backups that you can fit on the server and then implement scripts accordingly.
]]>