Backup w/RETAINDAYS just keeps growin', and growin'

Hi all;

I need to make daily backups to a computer in another domain, where I can't map a drive, so I use the TSQL to create a dump device, which works fine:

EXEC sp_addumpdevice 'disk', 'MyBackupDevice', [ipaddress & directory/file]

Then I schedule a server job which faithfully backs up my database, with one hitch - it never deletes days older than 5 as per the code - it just keeps growing. The command couldn't be much simpler:

BACKUP DATABASE ProductionETC to MyBackupDevice WITH RETAINDAYS = 5

For now, I just create a new backup device every now and then and go back and forth, deleting the oldest one when I create a new one. But it's annoying! Any ideas what I'm doing wrong?

Thanks,

L. Rap

RETAINDAYS only prevents SQL Server from overwriting the file unless the specified number of days have elapsed. It doesn't mean that SQL Server will automatically delete the file or erase the media after the specified number of days. So you will have to delete the files using your own scheduled job or use a database maintainence task (you can create from Enterprise Manager or SSMS).|||

Thanks Umachandar, that's very helpful. I misunderstood the instructions.

Would you happen to know if it is possible to delete the earliest backup from the set using T-SQL or some other preset command, or if I need to schedule an OS job to simply delete the file and start over?

Regards,

L. Rap

Backup to an USB Device

Hi:
Can anyone help me with backing up SQL Server Databases to a Shared USB
Device over a Network. I am able to Map the USB Drive on the machine. However
when I am trying to configure Backup using the Maintainence Wizard it does
not show the Mapped Drive. I am using SQL 2000 Server with Windows 2003
Server SP1. Please let me know if this is possible.
Thanks
Mitul
Hi,
You'll have to enter the UNC path to the device and then the account
that runs SQL server needs to have permissions to the share.
You problem right now is most likely that you have mapped the USB drive
with a different account than SQLserver/SQL server agent runs under and
that's why it doesn't appear.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Mitul Z wrote:
> Hi:
> Can anyone help me with backing up SQL Server Databases to a Shared USB
> Device over a Network. I am able to Map the USB Drive on the machine. However
> when I am trying to configure Backup using the Maintainence Wizard it does
> not show the Mapped Drive. I am using SQL 2000 Server with Windows 2003
> Server SP1. Please let me know if this is possible.
> Thanks
> Mitul
|||http://support.microsoft.com/kb/555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mitul Z" <MitulZ@.discussions.microsoft.com> wrote in message
news:630D134F-F808-41B1-932B-5E8746466A40@.microsoft.com...
> Hi:
> Can anyone help me with backing up SQL Server Databases to a Shared USB
> Device over a Network. I am able to Map the USB Drive on the machine.
> However
> when I am trying to configure Backup using the Maintainence Wizard it does
> not show the Mapped Drive. I am using SQL 2000 Server with Windows 2003
> Server SP1. Please let me know if this is possible.
> Thanks
> Mitul
|||Thanks for the reply.
The article states that this needs to be configured with a Domain account.
However we don't have an domain environment, then will SQL Server Services
running under Administrator Account suffice.
Thanks
Mitul Z
"Geoff N. Hiten" wrote:

> http://support.microsoft.com/kb/555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Mitul Z" <MitulZ@.discussions.microsoft.com> wrote in message
> news:630D134F-F808-41B1-932B-5E8746466A40@.microsoft.com...
>
|||The domain acount is necessary to access network resources. It provodes a
common security context. Administrator is only valid for the local machine
and has no meaning across the network. In short, no, it won't work
reliably.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mitul Z" <MitulZ@.discussions.microsoft.com> wrote in message
news:3F91A798-3779-4A21-A91C-983E3024A645@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply.
> The article states that this needs to be configured with a Domain account.
> However we don't have an domain environment, then will SQL Server Services
> running under Administrator Account suffice.
> Thanks
> Mitul Z
> "Geoff N. Hiten" wrote:

Backup to an USB Device

Hi:
Can anyone help me with backing up SQL Server Databases to a Shared USB
Device over a Network. I am able to Map the USB Drive on the machine. Howeve
r
when I am trying to configure Backup using the Maintainence Wizard it does
not show the Mapped Drive. I am using SQL 2000 Server with Windows 2003
Server SP1. Please let me know if this is possible.
Thanks
MitulHi,
You'll have to enter the UNC path to the device and then the account
that runs SQL server needs to have permissions to the share.
You problem right now is most likely that you have mapped the USB drive
with a different account than SQLserver/SQL server agent runs under and
that's why it doesn't appear.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Mitul Z wrote:
> Hi:
> Can anyone help me with backing up SQL Server Databases to a Shared USB
> Device over a Network. I am able to Map the USB Drive on the machine. Howe
ver
> when I am trying to configure Backup using the Maintainence Wizard it does
> not show the Mapped Drive. I am using SQL 2000 Server with Windows 2003
> Server SP1. Please let me know if this is possible.
> Thanks
> Mitul|||http://support.microsoft.com/kb/555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mitul Z" <MitulZ@.discussions.microsoft.com> wrote in message
news:630D134F-F808-41B1-932B-5E8746466A40@.microsoft.com...
> Hi:
> Can anyone help me with backing up SQL Server Databases to a Shared USB
> Device over a Network. I am able to Map the USB Drive on the machine.
> However
> when I am trying to configure Backup using the Maintainence Wizard it does
> not show the Mapped Drive. I am using SQL 2000 Server with Windows 2003
> Server SP1. Please let me know if this is possible.
> Thanks
> Mitul|||Thanks for the reply.
The article states that this needs to be configured with a Domain account.
However we don't have an domain environment, then will SQL Server Services
running under Administrator Account suffice.
Thanks
Mitul Z
"Geoff N. Hiten" wrote:

