User profile for rgrey – Support

Activity overview Badges (0) Posts (0) Comments (3)

Comments

Are you sure ? It's a day thing. If you've set the retention period to two days, you won't see anything being removed unless it is a backup from > 48 hours previously. And, considering I only posted this today, you can't have gone over two days yet [image] My 7 day strategy is working good. The SP is scheduled to be run at 4am, 10am, 4pm and 10pm everyday. Looking at the directory right now (22 Nov 5.30pm), the earliest backup remaining is correctly (15 Nov 10pm) - this should be removed when the (22 Nov 10pm) backup is created as it will be greater than 7*24 hours old. / comments

Are you sure ? It's a day thing. If you've set the retention period to two days, you won't see anything being removed unless it is a backup from > 48 hours previously. And, considering I only poste...

0 votes

This is what I wrote to backup all databases on a daily basis. It's a single stored procedure that you can schedule whenever. It will create a control table that lists the databases and defines whether they should be backed-up and for how long to retain previous backups. By default, backup = Y and retention period = 7 days. New databases are automatically added with these defaults (so you don't forget to add new databases to your backup schedule). The control table can then be manipulated manually, if you desire, to change the defaults. And, it could even be modified to include an encrpyt/decrypt value - the SP below automatically encrypts all databases for me. You'll have to generate your own password etc in the @... parameter at the end of the stored procedure, and modify the TO DISK string accordingly. Create the SP in the master database. The control table will also reside master. Not sure if this helps, but it shows what you can do ... or what SQLBackup 4.0 will possibly provide going forward [image] -- -- R.Grey -- 11-Nov-2005 -- -- Stored Procedure: myspEncBackupDaily -- -- Procedure to be scheduled via SQL Server Agent as often as required -- to backup and encrypt all databases using SQL Backup 3.2.0 -- -- Backup files are retained for the period specified in the -- myspEncBackupDaily table (default 7 days) -- -- -- Backup file format -- -- <dbname>_yyyymmdd_hhmmss.sqb -- -- Usage: -- -- exec myspEncBackup 'D:\SQLBackup\' -- CREATE Procedure myspEncBackupDaily @Path varchar(128) as -- create _myspEncBackupDaily table if not exists (select * from dbo.sysobjects where id = object_id(N'[master].[dbo].[_myspEncBackupDaily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) begin Create table [master].[dbo].[_myspEncBackupDaily] ( Name varchar(128) primary key nonclustered , BackupFlag varchar(1) not null check (BackupFlag in ('Y','N')) , RetentionPeriod int not null ) end set nocount on declare @... varchar(1000) -- Get all database names create table #DBName ( ID int identity (1,1) , Name varchar(128) not null , RetentionPeriod int null ) insert #DBName (Name) select name from master..sysdatabases -- Include any new databases in the backup insert _myspEncBackupDaily ( Name , BackupFlag , RetentionPeriod ) select #DBName.Name , 'Y' , 7 -- default 7 days from #DBName left outer join _myspEncBackupDaily on _myspEncBackupDaily.Name = #DBName.Name where _myspEncBackupDaily.Name is null and lower(#DBName.Name) not in ('tempdb','master','model','msdb') -- Remove any non-existant databases delete _myspEncBackupDaily where not exists ( select * from #DBName where #DBName.Name = _myspEncBackupDaily.Name ) delete #DBName -- loop through databases declare name varchar(128) , @RetentionPeriod int , @ID int , @MaxID int insert #DBName (Name, RetentionPeriod) select Name, RetentionPeriod from _myspEncBackupDaily where BackupFlag = 'Y' select @MaxID = max(ID) , @ID = 0 from #DBName while @ID < @MaxID begin -- get next database to backup select @ID = min(ID) from #DBName where ID > @ID select name = Name , @RetentionPeriod = RetentionPeriod from #DBName where ID = @ID declare @eID int , @eMaxID int , @eName varchar(128) -- now do the backup select @... = '-SQL "BACKUP DATABASE TO DISK = ''' + @Path + '\' + name + '_' + convert(varchar(10),getdate(),112) + '_' + replace(convert(varchar(10),getdate(),108), ':', '') + '.sqb'' WITH NAME = ''' + name + ' ' + replace(convert(varchar(10),getdate(),6),' ','-') + ' ' + convert(varchar(8),getdate(),108) + ''', DESCRIPTION = ''Backup on ' + replace(convert(varchar(10),getdate(),6),' ','-') + ' ' + convert(varchar(8),getdate(),108) + ' Database: ' + name + ''', INIT, PASSWORD = ''<ENCRYPTEDPASSWORD>........</ENCRYPTEDPASSWORD>'', ERASEFILES = ' + convert(varchar(3),@RetentionPeriod) + ', COMPRESSION = 3" -E' exec master..sqlbackup @... end GO / comments

This is what I wrote to backup all databases on a daily basis. It's a single stored procedure that you can schedule whenever. It will create a control table that lists the databases and defines whe...

0 votes

Damn formatting ! The smilies at the end of the SP are actually an 8 followed by a ) UPDATE: Aah ! Looks like Brian sorted it - my firefox (running with the MSIE user agent due to MS ISA Server rubbish) doesn't seem to like the WYSIWYG buttons for code block etc. :-( / comments

Damn formatting ! The smilies at the end of the SP are actually an 8 followed by a ) UPDATE: Aah ! Looks like Brian sorted it - my firefox (running with the MSIE user agent due to MS ISA Server rub...

0 votes

Total activity: 4
Last activity: February 05, 2025 13:08
Member since: February 04, 2025 15:02

Following: 0 users
Followed by: 0 users
Votes: 0
Subscriptions: 1