SQL 2000 - How to automate incremental backups

Question

How to you automate SQL Server 2000 incremental backups using SQL Server Agent and change the backup up name so that it has the current timestamp of when it gets backed up?

Answer 1

Here's a nice overview of the options at MSDN. Basically you'll have to write a T-SQL script, or a combination of a batch scheduled task with a SQL Server scheduled task.

If you're lazy, check out RedGate Backup. It does pretty much all you ask with its wizard.

Answer 2

Tweak this backup log script to get what you are after: DECLARE @date varchar (14), @disk varchar (255), @File varchar (260), @cmd varchar (255) SELECT @date = convert (varchar(12) , getdate(), 112) + substring (convert (varchar(12) , getdate(), 114),1,2) + substring (convert (varchar(12) , getdate(), 114),4,2) + substring(convert (varchar(12) , getdate(), 114),7,2) select @disk = '\' + @server + @path + @database + '\' + @database + 'tlog' + @date + '.TRN'

BACKUP LOG @database TO DISK = @disk