> http://support.microsoft.com/kb/555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Mitul Z" <MitulZ@.discussions.microsoft.com> wrote in message
> news:630D134F-F808-41B1-932B-5E8746466A40@.microsoft.com...
>|||The domain acount is necessary to access network resources. It provodes a
common security context. Administrator is only valid for the local machine
and has no meaning across the network. In short, no, it won't work
reliably.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mitul Z" <MitulZ@.discussions.microsoft.com> wrote in message
news:3F91A798-3779-4A21-A91C-983E3024A645@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply.
> The article states that this needs to be configured with a Domain account.
> However we don't have an domain environment, then will SQL Server Services
> running under Administrator Account suffice.
> Thanks
> Mitul Z
> "Geoff N. Hiten" wrote:
>

Backup to an USB Device

Hi:
Can anyone help me with backing up SQL Server Databases to a Shared USB
Device over a Network. I am able to Map the USB Drive on the machine. However
when I am trying to configure Backup using the Maintainence Wizard it does
not show the Mapped Drive. I am using SQL 2000 Server with Windows 2003
Server SP1. Please let me know if this is possible.
Thanks
MitulHi,
You'll have to enter the UNC path to the device and then the account
that runs SQL server needs to have permissions to the share.
You problem right now is most likely that you have mapped the USB drive
with a different account than SQLserver/SQL server agent runs under and
that's why it doesn't appear.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Mitul Z wrote:
> Hi:
> Can anyone help me with backing up SQL Server Databases to a Shared USB
> Device over a Network. I am able to Map the USB Drive on the machine. However
> when I am trying to configure Backup using the Maintainence Wizard it does
> not show the Mapped Drive. I am using SQL 2000 Server with Windows 2003
> Server SP1. Please let me know if this is possible.
> Thanks
> Mitul|||http://support.microsoft.com/kb/555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mitul Z" <MitulZ@.discussions.microsoft.com> wrote in message
news:630D134F-F808-41B1-932B-5E8746466A40@.microsoft.com...
> Hi:
> Can anyone help me with backing up SQL Server Databases to a Shared USB
> Device over a Network. I am able to Map the USB Drive on the machine.
> However
> when I am trying to configure Backup using the Maintainence Wizard it does
> not show the Mapped Drive. I am using SQL 2000 Server with Windows 2003
> Server SP1. Please let me know if this is possible.
> Thanks
> Mitul|||Thanks for the reply.
The article states that this needs to be configured with a Domain account.
However we don't have an domain environment, then will SQL Server Services
running under Administrator Account suffice.
Thanks
Mitul Z
"Geoff N. Hiten" wrote:
> http://support.microsoft.com/kb/555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Mitul Z" <MitulZ@.discussions.microsoft.com> wrote in message
> news:630D134F-F808-41B1-932B-5E8746466A40@.microsoft.com...
> > Hi:
> >
> > Can anyone help me with backing up SQL Server Databases to a Shared USB
> > Device over a Network. I am able to Map the USB Drive on the machine.
> > However
> > when I am trying to configure Backup using the Maintainence Wizard it does
> > not show the Mapped Drive. I am using SQL 2000 Server with Windows 2003
> > Server SP1. Please let me know if this is possible.
> >
> > Thanks
> > Mitul
>|||The domain acount is necessary to access network resources. It provodes a
common security context. Administrator is only valid for the local machine
and has no meaning across the network. In short, no, it won't work
reliably.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mitul Z" <MitulZ@.discussions.microsoft.com> wrote in message
news:3F91A798-3779-4A21-A91C-983E3024A645@.microsoft.com...
> Thanks for the reply.
> The article states that this needs to be configured with a Domain account.
> However we don't have an domain environment, then will SQL Server Services
> running under Administrator Account suffice.
> Thanks
> Mitul Z
> "Geoff N. Hiten" wrote:
>> http://support.microsoft.com/kb/555128
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "Mitul Z" <MitulZ@.discussions.microsoft.com> wrote in message
>> news:630D134F-F808-41B1-932B-5E8746466A40@.microsoft.com...
>> > Hi:
>> >
>> > Can anyone help me with backing up SQL Server Databases to a Shared USB
>> > Device over a Network. I am able to Map the USB Drive on the machine.
>> > However
>> > when I am trying to configure Backup using the Maintainence Wizard it
>> > does
>> > not show the Mapped Drive. I am using SQL 2000 Server with Windows 2003
>> > Server SP1. Please let me know if this is possible.
>> >
>> > Thanks
>> > Mitul
>>