April 28, 2009 at 9:33 am
I have created a procedure which will create backups of all the databases on the server for a SQL SERVER express instance when called from OSQL.Here is the code I have used to backup:
DECLARE @str1 varchar(50), @str3 varchar(50), @strSQL Varchar(500),@strDB Varchar(50)
SELECT @str1='BACKUP DATABASE ', @str3='.bak'' WITH RETAINDAYS = 5, INIT,SKIP, STATS = 10'
Select @strDB =Min(name) from master..sysdatabases WHERE NAME not in ('model','msdb','tempdb') and databaseproperty(name, 'IsOffline') <> 1
While @strDB is not null
begin
Select @strSQL=@str1+'['+@strDB+']'+@bkplocation+@strDB+REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') +@str3
Print @strSQL
Execute (@strSQL)
Select @strDB = Min(name) from master..sysdatabases WHERE NAME not in ('model','msdb','tempdb') and name > @strDB and databaseproperty(name, 'IsOffline') <> 1
end
Unfortunately The backups does not get deleted after 5 days.Can any of you SQL GURUS help me solve this?
thanks in advance
April 28, 2009 at 9:41 am
wannabe dba (4/28/2009)
I have created a procedure which will create backups of all the databases on the server for a SQL SERVER express instance when called from OSQL.Here is the code I have used to backup:DECLARE @str1 varchar(50), @str3 varchar(50), @strSQL Varchar(500),@strDB Varchar(50)
SELECT @str1='BACKUP DATABASE ', @str3='.bak'' WITH RETAINDAYS = 5, INIT,SKIP, STATS = 10'
Select @strDB =Min(name) from master..sysdatabases WHERE NAME not in ('model','msdb','tempdb') and databaseproperty(name, 'IsOffline') 1
While @strDB is not null
begin
Select @strSQL=@str1+'['+@strDB+']'+@bkplocation+@strDB+REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') +@str3
Print @strSQL
Execute (@strSQL)
Select @strDB = Min(name) from master..sysdatabases WHERE NAME not in ('model','msdb','tempdb') and name > @strDB and databaseproperty(name, 'IsOffline') 1
end
Unfortunately The backups does not get deleted after 5 days.Can any of you SQL GURUS help me solve this?
thanks in advance
you are expecting RETAINDAYS to delete the file? retaindays only protects the file for 5 days -0 it does not delete the file.
I would advise using maintenance plans for this. It's all build in
MVDBA
April 28, 2009 at 9:42 am
michael vessey (4/28/2009)
wannabe dba (4/28/2009)
I have created a procedure which will create backups of all the databases on the server for a SQL SERVER express instance when called from OSQL.Here is the code I have used to backup:DECLARE @str1 varchar(50), @str3 varchar(50), @strSQL Varchar(500),@strDB Varchar(50)
SELECT @str1='BACKUP DATABASE ', @str3='.bak'' WITH RETAINDAYS = 5, INIT,SKIP, STATS = 10'
Select @strDB =Min(name) from master..sysdatabases WHERE NAME not in ('model','msdb','tempdb') and databaseproperty(name, 'IsOffline') 1
While @strDB is not null
begin
Select @strSQL=@str1+'['+@strDB+']'+@bkplocation+@strDB+REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') +@str3
Print @strSQL
Execute (@strSQL)
Select @strDB = Min(name) from master..sysdatabases WHERE NAME not in ('model','msdb','tempdb') and name > @strDB and databaseproperty(name, 'IsOffline') 1
end
Unfortunately The backups does not get deleted after 5 days.Can any of you SQL GURUS help me solve this?
thanks in advance
you are expecting RETAINDAYS to delete the file? retaindays only protects the file for 5 days -0 it does not delete the file.
MVDBA
April 28, 2009 at 9:44 am
please ignore my posts - i didn't read the code correctly
MVDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply