probleme with tara`s sproc isp_Backup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

probleme with tara`s sproc isp_Backup

Author

Topic

joanne
Starting Member

46 Posts

Posted - 2005-11-06 : 10:35:24

Hi,
I need your help!
I want to backup my system databases and to remove files older then 7 days.
When I run the script, it removes all files from my folders.
I want to know what is wrong with the script.
Can somebody help me, please?

This is the sproc that Tara has developed and that I have modified a little.

DECLARE @Path VARCHAR(100)
DECLARE @dbType VARCHAR(6)
DECLARE @Retention INT
set @Retention=7
set @Path='D:\SQLDATA\SQLDataDump\'
DECLARE @Now CHAR(8)
DECLARE @DBName SYSNAME
DECLARE @cmd SYSNAME
DECLARE @Result INT
DECLARE @RowCnt INT
DECLARE @filename VARCHAR(200)

CREATE TABLE #WhichDatabase
(
dbName SYSNAME NOT NULL
)

INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] IN ('master', msdb)
ORDER BY [name]

-- Get the database to be backed up
SELECT TOP 1 @DBName = dbName
FROM #WhichDatabase

SET @RowCnt = @@ROWCOUNT

-- Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 0
BEGIN

-- Get the current date using style 112, remove all dashes, spaces, and colons
SELECT @Now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 112), '-', ''), ' ', ''), ':', '')

-- Build the .BKP path and file name
SELECT @filename = @Path + @DBName + '\' + @DBName + '_' + @Now +'.BKP'

-- Build the dir command that will check to see if the directory exists
SELECT @cmd = 'dir ' + @Path + @DBName

-- Run the dir command, put output of xp_cmdshell into @result
EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT

-- If the directory does not exist, we must create it
IF @result <> 0
BEGIN

-- Build the mkdir command
SELECT @cmd = 'mkdir ' + @Path + @DBName

-- Create the directory
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

END
-- The directory exists, so let's delete files older than 7 days
ELSE
BEGIN

-- Stores the name of the file to be deleted
DECLARE @WhichFile VARCHAR(1000)

CREATE TABLE #DeleteOldFiles
(
DirInfo VARCHAR(7000)
)

-- Build the command that will list out all of the files in a directory
SELECT @cmd = 'dir ' + @Path + @DBName + ' /OD'

-- Run the dir command and put the results into a temp table
INSERT INTO #DeleteOldFiles
EXEC master.dbo.xp_cmdshell @cmd

-- Delete all rows from the temp table except the ones that correspond to the files to be deleted
DELETE
FROM #DeleteOldFiles
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%

%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @Retention

-- Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles

SET @RowCnt = @@ROWCOUNT

-- Interate through the temp table until there are no more files to delete
WHILE @RowCnt <> 0
BEGIN

-- Build the del command
SELECT @cmd = 'del ' + @Path + + @DBName + '\' + @WhichFile + ' /Q /F'

-- Delete the file
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

-- To move to the next file, the current file name needs to be deleted from the temp table
DELETE
FROM #DeleteOldFiles
WHERE SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) = @WhichFile

-- Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles

SET @RowCnt = @@ROWCOUNT

END

DROP TABLE #DeleteOldFiles

END

-- Backup the database
BACKUP DATABASE @DBName
TO DISK = @filename
WITH INIT

-- To move onto the next database, the current database name needs to be deleted from the temp table
DELETE
FROM #WhichDatabase
WHERE dbName = @DBName

-- Get the database to be backed up
SELECT TOP 1 @DBName = dbName
FROM #WhichDatabase

SET @RowCnt = @@ROWCOUNT

-- Let the system rest for 5 seconds before starting on the next backup
WAITFOR DELAY '00:00:05'

END

DROP TABLE #WhichDatabase
GO

There is the result of select * from #DeleteOldFiles

06/11/2005 10:22 AM <DIR> ..
06/11/2005 10:22 AM <DIR> .
06/11/2005 10:22 AM 15,554,048 master_20051106.BKP

Kristen
Test

22859 Posts

Posted - 2005-11-06 : 12:19:32

I expect Tara's servers are using America format for dates in DIR (i.e. mm/dd/yyyy) and you are using European format (dd/mm/yyyy)

You could try adding

SET DATEFORMAT DMY

before the date calculations so SQL bases its conversion on your European date formatting

Kristen

joanne
Starting Member

46 Posts

Posted - 2005-11-06 : 15:14:48

Hi Kristen,

Thanks for your help!
That work great.
Many thanks again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-07 : 01:34:26

It is better to use ISO format yyyymmdd to avoid conflict with local settings

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources