Hi:
I am using the cmdexec step in the SQL Server agent to rename a file. Below
is the command I use to rename the file in the step.
ren c:\test1.bak test2.bak
However when I run the job the job fails with the following error.
The process could not be created for step 1 of job
0xDD5759D5B7103041B7F0BD736B442D1D (reason: The system cannot find the file
specified). The step failed.
What is wrong with the command?. the command works perfectly from dos prompt
but refuses to work from the job. I also tried
ren "c:\test1.bak test2.bak" and this also fails. I dont want to use
xp_cmdshell and hence want to use the cmdexec command. Can anyone please let
me know what I am doing wrong and what is\are the correct commands when
executed from a SQL Server Agent job step?.
Thanks
MWhen you run it from the command prompt is it on your local machine or the
server? xp_cmdshell is always executed on the server not the client.
Andrew J. Kelly SQL MVP
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:eJriWN7SGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Hi:
> I am using the cmdexec step in the SQL Server agent to rename a file.
> Below is the command I use to rename the file in the step.
> ren c:\test1.bak test2.bak
> However when I run the job the job fails with the following error.
> The process could not be created for step 1 of job
> 0xDD5759D5B7103041B7F0BD736B442D1D (reason: The system cannot find the
> file specified). The step failed.
> What is wrong with the command?. the command works perfectly from dos
> prompt but refuses to work from the job. I also tried
> ren "c:\test1.bak test2.bak" and this also fails. I dont want to use
> xp_cmdshell and hence want to use the cmdexec command. Can anyone please
> let me know what I am doing wrong and what is\are the correct commands
> when executed from a SQL Server Agent job step?.
> Thanks
> M
>|||It is on my local machine which is running SQL Server 2000.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uTMlOI8SGHA.4264@.TK2MSFTNGP11.phx.gbl...
> When you run it from the command prompt is it on your local machine or the
> server? xp_cmdshell is always executed on the server not the client.
> --
> Andrew J. Kelly SQL MVP
>
> "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
> news:eJriWN7SGHA.5656@.TK2MSFTNGP11.phx.gbl...
>|||Andrew:
I figured it out. Thanks for making me think. Here is the correct command.
ren "c:\test20.bak" test1.bak
Thats why I adore SQL MVPs. They make SQL guys think logically.
Best Regards
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:eUC6IY8SGHA.4952@.TK2MSFTNGP09.phx.gbl...
> It is on my local machine which is running SQL Server 2000.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uTMlOI8SGHA.4264@.TK2MSFTNGP11.phx.gbl...
>|||The double quotes usually are not required unless you have spaces in the
path or file name. In either case I am glad you fixed it.
Andrew J. Kelly SQL MVP
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:eJTtLc8SGHA.736@.TK2MSFTNGP12.phx.gbl...
> Andrew:
> I figured it out. Thanks for making me think. Here is the correct command.
> ren "c:\test20.bak" test1.bak
> Thats why I adore SQL MVPs. They make SQL guys think logically.
> Best Regards
> "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
> news:eUC6IY8SGHA.4952@.TK2MSFTNGP09.phx.gbl...
>
Showing posts with label job. Show all posts
Showing posts with label job. Show all posts
Friday, March 23, 2012
Friday, March 9, 2012
Removing Older Backups?
With SQL Server 2000 I created a backup disk file (and job) for full
database backups performed daily (which are appended), and a backup disk
file (and job) for log backups performed every hour (also appended). Because
of disk space constraints (on a dedicated server), I would like to remove
older backups from each of these backup files. For example, instead of
having seven (or 180) days of full backups and seven (or 180) days of log
backups on my local disk (the server is backed up to tape nightly), I would
like to automatically keep only the last three days of full backups and only
the transaction logs that came after the first of the three full backups.
I have not discovered a way to automatically create backups as separate
file, each labeled and time-stamped that I could periodically delete, if
there is not a way to do the above with single appended files.
So, how does one extract the older backups out of these single files with
appended backups? Is there a way to configure that with EM, or does it
require SQL scripting?
Thanks for any help.Hi,
Take a look into the maintenance plan in Enterprise manager. Maintenance
plan allow you to archive the backup files
based on te frequency you provide.
Thanks
Hari
SQL Server MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:utNmj6WyGHA.2392@.TK2MSFTNGP03.phx.gbl...
> With SQL Server 2000 I created a backup disk file (and job) for full
> database backups performed daily (which are appended), and a backup disk
> file (and job) for log backups performed every hour (also appended).
> Because
> of disk space constraints (on a dedicated server), I would like to remove
> older backups from each of these backup files. For example, instead of
> having seven (or 180) days of full backups and seven (or 180) days of log
> backups on my local disk (the server is backed up to tape nightly), I
> would
> like to automatically keep only the last three days of full backups and
> only
> the transaction logs that came after the first of the three full backups.
> I have not discovered a way to automatically create backups as separate
> file, each labeled and time-stamped that I could periodically delete, if
> there is not a way to do the above with single appended files.
> So, how does one extract the older backups out of these single files with
> appended backups? Is there a way to configure that with EM, or does it
> require SQL scripting?
> Thanks for any help.
>|||Don,
You can achieve this with the Maintenance Plan wizard although it has some
limitations and not very flexible. If you want to create your own jobs you
can use this sample as a start. It shows how to generate a new file name for
each backup and a sample on how to remove older backup files as well.
-- 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
"Don Miller" <nospam@.nospam.com> wrote in message
news:utNmj6WyGHA.2392@.TK2MSFTNGP03.phx.gbl...
> With SQL Server 2000 I created a backup disk file (and job) for full
> database backups performed daily (which are appended), and a backup disk
> file (and job) for log backups performed every hour (also appended).
> Because
> of disk space constraints (on a dedicated server), I would like to remove
> older backups from each of these backup files. For example, instead of
> having seven (or 180) days of full backups and seven (or 180) days of log
> backups on my local disk (the server is backed up to tape nightly), I
> would
> like to automatically keep only the last three days of full backups and
> only
> the transaction logs that came after the first of the three full backups.
> I have not discovered a way to automatically create backups as separate
> file, each labeled and time-stamped that I could periodically delete, if
> there is not a way to do the above with single appended files.
> So, how does one extract the older backups out of these single files with
> appended backups? Is there a way to configure that with EM, or does it
> require SQL scripting?
> Thanks for any help.
>|||Thanks for the script. I'll take a close look at it.
I did experiment with the Maintenance Plan wizard and instead of appending
the backups and logs to the same file, it creates those separate
time-stamped files. I'll probably go with this because I can more easily
deal (and understand) with many time-stamped files rather than one file with
the logs and such enclosed in some proprietary manner.
I'm also looking at SQL Sentry as you suggested. Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OSEqsLiyGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Don,
> You can achieve this with the Maintenance Plan wizard although it has some
> limitations and not very flexible. If you want to create your own jobs you
> can use this sample as a start. It shows how to generate a new file name
for
> each backup and a sample on how to remove older backup files as well.
>
> -- 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
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:utNmj6WyGHA.2392@.TK2MSFTNGP03.phx.gbl...
> > With SQL Server 2000 I created a backup disk file (and job) for full
> > database backups performed daily (which are appended), and a backup disk
> > file (and job) for log backups performed every hour (also appended).
> > Because
> > of disk space constraints (on a dedicated server), I would like to
remove
> > older backups from each of these backup files. For example, instead of
> > having seven (or 180) days of full backups and seven (or 180) days of
log
> > backups on my local disk (the server is backed up to tape nightly), I
> > would
> > like to automatically keep only the last three days of full backups and
> > only
> > the transaction logs that came after the first of the three full
backups.
> >
> > I have not discovered a way to automatically create backups as separate
> > file, each labeled and time-stamped that I could periodically delete, if
> > there is not a way to do the above with single appended files.
> >
> > So, how does one extract the older backups out of these single files
with
> > appended backups? Is there a way to configure that with EM, or does it
> > require SQL scripting?
> >
> > Thanks for any help.
> >
> >
>|||Don Miller wrote:
> With SQL Server 2000 I created a backup disk file (and job) for full
> database backups performed daily (which are appended), and a backup disk
> file (and job) for log backups performed every hour (also appended). Because
> of disk space constraints (on a dedicated server), I would like to remove
> older backups from each of these backup files. For example, instead of
> having seven (or 180) days of full backups and seven (or 180) days of log
> backups on my local disk (the server is backed up to tape nightly), I would
> like to automatically keep only the last three days of full backups and only
> the transaction logs that came after the first of the three full backups.
> I have not discovered a way to automatically create backups as separate
> file, each labeled and time-stamped that I could periodically delete, if
> there is not a way to do the above with single appended files.
> So, how does one extract the older backups out of these single files with
> appended backups? Is there a way to configure that with EM, or does it
> require SQL scripting?
> Thanks for any help.
>
Have a look at this script:
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
database backups performed daily (which are appended), and a backup disk
file (and job) for log backups performed every hour (also appended). Because
of disk space constraints (on a dedicated server), I would like to remove
older backups from each of these backup files. For example, instead of
having seven (or 180) days of full backups and seven (or 180) days of log
backups on my local disk (the server is backed up to tape nightly), I would
like to automatically keep only the last three days of full backups and only
the transaction logs that came after the first of the three full backups.
I have not discovered a way to automatically create backups as separate
file, each labeled and time-stamped that I could periodically delete, if
there is not a way to do the above with single appended files.
So, how does one extract the older backups out of these single files with
appended backups? Is there a way to configure that with EM, or does it
require SQL scripting?
Thanks for any help.Hi,
Take a look into the maintenance plan in Enterprise manager. Maintenance
plan allow you to archive the backup files
based on te frequency you provide.
Thanks
Hari
SQL Server MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:utNmj6WyGHA.2392@.TK2MSFTNGP03.phx.gbl...
> With SQL Server 2000 I created a backup disk file (and job) for full
> database backups performed daily (which are appended), and a backup disk
> file (and job) for log backups performed every hour (also appended).
> Because
> of disk space constraints (on a dedicated server), I would like to remove
> older backups from each of these backup files. For example, instead of
> having seven (or 180) days of full backups and seven (or 180) days of log
> backups on my local disk (the server is backed up to tape nightly), I
> would
> like to automatically keep only the last three days of full backups and
> only
> the transaction logs that came after the first of the three full backups.
> I have not discovered a way to automatically create backups as separate
> file, each labeled and time-stamped that I could periodically delete, if
> there is not a way to do the above with single appended files.
> So, how does one extract the older backups out of these single files with
> appended backups? Is there a way to configure that with EM, or does it
> require SQL scripting?
> Thanks for any help.
>|||Don,
You can achieve this with the Maintenance Plan wizard although it has some
limitations and not very flexible. If you want to create your own jobs you
can use this sample as a start. It shows how to generate a new file name for
each backup and a sample on how to remove older backup files as well.
-- 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
"Don Miller" <nospam@.nospam.com> wrote in message
news:utNmj6WyGHA.2392@.TK2MSFTNGP03.phx.gbl...
> With SQL Server 2000 I created a backup disk file (and job) for full
> database backups performed daily (which are appended), and a backup disk
> file (and job) for log backups performed every hour (also appended).
> Because
> of disk space constraints (on a dedicated server), I would like to remove
> older backups from each of these backup files. For example, instead of
> having seven (or 180) days of full backups and seven (or 180) days of log
> backups on my local disk (the server is backed up to tape nightly), I
> would
> like to automatically keep only the last three days of full backups and
> only
> the transaction logs that came after the first of the three full backups.
> I have not discovered a way to automatically create backups as separate
> file, each labeled and time-stamped that I could periodically delete, if
> there is not a way to do the above with single appended files.
> So, how does one extract the older backups out of these single files with
> appended backups? Is there a way to configure that with EM, or does it
> require SQL scripting?
> Thanks for any help.
>|||Thanks for the script. I'll take a close look at it.
I did experiment with the Maintenance Plan wizard and instead of appending
the backups and logs to the same file, it creates those separate
time-stamped files. I'll probably go with this because I can more easily
deal (and understand) with many time-stamped files rather than one file with
the logs and such enclosed in some proprietary manner.
I'm also looking at SQL Sentry as you suggested. Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OSEqsLiyGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Don,
> You can achieve this with the Maintenance Plan wizard although it has some
> limitations and not very flexible. If you want to create your own jobs you
> can use this sample as a start. It shows how to generate a new file name
for
> each backup and a sample on how to remove older backup files as well.
>
> -- 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
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:utNmj6WyGHA.2392@.TK2MSFTNGP03.phx.gbl...
> > With SQL Server 2000 I created a backup disk file (and job) for full
> > database backups performed daily (which are appended), and a backup disk
> > file (and job) for log backups performed every hour (also appended).
> > Because
> > of disk space constraints (on a dedicated server), I would like to
remove
> > older backups from each of these backup files. For example, instead of
> > having seven (or 180) days of full backups and seven (or 180) days of
log
> > backups on my local disk (the server is backed up to tape nightly), I
> > would
> > like to automatically keep only the last three days of full backups and
> > only
> > the transaction logs that came after the first of the three full
backups.
> >
> > I have not discovered a way to automatically create backups as separate
> > file, each labeled and time-stamped that I could periodically delete, if
> > there is not a way to do the above with single appended files.
> >
> > So, how does one extract the older backups out of these single files
with
> > appended backups? Is there a way to configure that with EM, or does it
> > require SQL scripting?
> >
> > Thanks for any help.
> >
> >
>|||Don Miller wrote:
> With SQL Server 2000 I created a backup disk file (and job) for full
> database backups performed daily (which are appended), and a backup disk
> file (and job) for log backups performed every hour (also appended). Because
> of disk space constraints (on a dedicated server), I would like to remove
> older backups from each of these backup files. For example, instead of
> having seven (or 180) days of full backups and seven (or 180) days of log
> backups on my local disk (the server is backed up to tape nightly), I would
> like to automatically keep only the last three days of full backups and only
> the transaction logs that came after the first of the three full backups.
> I have not discovered a way to automatically create backups as separate
> file, each labeled and time-stamped that I could periodically delete, if
> there is not a way to do the above with single appended files.
> So, how does one extract the older backups out of these single files with
> appended backups? Is there a way to configure that with EM, or does it
> require SQL scripting?
> Thanks for any help.
>
Have a look at this script:
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Removing Older Backups?
With SQL Server 2000 I created a backup disk file (and job) for full
database backups performed daily (which are appended), and a backup disk
file (and job) for log backups performed every hour (also appended). Because
of disk space constraints (on a dedicated server), I would like to remove
older backups from each of these backup files. For example, instead of
having seven (or 180) days of full backups and seven (or 180) days of log
backups on my local disk (the server is backed up to tape nightly), I would
like to automatically keep only the last three days of full backups and only
the transaction logs that came after the first of the three full backups.
I have not discovered a way to automatically create backups as separate
file, each labeled and time-stamped that I could periodically delete, if
there is not a way to do the above with single appended files.
So, how does one extract the older backups out of these single files with
appended backups? Is there a way to configure that with EM, or does it
require SQL scripting?
Thanks for any help.Hi,
Take a look into the maintenance plan in Enterprise manager. Maintenance
plan allow you to archive the backup files
based on te frequency you provide.
Thanks
Hari
SQL Server MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:utNmj6WyGHA.2392@.TK2MSFTNGP03.phx.gbl...
> With SQL Server 2000 I created a backup disk file (and job) for full
> database backups performed daily (which are appended), and a backup disk
> file (and job) for log backups performed every hour (also appended).
> Because
> of disk space constraints (on a dedicated server), I would like to remove
> older backups from each of these backup files. For example, instead of
> having seven (or 180) days of full backups and seven (or 180) days of log
> backups on my local disk (the server is backed up to tape nightly), I
> would
> like to automatically keep only the last three days of full backups and
> only
> the transaction logs that came after the first of the three full backups.
> I have not discovered a way to automatically create backups as separate
> file, each labeled and time-stamped that I could periodically delete, if
> there is not a way to do the above with single appended files.
> So, how does one extract the older backups out of these single files with
> appended backups? Is there a way to configure that with EM, or does it
> require SQL scripting?
> Thanks for any help.
>|||Don,
You can achieve this with the Maintenance Plan wizard although it has some
limitations and not very flexible. If you want to create your own jobs you
can use this sample as a start. It shows how to generate a new file name for
each backup and a sample on how to remove older backup files as well.
-- 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
"Don Miller" <nospam@.nospam.com> wrote in message
news:utNmj6WyGHA.2392@.TK2MSFTNGP03.phx.gbl...
> With SQL Server 2000 I created a backup disk file (and job) for full
> database backups performed daily (which are appended), and a backup disk
> file (and job) for log backups performed every hour (also appended).
> Because
> of disk space constraints (on a dedicated server), I would like to remove
> older backups from each of these backup files. For example, instead of
> having seven (or 180) days of full backups and seven (or 180) days of log
> backups on my local disk (the server is backed up to tape nightly), I
> would
> like to automatically keep only the last three days of full backups and
> only
> the transaction logs that came after the first of the three full backups.
> I have not discovered a way to automatically create backups as separate
> file, each labeled and time-stamped that I could periodically delete, if
> there is not a way to do the above with single appended files.
> So, how does one extract the older backups out of these single files with
> appended backups? Is there a way to configure that with EM, or does it
> require SQL scripting?
> Thanks for any help.
>|||Thanks for the script. I'll take a close look at it.
I did experiment with the Maintenance Plan wizard and instead of appending
the backups and logs to the same file, it creates those separate
time-stamped files. I'll probably go with this because I can more easily
deal (and understand) with many time-stamped files rather than one file with
the logs and such enclosed in some proprietary manner.
I'm also looking at SQL Sentry as you suggested. Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OSEqsLiyGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Don,
> You can achieve this with the Maintenance Plan wizard although it has some
> limitations and not very flexible. If you want to create your own jobs you
> can use this sample as a start. It shows how to generate a new file name
for
> each backup and a sample on how to remove older backup files as well.
>
> -- 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] VARC
HAR(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
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:utNmj6WyGHA.2392@.TK2MSFTNGP03.phx.gbl...
remove[vbcol=seagreen]
log[vbcol=seagreen]
backups.[vbcol=seagreen]
with[vbcol=seagreen]
>|||Don Miller wrote:
> With SQL Server 2000 I created a backup disk file (and job) for full
> database backups performed daily (which are appended), and a backup disk
> file (and job) for log backups performed every hour (also appended). Becau
se
> of disk space constraints (on a dedicated server), I would like to remove
> older backups from each of these backup files. For example, instead of
> having seven (or 180) days of full backups and seven (or 180) days of log
> backups on my local disk (the server is backed up to tape nightly), I woul
d
> like to automatically keep only the last three days of full backups and on
ly
> the transaction logs that came after the first of the three full backups.
> I have not discovered a way to automatically create backups as separate
> file, each labeled and time-stamped that I could periodically delete, if
> there is not a way to do the above with single appended files.
> So, how does one extract the older backups out of these single files with
> appended backups? Is there a way to configure that with EM, or does it
> require SQL scripting?
> Thanks for any help.
>
Have a look at this script:
http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
database backups performed daily (which are appended), and a backup disk
file (and job) for log backups performed every hour (also appended). Because
of disk space constraints (on a dedicated server), I would like to remove
older backups from each of these backup files. For example, instead of
having seven (or 180) days of full backups and seven (or 180) days of log
backups on my local disk (the server is backed up to tape nightly), I would
like to automatically keep only the last three days of full backups and only
the transaction logs that came after the first of the three full backups.
I have not discovered a way to automatically create backups as separate
file, each labeled and time-stamped that I could periodically delete, if
there is not a way to do the above with single appended files.
So, how does one extract the older backups out of these single files with
appended backups? Is there a way to configure that with EM, or does it
require SQL scripting?
Thanks for any help.Hi,
Take a look into the maintenance plan in Enterprise manager. Maintenance
plan allow you to archive the backup files
based on te frequency you provide.
Thanks
Hari
SQL Server MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:utNmj6WyGHA.2392@.TK2MSFTNGP03.phx.gbl...
> With SQL Server 2000 I created a backup disk file (and job) for full
> database backups performed daily (which are appended), and a backup disk
> file (and job) for log backups performed every hour (also appended).
> Because
> of disk space constraints (on a dedicated server), I would like to remove
> older backups from each of these backup files. For example, instead of
> having seven (or 180) days of full backups and seven (or 180) days of log
> backups on my local disk (the server is backed up to tape nightly), I
> would
> like to automatically keep only the last three days of full backups and
> only
> the transaction logs that came after the first of the three full backups.
> I have not discovered a way to automatically create backups as separate
> file, each labeled and time-stamped that I could periodically delete, if
> there is not a way to do the above with single appended files.
> So, how does one extract the older backups out of these single files with
> appended backups? Is there a way to configure that with EM, or does it
> require SQL scripting?
> Thanks for any help.
>|||Don,
You can achieve this with the Maintenance Plan wizard although it has some
limitations and not very flexible. If you want to create your own jobs you
can use this sample as a start. It shows how to generate a new file name for
each backup and a sample on how to remove older backup files as well.
-- 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
"Don Miller" <nospam@.nospam.com> wrote in message
news:utNmj6WyGHA.2392@.TK2MSFTNGP03.phx.gbl...
> With SQL Server 2000 I created a backup disk file (and job) for full
> database backups performed daily (which are appended), and a backup disk
> file (and job) for log backups performed every hour (also appended).
> Because
> of disk space constraints (on a dedicated server), I would like to remove
> older backups from each of these backup files. For example, instead of
> having seven (or 180) days of full backups and seven (or 180) days of log
> backups on my local disk (the server is backed up to tape nightly), I
> would
> like to automatically keep only the last three days of full backups and
> only
> the transaction logs that came after the first of the three full backups.
> I have not discovered a way to automatically create backups as separate
> file, each labeled and time-stamped that I could periodically delete, if
> there is not a way to do the above with single appended files.
> So, how does one extract the older backups out of these single files with
> appended backups? Is there a way to configure that with EM, or does it
> require SQL scripting?
> Thanks for any help.
>|||Thanks for the script. I'll take a close look at it.
I did experiment with the Maintenance Plan wizard and instead of appending
the backups and logs to the same file, it creates those separate
time-stamped files. I'll probably go with this because I can more easily
deal (and understand) with many time-stamped files rather than one file with
the logs and such enclosed in some proprietary manner.
I'm also looking at SQL Sentry as you suggested. Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OSEqsLiyGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Don,
> You can achieve this with the Maintenance Plan wizard although it has some
> limitations and not very flexible. If you want to create your own jobs you
> can use this sample as a start. It shows how to generate a new file name
for
> each backup and a sample on how to remove older backup files as well.
>
> -- 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] VARC
HAR(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
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:utNmj6WyGHA.2392@.TK2MSFTNGP03.phx.gbl...
remove[vbcol=seagreen]
log[vbcol=seagreen]
backups.[vbcol=seagreen]
with[vbcol=seagreen]
>|||Don Miller wrote:
> With SQL Server 2000 I created a backup disk file (and job) for full
> database backups performed daily (which are appended), and a backup disk
> file (and job) for log backups performed every hour (also appended). Becau
se
> of disk space constraints (on a dedicated server), I would like to remove
> older backups from each of these backup files. For example, instead of
> having seven (or 180) days of full backups and seven (or 180) days of log
> backups on my local disk (the server is backed up to tape nightly), I woul
d
> like to automatically keep only the last three days of full backups and on
ly
> the transaction logs that came after the first of the three full backups.
> I have not discovered a way to automatically create backups as separate
> file, each labeled and time-stamped that I could periodically delete, if
> there is not a way to do the above with single appended files.
> So, how does one extract the older backups out of these single files with
> appended backups? Is there a way to configure that with EM, or does it
> require SQL scripting?
> Thanks for any help.
>
Have a look at this script:
http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Subscribe to:
Posts (Atom)