[b]Write your backup statement so that each time the backup runs it backs up to a new file, easiest way is to stamp the date and time into the file name.
Me again. I'm trying to take this advice, and timestamp my backup files.
I did a search and found some help on this site here
But my own version of it (below) brings back an error message.
declare @backupfile nvarchar(2000)
set @backupfile = N'E:\path\databasename_LOG_BACKUP_' + replace(convert(nvarchar(50), getdate(), 120), ' ',':') + N'.trn'
BACKUP LOG [databasename] TO DISK = @backupfile
The error message:
Msg 3201, Level 16, State 1, Line 6
Cannot open backup device 'E:\path\databasename_LOG_BACKUP_2013-09-18:13:04:40.trn'. Operating system error 123(failed to retrieve text for this error. Reason: 15105).
Msg 3013, Level 16, State 1, Line 6
BACKUP LOG is terminating abnormally.
Googling suggests that this is a permissions issue, but I can backup to E:\path\databasename
_LOG_BACKUP etc. using the GUI, so does that mean it's not
a permissions issue?
(I also thought it might be a space issue but there is absolutely tons of space on the E drive.)