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\MS
SQLServer\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...
quote:|||I have the same problem.
> 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
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...
quote:
> this is from google.com
> the default backup folder path is stored in the regisrty at:
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\BackupDirector[QUO
TE]
> y
> for a default SQL Server installation. You can edit the value by[/QUOTE]
specifying
quote:|||if you use the wizard you don't have the choice. SQL wizard backup db and
> 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...
>
logs to .BAK and .TRN files
"Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
news:OKVnQG53DHA.4060@.TK2MSFTNGP11.phx.gbl...
quote:
> I have the same problem.
> How does this solution help? Enterprise Manager still refuses to accept
the
quote:
> UNC address (as shown in BOL "sp_addumpdevice") and although
sp_addumpdevice
quote:
> 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
quote:
> exec sp_addumpdevice 'disk', 'device2', 'R:\Temp\backup.dat' -- remote
> drive
> exec sp_addumpdevice 'disk', 'device3',
\\Acqserver\R\Temp\backup.dat' --
quote:
> 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...
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\BackupDirector[QUO
TE]
> specifying
>[/QUOTE]|||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...
quote:
> 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...
> the
> sp_addumpdevice
it[QUOTE]
> drive
> \\Acqserver\R\Temp\backup.dat' --
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\BackupDirector[QUO
TE]
Server
quote:|||Statements are valid and probably you don't have valid permission on remote
logs[QUOTE]
to[QUOTE]
>
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...
quote:
> 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
quote:
> 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...
and[QUOTE]
accept[QUOTE]
that[QUOTE]
> it
remote[QUOTE]
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\BackupDirector[QUO
TE]
> Server
> logs
possible?
quote:|||Problem solved. It turns out that MSSQLSERVER was logged on as LocalSystem
> to
see[QUOTE]
>
(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...
quote:
> Statements are valid and probably you don't have valid permission on
remote
quote:
> 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...
running[QUOTE]
> changed
> and
> accept
> that
local[QUOTE]
> remote
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\BackupDirector[QUO
TE]
automatically
quote:
> possible?
logs[QUOTE]
> see
>
No comments:
Post a Comment