sql overflow error: overwriting

Showing posts with label overwriting. Show all posts

Wednesday, March 7, 2012

Overwriting the backup media

Hi,
Is there a command to overwrite the backup mdeia (I tried the NOSKIP
option but that doesn't serve the purpose). I want the command equivalent to the checkbox in the backup window that says "Overwrite existing media".
Thanks.don't know in which languagebut if you're using sqLDMO
in VB it's like this...

dim oBackup as new SQLDMO.Backup

oBackup.initialize = true

(This will put the backupdevice to 'overwrite')|||To over write using the BACKUP command, you need to specify 'SKIP, INIT' If you want to oblitirate the backup AND any other backupsets written across the tape (stripped set) then use FORMAT. Let us know if you are still having problems.|||Hi Paul,

That seems to work.
Thanks a lot.

Overwriting text control

Hello,
Is it possible to overwrite a text control in reporting services, so
that I can add two additional properties to it? If possible, do you
have any links/point to resources?
Thanks
/Peterdo you talk about a parameter related textbox?
yes you can... if you develop your own report access interface or if you use
the reportviewer control.
directly in reportserver, you can't
from where you want to add properties in the textbox?
do you want to open a report with some parameters predefined and send in the
URL?
"Peter" <ptrlsn@.gmail.com> wrote in message
news:1178650584.307210.221870@.p77g2000hsh.googlegroups.com...
> Hello,
> Is it possible to overwrite a text control in reporting services, so
> that I can add two additional properties to it? If possible, do you
> have any links/point to resources?
>
> Thanks
> /Peter
>|||On 9 Maj, 04:44, "Jeje" <willg...@.hotmail.com> wrote:
> do you talk about a parameter related textbox?
> yes you can... if you develop your own report access interface or if you use
> the reportviewer control.
> directly in reportserver, you can't
> from where you want to add properties in the textbox?
> do you want to open a report with some parameters predefined and send in the
> URL?
Thanks for the reply
The text in the text box should be retrieved from an external
application based on a property-value that it will pass to said
external application. It will connect to the external app through a
COM connector.
Is this possible programmatically? For example by overwriting the text
control and give it one or two extra properties?
/Peter

Overwriting entry in table

Task:

To insert entries into a table. The table has a primary key based on a field 'ID'. When inserting into the destination table, I want to make sure that the new entry will overwrite the old entry.

What's the quickest/cleaniest way to do this ?

thanks,
ClaytonUPDATE <table>
SET <column>=<value>, etc...
WHERE field_id = <field_id>

Overwriting backups

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
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
>
|||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...
>
>
|||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...[vbcol=seagreen]
> 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:

Overwriting backups

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
Hi Leon,
If you are using DB Maintenance plan, in the transaction log backup section,
"Remove file older than" provides the functionality you are looking for.
And if you are using, T-SQL you can specify "RETAINDAYS" parameter.
Check out for BACKUP in BOL.
Thanks
Yogish
"Leon Shargorodsky" wrote:

> 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
|||Thanks for your reply, Yogish, but this is not what I'm asking for.
The key-word here is BACKUP DEVICE, Maintenance Plan does not have option to
backup to a DEVICE.
"Yogish" wrote:
[vbcol=seagreen]
> Hi Leon,
> If you are using DB Maintenance plan, in the transaction log backup section,
> "Remove file older than" provides the functionality you are looking for.
> And if you are using, T-SQL you can specify "RETAINDAYS" parameter.
> Check out for BACKUP in BOL.
> --
> Thanks
> Yogish
> "Leon Shargorodsky" wrote:

Overwriting backups

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
LeonYou 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
>|||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
> >
>
>|||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
>> >
>>

Overwriting backups

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
LeonHi Leon,
If you are using DB Maintenance plan, in the transaction log backup section,
"Remove file older than" provides the functionality you are looking for.
And if you are using, T-SQL you can specify "RETAINDAYS" parameter.
Check out for BACKUP in BOL.
--
Thanks
Yogish
"Leon Shargorodsky" wrote:
> 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|||Thanks for your reply, Yogish, but this is not what I'm asking for.
The key-word here is BACKUP DEVICE, Maintenance Plan does not have option to
backup to a DEVICE.
"Yogish" wrote:
> Hi Leon,
> If you are using DB Maintenance plan, in the transaction log backup section,
> "Remove file older than" provides the functionality you are looking for.
> And if you are using, T-SQL you can specify "RETAINDAYS" parameter.
> Check out for BACKUP in BOL.
> --
> Thanks
> Yogish
> "Leon Shargorodsky" wrote:
> > 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

Overwriting backups

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
LeonYou 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
>|||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 you
r
> only options being to use INIT or NOINIT. INIT will remove ALL files in t
he
> 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 wh
at
> 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...
>
>|||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...[vbcol=seagreen]
> 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:
>

Overwriting backups

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
LeonHi Leon,
If you are using DB Maintenance plan, in the transaction log backup section,
"Remove file older than" provides the functionality you are looking for.
And if you are using, T-SQL you can specify "RETAINDAYS" parameter.
Check out for BACKUP in BOL.
Thanks
Yogish
"Leon Shargorodsky" wrote:

> I'm backing up my transaction log on backup device every 15 minutes. I wan
t
> 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|||Thanks for your reply, Yogish, but this is not what I'm asking for.
The key-word here is BACKUP DEVICE, Maintenance Plan does not have option to
backup to a DEVICE.
"Yogish" wrote:
[vbcol=seagreen]
> Hi Leon,
> If you are using DB Maintenance plan, in the transaction log backup sectio
n,
> "Remove file older than" provides the functionality you are looking for.
> And if you are using, T-SQL you can specify "RETAINDAYS" parameter.
> Check out for BACKUP in BOL.
> --
> Thanks
> Yogish
> "Leon Shargorodsky" wrote:
>

Overwriting Backup Set

Hi,
I did a full backup of my database, but everyday when it is backing up I
would like it to overwrite the previous day's backup.
I'm doing it thru Transact-SQL script(T-SQL) so i am entering the following
command:
"EXEC sp_addumpdevice 'disk', 'backup_file6', 'C:\test' backup database
WSS_Content to backup_file6 with init"
Is this right? Or do I need to include something else?From BOL:
INIT Specifies that all backup sets should be overwritten, but preserves
the media header. If INIT is specified, any existing backup set on that
device is overwritten, if conditions permit. By default, BACKUP checks for
the following conditions and does not overwrite the backup media if either
condition exists:
a.. Any backup set has not yet expired. For more information, see the
EXPIREDATE and RETAINDAYS options.
b.. The backup set name given in the BACKUP statement, if provided, does
not match the name on the backup media. For more information, see the NAME
option, earlier in this section.
To override these checks, use the SKIP option.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Shelley" <Shelley@.discussions.microsoft.com> wrote in message
news:AFAA262A-FF58-4ABB-A2AC-A6803A88C87A@.microsoft.com...
> Hi,
> I did a full backup of my database, but everyday when it is backing up I
> would like it to overwrite the previous day's backup.
> I'm doing it thru Transact-SQL script(T-SQL) so i am entering the
> following
> command:
> "EXEC sp_addumpdevice 'disk', 'backup_file6', 'C:\test' backup database
> WSS_Content to backup_file6 with init"
> Is this right? Or do I need to include something else?|||Hi, thanks for the response........
Where do I need to go to see the EXPIREDATE and RETAINDAYS options?
Also, I use the SKIP option with "with skip, init"?
"TheSQLGuru" wrote:
> From BOL:
> INIT Specifies that all backup sets should be overwritten, but preserves
> the media header. If INIT is specified, any existing backup set on that
> device is overwritten, if conditions permit. By default, BACKUP checks for
> the following conditions and does not overwrite the backup media if either
> condition exists:
> a.. Any backup set has not yet expired. For more information, see the
> EXPIREDATE and RETAINDAYS options.
>
> b.. The backup set name given in the BACKUP statement, if provided, does
> not match the name on the backup media. For more information, see the NAME
> option, earlier in this section.
> To override these checks, use the SKIP option.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
> news:AFAA262A-FF58-4ABB-A2AC-A6803A88C87A@.microsoft.com...
> > Hi,
> > I did a full backup of my database, but everyday when it is backing up I
> > would like it to overwrite the previous day's backup.
> > I'm doing it thru Transact-SQL script(T-SQL) so i am entering the
> > following
> > command:
> > "EXEC sp_addumpdevice 'disk', 'backup_file6', 'C:\test' backup database
> > WSS_Content to backup_file6 with init"
> >
> > Is this right? Or do I need to include something else?
>
>|||See Backup Database in Books Online. Everything is explained nicely there.
To answer your question here yes, WITH options are simply comma separated.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Shelley" <Shelley@.discussions.microsoft.com> wrote in message
news:73A564B3-E7E1-4EB8-BE86-5660091A7A97@.microsoft.com...
> Hi, thanks for the response........
> Where do I need to go to see the EXPIREDATE and RETAINDAYS options?
> Also, I use the SKIP option with "with skip, init"?
> "TheSQLGuru" wrote:
>> From BOL:
>> INIT Specifies that all backup sets should be overwritten, but preserves
>> the media header. If INIT is specified, any existing backup set on that
>> device is overwritten, if conditions permit. By default, BACKUP checks
>> for
>> the following conditions and does not overwrite the backup media if
>> either
>> condition exists:
>> a.. Any backup set has not yet expired. For more information, see the
>> EXPIREDATE and RETAINDAYS options.
>>
>> b.. The backup set name given in the BACKUP statement, if provided,
>> does
>> not match the name on the backup media. For more information, see the
>> NAME
>> option, earlier in this section.
>> To override these checks, use the SKIP option.
>>
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
>> news:AFAA262A-FF58-4ABB-A2AC-A6803A88C87A@.microsoft.com...
>> > Hi,
>> > I did a full backup of my database, but everyday when it is backing up
>> > I
>> > would like it to overwrite the previous day's backup.
>> > I'm doing it thru Transact-SQL script(T-SQL) so i am entering the
>> > following
>> > command:
>> > "EXEC sp_addumpdevice 'disk', 'backup_file6', 'C:\test' backup database
>> > WSS_Content to backup_file6 with init"
>> >
>> > Is this right? Or do I need to include something else?
>>|||Hi,
I'm trying to do an automated backup where it overwrites the previous
backup. I'm running the command:
"EXEC sp_addumpdevice 'disk', 'backup_file9', 'C:\Friday' backup database
WSS_Content to backup_file9
with skip, init"
But when I go to View Job History, I'm getting the error:
"The add device request was denied. A physical device named 'C:\Friday'
already exists. Only one backup device may refer to any physical device
name.[SQLSTATE 42000](Error 15061). The step failed."
Why exactly is that? What needs to be done?
Please help.......
"TheSQLGuru" wrote:
> See Backup Database in Books Online. Everything is explained nicely there.
> To answer your question here yes, WITH options are simply comma separated.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
> news:73A564B3-E7E1-4EB8-BE86-5660091A7A97@.microsoft.com...
> > Hi, thanks for the response........
> > Where do I need to go to see the EXPIREDATE and RETAINDAYS options?
> > Also, I use the SKIP option with "with skip, init"?
> >
> > "TheSQLGuru" wrote:
> >
> >> From BOL:
> >>
> >> INIT Specifies that all backup sets should be overwritten, but preserves
> >> the media header. If INIT is specified, any existing backup set on that
> >> device is overwritten, if conditions permit. By default, BACKUP checks
> >> for
> >> the following conditions and does not overwrite the backup media if
> >> either
> >> condition exists:
> >> a.. Any backup set has not yet expired. For more information, see the
> >> EXPIREDATE and RETAINDAYS options.
> >>
> >>
> >> b.. The backup set name given in the BACKUP statement, if provided,
> >> does
> >> not match the name on the backup media. For more information, see the
> >> NAME
> >> option, earlier in this section.
> >>
> >> To override these checks, use the SKIP option.
> >>
> >>
> >> --
> >> Kevin G. Boles
> >> TheSQLGuru
> >> Indicium Resources, Inc.
> >>
> >>
> >> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
> >> news:AFAA262A-FF58-4ABB-A2AC-A6803A88C87A@.microsoft.com...
> >> > Hi,
> >> > I did a full backup of my database, but everyday when it is backing up
> >> > I
> >> > would like it to overwrite the previous day's backup.
> >> > I'm doing it thru Transact-SQL script(T-SQL) so i am entering the
> >> > following
> >> > command:
> >> > "EXEC sp_addumpdevice 'disk', 'backup_file6', 'C:\test' backup database
> >> > WSS_Content to backup_file6 with init"
> >> >
> >> > Is this right? Or do I need to include something else?
> >>
> >>
> >>
>
>|||sp_addumpdevice creates a backupdevice, which you later use in your backup command. So, you only
need to do this once.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Shelley" <Shelley@.discussions.microsoft.com> wrote in message
news:2808B4FF-8B74-4596-BDF2-A574E6FDA2D4@.microsoft.com...
> Hi,
> I'm trying to do an automated backup where it overwrites the previous
> backup. I'm running the command:
> "EXEC sp_addumpdevice 'disk', 'backup_file9', 'C:\Friday' backup database
> WSS_Content to backup_file9
> with skip, init"
> But when I go to View Job History, I'm getting the error:
> "The add device request was denied. A physical device named 'C:\Friday'
> already exists. Only one backup device may refer to any physical device
> name.[SQLSTATE 42000](Error 15061). The step failed."
> Why exactly is that? What needs to be done?
> Please help.......
>
> "TheSQLGuru" wrote:
>> See Backup Database in Books Online. Everything is explained nicely there.
>> To answer your question here yes, WITH options are simply comma separated.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
>> news:73A564B3-E7E1-4EB8-BE86-5660091A7A97@.microsoft.com...
>> > Hi, thanks for the response........
>> > Where do I need to go to see the EXPIREDATE and RETAINDAYS options?
>> > Also, I use the SKIP option with "with skip, init"?
>> >
>> > "TheSQLGuru" wrote:
>> >
>> >> From BOL:
>> >>
>> >> INIT Specifies that all backup sets should be overwritten, but preserves
>> >> the media header. If INIT is specified, any existing backup set on that
>> >> device is overwritten, if conditions permit. By default, BACKUP checks
>> >> for
>> >> the following conditions and does not overwrite the backup media if
>> >> either
>> >> condition exists:
>> >> a.. Any backup set has not yet expired. For more information, see the
>> >> EXPIREDATE and RETAINDAYS options.
>> >>
>> >>
>> >> b.. The backup set name given in the BACKUP statement, if provided,
>> >> does
>> >> not match the name on the backup media. For more information, see the
>> >> NAME
>> >> option, earlier in this section.
>> >>
>> >> To override these checks, use the SKIP option.
>> >>
>> >>
>> >> --
>> >> Kevin G. Boles
>> >> TheSQLGuru
>> >> Indicium Resources, Inc.
>> >>
>> >>
>> >> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
>> >> news:AFAA262A-FF58-4ABB-A2AC-A6803A88C87A@.microsoft.com...
>> >> > Hi,
>> >> > I did a full backup of my database, but everyday when it is backing up
>> >> > I
>> >> > would like it to overwrite the previous day's backup.
>> >> > I'm doing it thru Transact-SQL script(T-SQL) so i am entering the
>> >> > following
>> >> > command:
>> >> > "EXEC sp_addumpdevice 'disk', 'backup_file6', 'C:\test' backup database
>> >> > WSS_Content to backup_file6 with init"
>> >> >
>> >> > Is this right? Or do I need to include something else?
>> >>
>> >>
>> >>
>>|||Hi, thanks for your response......
What command exactly do I need to use -
"EXEC backup database WSS_Content to backup_file9
with skip, init" ?
"Tibor Karaszi" wrote:
> sp_addumpdevice creates a backupdevice, which you later use in your backup command. So, you only
> need to do this once.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
> news:2808B4FF-8B74-4596-BDF2-A574E6FDA2D4@.microsoft.com...
> > Hi,
> > I'm trying to do an automated backup where it overwrites the previous
> > backup. I'm running the command:
> > "EXEC sp_addumpdevice 'disk', 'backup_file9', 'C:\Friday' backup database
> > WSS_Content to backup_file9
> > with skip, init"
> >
> > But when I go to View Job History, I'm getting the error:
> > "The add device request was denied. A physical device named 'C:\Friday'
> > already exists. Only one backup device may refer to any physical device
> > name.[SQLSTATE 42000](Error 15061). The step failed."
> >
> > Why exactly is that? What needs to be done?
> >
> > Please help.......
> >
> >
> > "TheSQLGuru" wrote:
> >
> >> See Backup Database in Books Online. Everything is explained nicely there.
> >> To answer your question here yes, WITH options are simply comma separated.
> >>
> >> --
> >> Kevin G. Boles
> >> TheSQLGuru
> >> Indicium Resources, Inc.
> >>
> >>
> >> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
> >> news:73A564B3-E7E1-4EB8-BE86-5660091A7A97@.microsoft.com...
> >> > Hi, thanks for the response........
> >> > Where do I need to go to see the EXPIREDATE and RETAINDAYS options?
> >> > Also, I use the SKIP option with "with skip, init"?
> >> >
> >> > "TheSQLGuru" wrote:
> >> >
> >> >> From BOL:
> >> >>
> >> >> INIT Specifies that all backup sets should be overwritten, but preserves
> >> >> the media header. If INIT is specified, any existing backup set on that
> >> >> device is overwritten, if conditions permit. By default, BACKUP checks
> >> >> for
> >> >> the following conditions and does not overwrite the backup media if
> >> >> either
> >> >> condition exists:
> >> >> a.. Any backup set has not yet expired. For more information, see the
> >> >> EXPIREDATE and RETAINDAYS options.
> >> >>
> >> >>
> >> >> b.. The backup set name given in the BACKUP statement, if provided,
> >> >> does
> >> >> not match the name on the backup media. For more information, see the
> >> >> NAME
> >> >> option, earlier in this section.
> >> >>
> >> >> To override these checks, use the SKIP option.
> >> >>
> >> >>
> >> >> --
> >> >> Kevin G. Boles
> >> >> TheSQLGuru
> >> >> Indicium Resources, Inc.
> >> >>
> >> >>
> >> >> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
> >> >> news:AFAA262A-FF58-4ABB-A2AC-A6803A88C87A@.microsoft.com...
> >> >> > Hi,
> >> >> > I did a full backup of my database, but everyday when it is backing up
> >> >> > I
> >> >> > would like it to overwrite the previous day's backup.
> >> >> > I'm doing it thru Transact-SQL script(T-SQL) so i am entering the
> >> >> > following
> >> >> > command:
> >> >> > "EXEC sp_addumpdevice 'disk', 'backup_file6', 'C:\test' backup database
> >> >> > WSS_Content to backup_file6 with init"
> >> >> >
> >> >> > Is this right? Or do I need to include something else?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||If your dumbdevice is "backup_file9" then:
BACKUP DATABASE mydb TO backup_file9 WITH INIT
This command will backup "mydb" database to "bacup_file9" dumbdevice and
it'll overwrite the old one(s).
--
Ekrem Ã?nsoy
"Shelley" <Shelley@.discussions.microsoft.com> wrote in message
news:AAE46936-7E69-45AD-8E02-BF986546FD28@.microsoft.com...
> Hi, thanks for your response......
> What command exactly do I need to use -
> "EXEC backup database WSS_Content to backup_file9
> with skip, init" ?
> "Tibor Karaszi" wrote:
>> sp_addumpdevice creates a backupdevice, which you later use in your
>> backup command. So, you only
>> need to do this once.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
>> news:2808B4FF-8B74-4596-BDF2-A574E6FDA2D4@.microsoft.com...
>> > Hi,
>> > I'm trying to do an automated backup where it overwrites the previous
>> > backup. I'm running the command:
>> > "EXEC sp_addumpdevice 'disk', 'backup_file9', 'C:\Friday' backup
>> > database
>> > WSS_Content to backup_file9
>> > with skip, init"
>> >
>> > But when I go to View Job History, I'm getting the error:
>> > "The add device request was denied. A physical device named
>> > 'C:\Friday'
>> > already exists. Only one backup device may refer to any physical
>> > device
>> > name.[SQLSTATE 42000](Error 15061). The step failed."
>> >
>> > Why exactly is that? What needs to be done?
>> >
>> > Please help.......
>> >
>> >
>> > "TheSQLGuru" wrote:
>> >
>> >> See Backup Database in Books Online. Everything is explained nicely
>> >> there.
>> >> To answer your question here yes, WITH options are simply comma
>> >> separated.
>> >>
>> >> --
>> >> Kevin G. Boles
>> >> TheSQLGuru
>> >> Indicium Resources, Inc.
>> >>
>> >>
>> >> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
>> >> news:73A564B3-E7E1-4EB8-BE86-5660091A7A97@.microsoft.com...
>> >> > Hi, thanks for the response........
>> >> > Where do I need to go to see the EXPIREDATE and RETAINDAYS options?
>> >> > Also, I use the SKIP option with "with skip, init"?
>> >> >
>> >> > "TheSQLGuru" wrote:
>> >> >
>> >> >> From BOL:
>> >> >>
>> >> >> INIT Specifies that all backup sets should be overwritten, but
>> >> >> preserves
>> >> >> the media header. If INIT is specified, any existing backup set on
>> >> >> that
>> >> >> device is overwritten, if conditions permit. By default, BACKUP
>> >> >> checks
>> >> >> for
>> >> >> the following conditions and does not overwrite the backup media if
>> >> >> either
>> >> >> condition exists:
>> >> >> a.. Any backup set has not yet expired. For more information, see
>> >> >> the
>> >> >> EXPIREDATE and RETAINDAYS options.
>> >> >>
>> >> >>
>> >> >> b.. The backup set name given in the BACKUP statement, if
>> >> >> provided,
>> >> >> does
>> >> >> not match the name on the backup media. For more information, see
>> >> >> the
>> >> >> NAME
>> >> >> option, earlier in this section.
>> >> >>
>> >> >> To override these checks, use the SKIP option.
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Kevin G. Boles
>> >> >> TheSQLGuru
>> >> >> Indicium Resources, Inc.
>> >> >>
>> >> >>
>> >> >> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
>> >> >> news:AFAA262A-FF58-4ABB-A2AC-A6803A88C87A@.microsoft.com...
>> >> >> > Hi,
>> >> >> > I did a full backup of my database, but everyday when it is
>> >> >> > backing up
>> >> >> > I
>> >> >> > would like it to overwrite the previous day's backup.
>> >> >> > I'm doing it thru Transact-SQL script(T-SQL) so i am entering the
>> >> >> > following
>> >> >> > command:
>> >> >> > "EXEC sp_addumpdevice 'disk', 'backup_file6', 'C:\test' backup
>> >> >> > database
>> >> >> > WSS_Content to backup_file6 with init"
>> >> >> >
>> >> >> > Is this right? Or do I need to include something else?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Thank you so much......It worked!
"Ekrem Ã?nsoy" wrote:
> If your dumbdevice is "backup_file9" then:
> BACKUP DATABASE mydb TO backup_file9 WITH INIT
> This command will backup "mydb" database to "bacup_file9" dumbdevice and
> it'll overwrite the old one(s).
> --
> Ekrem Ã?nsoy
>
> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
> news:AAE46936-7E69-45AD-8E02-BF986546FD28@.microsoft.com...
> > Hi, thanks for your response......
> > What command exactly do I need to use -
> > "EXEC backup database WSS_Content to backup_file9
> > with skip, init" ?
> >
> > "Tibor Karaszi" wrote:
> >
> >> sp_addumpdevice creates a backupdevice, which you later use in your
> >> backup command. So, you only
> >> need to do this once.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
> >> news:2808B4FF-8B74-4596-BDF2-A574E6FDA2D4@.microsoft.com...
> >> > Hi,
> >> > I'm trying to do an automated backup where it overwrites the previous
> >> > backup. I'm running the command:
> >> > "EXEC sp_addumpdevice 'disk', 'backup_file9', 'C:\Friday' backup
> >> > database
> >> > WSS_Content to backup_file9
> >> > with skip, init"
> >> >
> >> > But when I go to View Job History, I'm getting the error:
> >> > "The add device request was denied. A physical device named
> >> > 'C:\Friday'
> >> > already exists. Only one backup device may refer to any physical
> >> > device
> >> > name.[SQLSTATE 42000](Error 15061). The step failed."
> >> >
> >> > Why exactly is that? What needs to be done?
> >> >
> >> > Please help.......
> >> >
> >> >
> >> > "TheSQLGuru" wrote:
> >> >
> >> >> See Backup Database in Books Online. Everything is explained nicely
> >> >> there.
> >> >> To answer your question here yes, WITH options are simply comma
> >> >> separated.
> >> >>
> >> >> --
> >> >> Kevin G. Boles
> >> >> TheSQLGuru
> >> >> Indicium Resources, Inc.
> >> >>
> >> >>
> >> >> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
> >> >> news:73A564B3-E7E1-4EB8-BE86-5660091A7A97@.microsoft.com...
> >> >> > Hi, thanks for the response........
> >> >> > Where do I need to go to see the EXPIREDATE and RETAINDAYS options?
> >> >> > Also, I use the SKIP option with "with skip, init"?
> >> >> >
> >> >> > "TheSQLGuru" wrote:
> >> >> >
> >> >> >> From BOL:
> >> >> >>
> >> >> >> INIT Specifies that all backup sets should be overwritten, but
> >> >> >> preserves
> >> >> >> the media header. If INIT is specified, any existing backup set on
> >> >> >> that
> >> >> >> device is overwritten, if conditions permit. By default, BACKUP
> >> >> >> checks
> >> >> >> for
> >> >> >> the following conditions and does not overwrite the backup media if
> >> >> >> either
> >> >> >> condition exists:
> >> >> >> a.. Any backup set has not yet expired. For more information, see
> >> >> >> the
> >> >> >> EXPIREDATE and RETAINDAYS options.
> >> >> >>
> >> >> >>
> >> >> >> b.. The backup set name given in the BACKUP statement, if
> >> >> >> provided,
> >> >> >> does
> >> >> >> not match the name on the backup media. For more information, see
> >> >> >> the
> >> >> >> NAME
> >> >> >> option, earlier in this section.
> >> >> >>
> >> >> >> To override these checks, use the SKIP option.
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >> Kevin G. Boles
> >> >> >> TheSQLGuru
> >> >> >> Indicium Resources, Inc.
> >> >> >>
> >> >> >>
> >> >> >> "Shelley" <Shelley@.discussions.microsoft.com> wrote in message
> >> >> >> news:AFAA262A-FF58-4ABB-A2AC-A6803A88C87A@.microsoft.com...
> >> >> >> > Hi,
> >> >> >> > I did a full backup of my database, but everyday when it is
> >> >> >> > backing up
> >> >> >> > I
> >> >> >> > would like it to overwrite the previous day's backup.
> >> >> >> > I'm doing it thru Transact-SQL script(T-SQL) so i am entering the
> >> >> >> > following
> >> >> >> > command:
> >> >> >> > "EXEC sp_addumpdevice 'disk', 'backup_file6', 'C:\test' backup
> >> >> >> > database
> >> >> >> > WSS_Content to backup_file6 with init"
> >> >> >> >
> >> >> >> > Is this right? Or do I need to include something else?
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>

Overwriting Backup Set

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Can you simply insert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though.

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue.

First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them.

If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved.

I hope this makes sense.

|||

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty.

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

Attempt to parse the expression "@.[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot
be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @.[user::filename] and iam getting the error that i wrote previously.

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

|||I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.|||

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I hope this makes sense.

|||

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I hope this makes sense.

|||

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Saturday, February 25, 2012

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I hope this makes sense.

|||

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Wednesday, March 7, 2012

Overwriting the backup media

Overwriting text control

Overwriting entry in table

Overwriting backups

Overwriting backups

Overwriting backups

Overwriting backups

Overwriting backups

Overwriting backups

Overwriting Backup Set

Overwriting Backup Set

Overwriting Backup Set

Overwriting an excel file using SSIS

Overwriting an excel file using SSIS

Overwriting an excel file using SSIS

Saturday, February 25, 2012

Overwriting an excel file using SSIS

sql overflow error

Blog Archive

About Me