|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Overwriting Backup SetHi,
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. -- Show quoteKevin G. Boles TheSQLGuru Indicium Resources, Inc. "Shelley" <Shel***@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"? Show quote "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" <Shel***@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. -- Show quoteKevin G. Boles TheSQLGuru Indicium Resources, Inc. "Shelley" <Shel***@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" <Shel***@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......... Show quote "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" <Shel***@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" <Shel***@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. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Shelley" <Shel***@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" <Shel***@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" <Shel***@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" ? Show quote "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" <Shel***@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" <Shel***@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" <Shel***@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). -- Show quoteEkrem Önsoy "Shelley" <Shel***@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" <Shel***@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" <Shel***@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" <Shel***@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!
Show quote "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" <Shel***@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" <Shel***@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" <Shel***@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" <Shel***@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? > >> >> >> > >> >> >> > >> >> >> > >> >> > >> >> > >> >> > >> > >> > >> >  |
|||||||||||||||||||||||
Other interesting topics