Database backup

Question

Database backup

Minu

SSC Enthusiast

Points: 105
More actions
March 9, 2004 at 6:19 am

#107445

I have a stored procedure that takes a backup. Most of the time it does not overwrite the bak file which it created on the previous day. If I delete the bak file manually then it works. am I missing something....
CREATE PROCEDURE sp_DatabaseBackup_Morning AS
declare @dbname as varchar(1000)
select @dbname = case datepart(weekday,getdate())
when 7 then
   '\\nas01\company\IT Department\Database Backups\sat_backup_morning.bak'
when 1 then
   '\\nas01\company\IT Department\Database Backups\sun_backup_morning.bak'
when 2 then
  '\\nas01\company\IT Department\Database Backups\mon_backup_morning.bak'
when 3 then
  '\\nas01\company\IT Department\Database Backups\tue_backup_morning.bak'
when 4 then
  '\\nas01\company\IT Department\Database Backups\wed_backup_morning.bak'
when 5 then
  '\\nas01\company\IT Department\Database Backups\thu_backup_morning.bak'
when 6 then
  '\\nas01\company\IT Department\Database Backups\fri_backup_morning.bak'
end
BACKUP DATABASE [angie] TO DISK = @dbname WITH INIT , NAME = 'Full Backup of angie'
DECLARE @i INT
select @i = position from msdb..backupset where database_name='angie'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='angie')
RESTORE VERIFYONLY FROM   DISK = @dbname WITH FILE = @i
GO

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

The with INIT should overwrite the previous backup. Not sure why it doesn't work here. I take a different approach. I make a new filename everyday, so I have the date in the file name, i.e., angie_20040209.bak. Then I prune these after xx days, usually 2 here since they go to tape.

Bert De Haes Hall of Fame Points: 3093 More actions · Answer 2

Maybe you should verify sp_configure 'media retention'. You could add RETAINDAYS = 5 to make sure you can overwrite the file, or you could use the SKIP option ...

Important If EXPIREDATE or RETAINDAYS is not specified, expiration is determined by the media retention configuration setting of sp_configure. These options only prevent SQL Server from overwriting a file. Tapes can be erased using other methods, and disk files can be deleted through the operating system. For more information about expiration verification, see SKIP and FORMAT in this topic.

Andy Hartland Valued Member Points: 72 More actions · Answer 3

Another method you could do is use xp_cmdshell to rename the old file, create a new backup and then delete the old file.

This will depend on available space on your server.

If you have a multistep job with 3 steps

1 Rename old file

2 Backup Database

3 Delete old file

If step 2 fails you still have a backup in reserve.

Charmaine Wong Newbie Points: 1 More actions · Answer 4

Try this as one of your SQL job steps:

EXEC xp_cmdshell 'erase /F /Q <filepath>'

sa24 SSCrazy Eights Points: 8027 More actions · Answer 5

Something you can also do is.

1) Create a dummy maintenance plan. By dummy i mean only select the databases and do not select any options of backup database or log.

2) use xp_sqlmaint to script your procedure.