August 2, 2005 at 8:19 am
Does anyone know if there is a way to clean out your backup devices when using the append option ?
August 3, 2005 at 7:26 am
There is no way. It is all or nothing based on the INIT or NOINIT option that you use. If you want to retain x many days then you should use a physical device with a new name each backup.
Andy
August 3, 2005 at 7:36 am
Thank you for the reply. Thats what I was afraid of, Seems a little dumb to me.
Peter
August 3, 2005 at 7:45 am
Why is that dumb? You have to consider where this all came from or where it may go. Especially TAPE devices. You usually can't delete a part of the tape backup and that is no fault of SQL Server. But in any case it is really easy to do this. Here is a simple example of how to backup each db to a different file based on the datetime:
---------- Separate file for each day of the week ----------------
DECLARE @DBName NVARCHAR(100), @Device NVARCHAR(100), @Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
-- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Device = N'C:\Data\Backups\DD_' + @DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @Name = @DBName + N' Full Backup'
PRINT 'Backing up database ' + @DBName
BACKUP DATABASE @DBName TO DISK = @Device WITH INIT , NOUNLOAD ,
NAME = @Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @Device WITH FILE = 1
PRINT '--------------------------------------- '
FETCH NEXT FROM cur_DBs INTO @DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Hope that helps,
Andy
SQL MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply