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