Backup or move transaction logs to another server.

Can anyone advice me:
I'm backing up my transaction logs every 2 hours using the SQL Server
database management wizard.
I only keep the logs for two days before the wizard automatically
deletes the ones which are older than two days.
I want to include into the routine a process which will move the
transaction logs to a different server, but will also delete the logs
on the other server if they are older than 2 days. Is this possible?
Or is it possible to set SQL Server to backup the transaction logs to
another server using the wizard or some other means. I can only see
the local driver on the server, even if I've mapped one.
Many thanks in advancethis is from google.com
the default backup folder path is stored in the regisrty at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirector
y
for a default SQL Server installation. You can edit the value by specifying
the required valid path.
Becareful while editing the registry.
--
HTH,
Vyas, MVP (SQL Server)
"Allan Martin" <allanmartin@.ntlworld.com> wrote in message
news:a6d765d6.0401200822.415cb11c@.posting.google.com...
> Can anyone advice me:
> I'm backing up my transaction logs every 2 hours using the SQL Server
> database management wizard.
> I only keep the logs for two days before the wizard automatically
> deletes the ones which are older than two days.
> I want to include into the routine a process which will move the
> transaction logs to a different server, but will also delete the logs
> on the other server if they are older than 2 days. Is this possible?
> Or is it possible to set SQL Server to backup the transaction logs to
> another server using the wizard or some other means. I can only see
> the local driver on the server, even if I've mapped one.
> Many thanks in advance|||I have the same problem.
How does this solution help? Enterprise Manager still refuses to accept the
UNC address (as shown in BOL "sp_addumpdevice") and although sp_addumpdevice
will accept it (it accepts anything), the backup command complains that it
can't open the backup device. Here's the code:
exec sp_addumpdevice 'disk', 'device1', 'C:\Temp\backup.dat' -- local drive
exec sp_addumpdevice 'disk', 'device2', 'R:\Temp\backup.dat' -- remote
drive
exec sp_addumpdevice 'disk', 'device3', '\\Acqserver\R\Temp\backup.dat' --
same drive, UNC notation
backup database PC_DASC_DB to device1 -- this works
backup database PC_DASC_DB to device2 -- this fails
backup database PC_DASC_DB to device3 -- this fails
TIA,
Wm Schmidt
"ME" <mail@.moon.net> wrote in message
news:%23WzsUn33DHA.3216@.TK2MSFTNGP11.phx.gbl...
> this is from google.com
> the default backup folder path is stored in the regisrty at:
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirector
> y
> for a default SQL Server installation. You can edit the value by
specifying
> the required valid path.
> Becareful while editing the registry.
> --
> HTH,
> Vyas, MVP (SQL Server)
>
> "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> news:a6d765d6.0401200822.415cb11c@.posting.google.com...
> > Can anyone advice me:
> >
> > I'm backing up my transaction logs every 2 hours using the SQL Server
> > database management wizard.
> > I only keep the logs for two days before the wizard automatically
> > deletes the ones which are older than two days.
> >
> > I want to include into the routine a process which will move the
> > transaction logs to a different server, but will also delete the logs
> > on the other server if they are older than 2 days. Is this possible?
> > Or is it possible to set SQL Server to backup the transaction logs to
> > another server using the wizard or some other means. I can only see
> > the local driver on the server, even if I've mapped one.
> >
> > Many thanks in advance
>|||if you use the wizard you don't have the choice. SQL wizard backup db and
logs to .BAK and .TRN files
"Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
news:OKVnQG53DHA.4060@.TK2MSFTNGP11.phx.gbl...
> I have the same problem.
> How does this solution help? Enterprise Manager still refuses to accept
the
> UNC address (as shown in BOL "sp_addumpdevice") and although
sp_addumpdevice
> will accept it (it accepts anything), the backup command complains that it
> can't open the backup device. Here's the code:
> exec sp_addumpdevice 'disk', 'device1', 'C:\Temp\backup.dat' -- local
drive
> exec sp_addumpdevice 'disk', 'device2', 'R:\Temp\backup.dat' -- remote
> drive
> exec sp_addumpdevice 'disk', 'device3',
\\Acqserver\R\Temp\backup.dat' --
> same drive, UNC notation
> backup database PC_DASC_DB to device1 -- this works
> backup database PC_DASC_DB to device2 -- this fails
> backup database PC_DASC_DB to device3 -- this fails
> TIA,
> Wm Schmidt
>
> "ME" <mail@.moon.net> wrote in message
> news:%23WzsUn33DHA.3216@.TK2MSFTNGP11.phx.gbl...
> > this is from google.com
> >
> > the default backup folder path is stored in the regisrty at:
> >
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirector
> > y
> > for a default SQL Server installation. You can edit the value by
> specifying
> > the required valid path.
> >
> > Becareful while editing the registry.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> >
> >
> > "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> > news:a6d765d6.0401200822.415cb11c@.posting.google.com...
> > > Can anyone advice me:
> > >
> > > I'm backing up my transaction logs every 2 hours using the SQL Server
> > > database management wizard.
> > > I only keep the logs for two days before the wizard automatically
> > > deletes the ones which are older than two days.
> > >
> > > I want to include into the routine a process which will move the
> > > transaction logs to a different server, but will also delete the logs
> > > on the other server if they are older than 2 days. Is this possible?
> > > Or is it possible to set SQL Server to backup the transaction logs to
> > > another server using the wizard or some other means. I can only see
> > > the local driver on the server, even if I've mapped one.
> > >
> > > Many thanks in advance
> >
> >
>|||Ok, let's forget EI. I have to use T-SQL for my app anyway. I'm running
this test using the Query Analyzer with the commands shown below. I changed
all the file names to backup.bak but it still doesn't work for network
drives.
"ME" <mail@.moon.net> wrote in message
news:OXJqQS53DHA.1404@.TK2MSFTNGP11.phx.gbl...
> if you use the wizard you don't have the choice. SQL wizard backup db and
> logs to .BAK and .TRN files
>
> "Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
> news:OKVnQG53DHA.4060@.TK2MSFTNGP11.phx.gbl...
> > I have the same problem.
> >
> > How does this solution help? Enterprise Manager still refuses to accept
> the
> > UNC address (as shown in BOL "sp_addumpdevice") and although
> sp_addumpdevice
> > will accept it (it accepts anything), the backup command complains that
it
> > can't open the backup device. Here's the code:
> >
> > exec sp_addumpdevice 'disk', 'device1', 'C:\Temp\backup.dat' -- local
> drive
> > exec sp_addumpdevice 'disk', 'device2', 'R:\Temp\backup.dat' -- remote
> > drive
> > exec sp_addumpdevice 'disk', 'device3',
> \\Acqserver\R\Temp\backup.dat' --
> > same drive, UNC notation
> >
> > backup database PC_DASC_DB to device1 -- this works
> >
> > backup database PC_DASC_DB to device2 -- this fails
> >
> > backup database PC_DASC_DB to device3 -- this fails
> >
> > TIA,
> > Wm Schmidt
> >
> >
> > "ME" <mail@.moon.net> wrote in message
> > news:%23WzsUn33DHA.3216@.TK2MSFTNGP11.phx.gbl...
> > > this is from google.com
> > >
> > > the default backup folder path is stored in the regisrty at:
> > >
> >
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirector
> > > y
> > > for a default SQL Server installation. You can edit the value by
> > specifying
> > > the required valid path.
> > >
> > > Becareful while editing the registry.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > >
> > >
> > > "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> > > news:a6d765d6.0401200822.415cb11c@.posting.google.com...
> > > > Can anyone advice me:
> > > >
> > > > I'm backing up my transaction logs every 2 hours using the SQL
Server
> > > > database management wizard.
> > > > I only keep the logs for two days before the wizard automatically
> > > > deletes the ones which are older than two days.
> > > >
> > > > I want to include into the routine a process which will move the
> > > > transaction logs to a different server, but will also delete the
logs
> > > > on the other server if they are older than 2 days. Is this possible?
> > > > Or is it possible to set SQL Server to backup the transaction logs
to
> > > > another server using the wizard or some other means. I can only see
> > > > the local driver on the server, even if I've mapped one.
> > > >
> > > > Many thanks in advance
> > >
> > >
> >
> >
>|||Statements are valid and probably you don't have valid permission on remote
machine.
Does your SQLServerAgent Service have a permission to write on remote
machine?
"Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
news:OnHvwg53DHA.1264@.TK2MSFTNGP11.phx.gbl...
> Ok, let's forget EI. I have to use T-SQL for my app anyway. I'm running
> this test using the Query Analyzer with the commands shown below. I
changed
> all the file names to backup.bak but it still doesn't work for network
> drives.
>
> "ME" <mail@.moon.net> wrote in message
> news:OXJqQS53DHA.1404@.TK2MSFTNGP11.phx.gbl...
> > if you use the wizard you don't have the choice. SQL wizard backup db
and
> > logs to .BAK and .TRN files
> >
> >
> > "Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
> > news:OKVnQG53DHA.4060@.TK2MSFTNGP11.phx.gbl...
> > > I have the same problem.
> > >
> > > How does this solution help? Enterprise Manager still refuses to
accept
> > the
> > > UNC address (as shown in BOL "sp_addumpdevice") and although
> > sp_addumpdevice
> > > will accept it (it accepts anything), the backup command complains
that
> it
> > > can't open the backup device. Here's the code:
> > >
> > > exec sp_addumpdevice 'disk', 'device1', 'C:\Temp\backup.dat' -- local
> > drive
> > > exec sp_addumpdevice 'disk', 'device2', 'R:\Temp\backup.dat' --
remote
> > > drive
> > > exec sp_addumpdevice 'disk', 'device3',
> > \\Acqserver\R\Temp\backup.dat' --
> > > same drive, UNC notation
> > >
> > > backup database PC_DASC_DB to device1 -- this works
> > >
> > > backup database PC_DASC_DB to device2 -- this fails
> > >
> > > backup database PC_DASC_DB to device3 -- this fails
> > >
> > > TIA,
> > > Wm Schmidt
> > >
> > >
> > > "ME" <mail@.moon.net> wrote in message
> > > news:%23WzsUn33DHA.3216@.TK2MSFTNGP11.phx.gbl...
> > > > this is from google.com
> > > >
> > > > the default backup folder path is stored in the regisrty at:
> > > >
> > >
> >
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirector
> > > > y
> > > > for a default SQL Server installation. You can edit the value by
> > > specifying
> > > > the required valid path.
> > > >
> > > > Becareful while editing the registry.
> > > > --
> > > > HTH,
> > > > Vyas, MVP (SQL Server)
> > > >
> > > >
> > > > "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> > > > news:a6d765d6.0401200822.415cb11c@.posting.google.com...
> > > > > Can anyone advice me:
> > > > >
> > > > > I'm backing up my transaction logs every 2 hours using the SQL
> Server
> > > > > database management wizard.
> > > > > I only keep the logs for two days before the wizard automatically
> > > > > deletes the ones which are older than two days.
> > > > >
> > > > > I want to include into the routine a process which will move the
> > > > > transaction logs to a different server, but will also delete the
> logs
> > > > > on the other server if they are older than 2 days. Is this
possible?
> > > > > Or is it possible to set SQL Server to backup the transaction logs
> to
> > > > > another server using the wizard or some other means. I can only
see
> > > > > the local driver on the server, even if I've mapped one.
> > > > >
> > > > > Many thanks in advance
> > > >
> > > >
> > >
> > >
> >
> >
>|||Problem solved. It turns out that MSSQLSERVER was logged on as LocalSystem
(the default when a service is installed). By setting the login to
administrator I was able to successfully do a backup to a remote drive.
Apparently it doesn't matter that the remote drive is shared with full
permissions for Everyone. Or that SQLServerAgent was logged on as
administrator.
Thanks for your help in solving the problem.
William
"Ana Mihalj" <amihalj@.hotmail.com> wrote in message
news:%23COs1X$3DHA.632@.TK2MSFTNGP12.phx.gbl...
> Statements are valid and probably you don't have valid permission on
remote
> machine.
> Does your SQLServerAgent Service have a permission to write on remote
> machine?
>
>
>
> "Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
> news:OnHvwg53DHA.1264@.TK2MSFTNGP11.phx.gbl...
> > Ok, let's forget EI. I have to use T-SQL for my app anyway. I'm
running
> > this test using the Query Analyzer with the commands shown below. I
> changed
> > all the file names to backup.bak but it still doesn't work for network
> > drives.
> >
> >
> > "ME" <mail@.moon.net> wrote in message
> > news:OXJqQS53DHA.1404@.TK2MSFTNGP11.phx.gbl...
> > > if you use the wizard you don't have the choice. SQL wizard backup db
> and
> > > logs to .BAK and .TRN files
> > >
> > >
> > > "Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
> > > news:OKVnQG53DHA.4060@.TK2MSFTNGP11.phx.gbl...
> > > > I have the same problem.
> > > >
> > > > How does this solution help? Enterprise Manager still refuses to
> accept
> > > the
> > > > UNC address (as shown in BOL "sp_addumpdevice") and although
> > > sp_addumpdevice
> > > > will accept it (it accepts anything), the backup command complains
> that
> > it
> > > > can't open the backup device. Here's the code:
> > > >
> > > > exec sp_addumpdevice 'disk', 'device1', 'C:\Temp\backup.dat' --
local
> > > drive
> > > > exec sp_addumpdevice 'disk', 'device2', 'R:\Temp\backup.dat' --
> remote
> > > > drive
> > > > exec sp_addumpdevice 'disk', 'device3',
> > > \\Acqserver\R\Temp\backup.dat' --
> > > > same drive, UNC notation
> > > >
> > > > backup database PC_DASC_DB to device1 -- this works
> > > >
> > > > backup database PC_DASC_DB to device2 -- this fails
> > > >
> > > > backup database PC_DASC_DB to device3 -- this fails
> > > >
> > > > TIA,
> > > > Wm Schmidt
> > > >
> > > >
> > > > "ME" <mail@.moon.net> wrote in message
> > > > news:%23WzsUn33DHA.3216@.TK2MSFTNGP11.phx.gbl...
> > > > > this is from google.com
> > > > >
> > > > > the default backup folder path is stored in the regisrty at:
> > > > >
> > > >
> > >
> >
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirector
> > > > > y
> > > > > for a default SQL Server installation. You can edit the value by
> > > > specifying
> > > > > the required valid path.
> > > > >
> > > > > Becareful while editing the registry.
> > > > > --
> > > > > HTH,
> > > > > Vyas, MVP (SQL Server)
> > > > >
> > > > >
> > > > > "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> > > > > news:a6d765d6.0401200822.415cb11c@.posting.google.com...
> > > > > > Can anyone advice me:
> > > > > >
> > > > > > I'm backing up my transaction logs every 2 hours using the SQL
> > Server
> > > > > > database management wizard.
> > > > > > I only keep the logs for two days before the wizard
automatically
> > > > > > deletes the ones which are older than two days.
> > > > > >
> > > > > > I want to include into the routine a process which will move the
> > > > > > transaction logs to a different server, but will also delete the
> > logs
> > > > > > on the other server if they are older than 2 days. Is this
> possible?
> > > > > > Or is it possible to set SQL Server to backup the transaction
logs
> > to
> > > > > > another server using the wizard or some other means. I can only
> see
> > > > > > the local driver on the server, even if I've mapped one.
> > > > > >
> > > > > > Many thanks in advance
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>