Re: Overwriting backups
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 01/09/05
- Next message: David Gugick: "Re: SQL version"
- Previous message: Mike Epprecht \(SQL MVP\): "Re: error '80020009' HELP!!!"
- In reply to: Leon Shargorodsky: "Re: Overwriting backups"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 8 Jan 2005 22:06:30 -0500
Not that I know of. This isn't a bug it is simply the way it works. There
is alreay a viable way to handle this. That is to use multiple devices as I
already mentioned. Here is a simple example of how to do this with tsql:
---------- Do a backup and create a separate file for each day of the
eek ----------------
DECLARE @DBName NVARCHAR(50), @Device NVARCHAR(100), @Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Device = N'C:\Backups\DD_' + @DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @Name = @DBName + N' Full Backup'
BACKUP DATABASE @DBName TO DISK = @Device WITH INIT , NOUNLOAD ,
NAME = @Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
-------------------------------------------------------------------------
---- Removing Older Backup Files -------------------
DECLARE @Error INT, @D DATETIME
SET @D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @Error = remove_old_log_files @D
SELECT @Error
--------------------------------------------------------------------------
---- *** Procedure to remove old backups **** ------
CREATE PROCEDURE remove_old_log_files
@DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(500), @FName VARCHAR(40), @Error INT
DECLARE @Delete VARCHAR(300), @Msg VARCHAR(100), @Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
SET @Error = @@ERROR
IF @Error <> 0
BEGIN
SET @Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @Fname
WHILE (@@fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @Delete = 'DEL "C:\Backups\' + @FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @Delete
IF @@RowCount > 1
BEGIN
SET @Error = -1
SET @Msg = 'Error while Deleting file ' + @FName
GOTO On_Error
END
-- PRINT @Delete
PRINT 'Deleted ' + @FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @Error
On_Error:
BEGIN
IF @Error <> 0
BEGIN
SELECT @Msg + '. Error # ' + CAST(@Error AS VARCHAR(10))
RAISERROR(@Msg,12,1)
RETURN @Error
END
END
GO
-- Andrew J. Kelly SQL MVP "Leon Shargorodsky" <Leon Shargorodsky@discussions.microsoft.com> wrote in message news:176BA8ED-9759-4D19-BD95-25086540C399@microsoft.com... > Thank you, Andrew, I hope this is something that is going to be addressed > in > Yukon. > Thanks again for your help! > > "Andrew J. Kelly" wrote: > >> You can't if you are using a single device. It is all or nothing with >> your >> only options being to use INIT or NOINIT. INIT will remove ALL files in >> the >> device and NOINIT will simply append. Don't use a logical device and >> instead backup to a different file name each time. Then you can delete >> what >> you want. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Leon Shargorodsky" <Leon Shargorodsky@discussions.microsoft.com> wrote >> in >> message news:FA6D9E38-FD99-456D-93B0-7C3ED322E3E1@microsoft.com... >> > I'm backing up my transaction log on backup device every 15 minutes. I >> > want >> > every backup expire in 1 hour so only 4 sequential transaction log >> > backups >> > reside on my backup device. How do I do that? Thank you in advance >> > >> > Leon >> > >> >> >>
- Next message: David Gugick: "Re: SQL version"
- Previous message: Mike Epprecht \(SQL MVP\): "Re: error '80020009' HELP!!!"
- In reply to: Leon Shargorodsky: "Re: Overwriting backups"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
