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:\MVP\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
-----------------------------------------------
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 D:\MVP\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 "D:\MVP\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
