Backup Script Revisions Help

We recently made some changes to our hourly transaction log backups script
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although the
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileNameAS VARCHAR(200)
DECLARE @.HoursAS CHAR(02)
DECLARE @.MinsAS CHAR(02)
DECLARE @.SecsAS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTimeAS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins= substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs= substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.
Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:

> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script is
> running well, but the old backups are not deleted from the disk, although the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileNameAS VARCHAR(200)
> DECLARE @.HoursAS CHAR(02)
> DECLARE @.MinsAS CHAR(02)
> DECLARE @.SecsAS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTimeAS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins= substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs= substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script to
> ensure that backups are deleted successfully? Thanks in advance for your
> help.

Backup Script Revisions Help

We recently made some changes to our hourly transaction log backups script
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although th
e
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileName AS VARCHAR(200)
DECLARE @.Hours AS CHAR(02)
DECLARE @.Mins AS CHAR(02)
DECLARE @.Secs AS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTime AS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:

> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script
is
> running well, but the old backups are not deleted from the disk, although
the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileName AS VARCHAR(200)
> DECLARE @.Hours AS CHAR(02)
> DECLARE @.Mins AS CHAR(02)
> DECLARE @.Secs AS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTime AS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script t
o
> ensure that backups are deleted successfully? Thanks in advance for your
> help.

Backup Script Revisions Help

We recently made some changes to our hourly transaction log backups script
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although the
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileName AS VARCHAR(200)
DECLARE @.Hours AS CHAR(02)
DECLARE @.Mins AS CHAR(02)
DECLARE @.Secs AS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTime AS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:
> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script is
> running well, but the old backups are not deleted from the disk, although the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileName AS VARCHAR(200)
> DECLARE @.Hours AS CHAR(02)
> DECLARE @.Mins AS CHAR(02)
> DECLARE @.Secs AS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTime AS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script to
> ensure that backups are deleted successfully? Thanks in advance for your
> help.sql

BackUp Question

When you do a full back-up of a sql server db, does that include the transaction logs or do they still have to be backed up seperately?
Thanks!Depends what your question is - does a full backup include all transactions in T-log? Yes. Does full backup checkpoint the T-log and truncate it? No. Does it shrink the T-log in a full backup? No.

To test this run this command:

dbcc sqlperf('logspace')

this will show you the percentage of your T-log used. Run a full backup then run the command again and you will see that the percentage did not go down. If you are having T-log growth problems, you probably want to back it up periodically.

Another good command to know for the T-log is this one:

select * from ::fn_dblog(null,null)

This will show you the entries in your active T-log.

HTH

Backup practices ...

Isn't it better to backup databases and transaction logs to a different
drive than the data files? Both for reliability (if the drive dies you're
hurting since the backup is there as well) and effeciency (less contention
on the data drive). If you're backing up everything to the same drive as the
data, can this cause processor spikes? We do backup to tape as well, but
this is not as fast to recover from when there are a zillion users.
As you probably guessed, some pre-existing maintence plans are set up this
way and I'm thinking I should push to get them changed. But this may require
additional hardware so somebody has to write a check. You know the drill.
Thanks,
Bob Castleman
DBA Poseur"Bob Castleman" <nomail@.here> wrote in message
news:e$madNqaFHA.3684@.TK2MSFTNGP12.phx.gbl...
> Isn't it better to backup databases and transaction logs to a different
> drive than the data files? Both for reliability (if the drive dies you're
> hurting since the backup is there as well) and effeciency (less contention
> on the data drive). If you're backing up everything to the same drive as
the
> data, can this cause processor spikes? We do backup to tape as well, but
> this is not as fast to recover from when there are a zillion users.
> As you probably guessed, some pre-existing maintence plans are set up this
> way and I'm thinking I should push to get them changed. But this may
require
> additional hardware so somebody has to write a check. You know the drill.
> Thanks,
> Bob Castleman
> DBA Poseur
>
Actually Bob, you should always do your backups to the same local drives as
the active databases are on. In fact, I'm not sure why you are even
bothering with doing backups at all... <wink>
You are correct, a good backup (and recovery) strategy should be
implemented. Backing up to the same hard disks is dependent on a lot of
different factors.
1. How much activity is going on during the backup process?
2. How big are the backups that we are talking about?
3. How much network traffic do you have already (if you wanted to push your
backups to a UNC name on a different computer).
That said, having your backups in contention with the active databases on
the same physical drives is almost never a good idea (for the reasons you
listed).
Rick Sawtell|||
> Actually Bob, you should always do your backups to the same local drives
> as
> the active databases are on. In fact, I'm not sure why you are even
> bothering with doing backups at all... <wink>
Facetiously sarcastic. Gotta luv it!

> 1. How much activity is going on during the backup process?
Log backup during the production day every four hours spike the processor
pretty hard. Full backups at night are not an issues, yet.

> 2. How big are the backups that we are talking about?
Several hundred databases. Transaction logs every four hours total about a
gig. Full nightly backups are about 100 gig.

> 3. How much network traffic do you have already (if you wanted to push
> your
> backups to a UNC name on a different computer).
>
None right now. Backups are over a fiber chanel to the RAID and so far
network traffic to the database servers is very low.|||"Bob Castleman" <nomail@.here> wrote in message
news:uKsEHrqaFHA.220@.TK2MSFTNGP10.phx.gbl...
>
> Log backup during the production day every four hours spike the processor
> pretty hard. Full backups at night are not an issues, yet.
>
Well, if you can't get the new hardware, how about doing TLog dumps more
often. You still have the pay the piper for the CPU time, but if they were
every hour instead of every 4 hours, your spikes should be shorter-lived.

> None right now. Backups are over a fiber chanel to the RAID and so far
> network traffic to the database servers is very low.
>
Very nice!!!
Rick Sawtell

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\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:

---

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

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:

---

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

---

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

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:

---

logs[QUOTE]
to[QUOTE]
>

---

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

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:

---

> to
see[QUOTE]
>

---

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

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
>

---

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

Backup of Transaction Logs

When setting up a backup of the database the option to
backup the transaction logs is grayed out. How do I set up
a seperate backup for the T-Logs after I have set up
backup jobs for the data?Most probable reason is that the database is in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ray Ellers" <rellers@.flexiblesystems.com> wrote in message
news:40bf01c3aac7$6000b9e0$a601280a@.phx.gbl...
> When setting up a backup of the database the option to
> backup the transaction logs is grayed out. How do I set up
> a seperate backup for the T-Logs after I have set up
> backup jobs for the data?|||This is a multi-part message in MIME format.
--=_NextPart_000_01DB_01C3AA9E.4191E160
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You will have to change the recovery model for the database to full or
bulk-logged.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Ray Ellers" <rellers@.flexiblesystems.com> wrote in message
news:40bf01c3aac7$6000b9e0$a601280a@.phx.gbl...
When setting up a backup of the database the option to
backup the transaction logs is grayed out. How do I set up
a seperate backup for the T-Logs after I have set up
backup jobs for the data?
--=_NextPart_000_01DB_01C3AA9E.4191E160
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You will have to change the recovery =model for the database to full or bulk-logged.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Ray Ellers" wrote in message news:40bf01c3aac7$60=00b9e0$a601280a@.phx.gbl...When setting up a backup of the database the option to backup the =transaction logs is grayed out. How do I set up a seperate backup for the T-Logs =after I have set up backup jobs for the data?

---

--=_NextPart_000_01DB_01C3AA9E.4191E160--

Backup of Log fails.

It appears that the data is backing up fine but logs do not. Obviously something has changed but I cannot see what. I have plenty of space and privs but the backups fail with the below errorlog. If anyone can point me in a new direction I would greatly appreciate it.

2005-12-31 08:22:34.16 spid11 Analysis of database 'CEMS_PRO' (7) is 100% complete (approximately 0 more seconds)
2005-12-31 08:22:34.25 spid5 Starting up database 'tempdb'.
2005-12-31 08:22:34.58 spid3 Recovery complete.
2005-12-31 08:22:34.58 spid3 SQL global counter collection task is created.
2005-12-31 08:22:36.22 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2005-12-31 09:11:52.18 spid173 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'xp_regread'.
2005-12-31 09:11:54.85 spid173 Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure 'xp_cmdshell'.
2005-12-31 21:00:00.75 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 22:00:00.92 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 23:00:00.86 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 00:45:00.91 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH INIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 02:25:22.67 backup Database backed up: Database: CEMS_PRO, creation date(time): 2005/01/17(12:36:00), pages dumped: 17259208, first LSN: 45585:18685:21, last LSN: 45586:81369:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'CEMS_Backup'}).
2006-01-01 02:25:23.03 backup BACKUP failed to complete the command backup log CEMS_PRO TO CEMS_Log_Back2 WITH INIT , NOUNLOAD , NAME = N'CEMS_Log_Backup', NOSKIP , STATS = 10, NOFORMAT

You might try running the sql profiler tool to capture the error messages that are being sent back to the backup application.

If you are running this as part of a maintenance plan, then there should be job history logs that capture the error messages.

|||

We are recieving the exact same error:

SQL Profiler is a powerful tool for debugging SQL. What parts do you recommend to monitor?

Thanks

|||What happens if you take the command which is shown in the log and run it in QA or SSMS?

Backup of Log fails.

It appears that the data is backing up fine but logs do not. Obviously something has changed but I cannot see what. I have plenty of space and privs but the backups fail with the below errorlog. If anyone can point me in a new direction I would greatly appreciate it.

2005-12-31 08:22:34.16 spid11 Analysis of database 'CEMS_PRO' (7) is 100% complete (approximately 0 more seconds)
2005-12-31 08:22:34.25 spid5 Starting up database 'tempdb'.
2005-12-31 08:22:34.58 spid3 Recovery complete.
2005-12-31 08:22:34.58 spid3 SQL global counter collection task is created.
2005-12-31 08:22:36.22 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2005-12-31 09:11:52.18 spid173 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'xp_regread'.
2005-12-31 09:11:54.85 spid173 Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure 'xp_cmdshell'.
2005-12-31 21:00:00.75 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 22:00:00.92 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 23:00:00.86 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 00:45:00.91 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH INIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 02:25:22.67 backup Database backed up: Database: CEMS_PRO, creation date(time): 2005/01/17(12:36:00), pages dumped: 17259208, first LSN: 45585:18685:21, last LSN: 45586:81369:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'CEMS_Backup'}).
2006-01-01 02:25:23.03 backup BACKUP failed to complete the command backup log CEMS_PRO TO CEMS_Log_Back2 WITH INIT , NOUNLOAD , NAME = N'CEMS_Log_Backup', NOSKIP , STATS = 10, NOFORMAT

You might try running the sql profiler tool to capture the error messages that are being sent back to the backup application.

If you are running this as part of a maintenance plan, then there should be job history logs that capture the error messages.

|||

We are recieving the exact same error:

SQL Profiler is a powerful tool for debugging SQL. What parts do you recommend to monitor?

Thanks

|||What happens if you take the command which is shown in the log and run it in QA or SSMS?

backup Maintenance wizard

Will Trans. logs backup options be in the wizard in sp2?

At this point there are no options to backup trans. logs every 1hr and deleting files over x amount of days old without doing a TSQL command correct?

To clarify, in 2k enterprise, it's in the maintenance plan. I can specify that info and give it the trn extension and folder to backup into.

I am a network admin being given sql duties now, so If I may seem like a beginner, it's because I am.

I did to a search for this topic, so forgive me if there is one i did not find.

Also, I was trying to submit feedback as requested for sp2 about this, but i did not see a button on the bottom of the search through feedback as stated.

You can do transaction log backups along with differential backups in a maintenance plan. You have to add a Backup database task. Then you can configure the backup type within the task. (It's the third field from the top in the dialog.)|||Probably looking for a similar option ion SQL Server 2000. I had a hard time myself way back.|||

You can back up Transaction logs every hour if you want to, just set up a job to back them up as often as you wish, for example:

http://img.photobucket.com/albums/v472/Schmedrick/backuptranslog.gif

You can also delete backup files older than "x" days by adding a maintenance cleanup task to your maintenance plan after it has been created, this does require that SP1 has been installed, for example:

http://img.photobucket.com/albums/v472/Schmedrick/mainttask.gif

|||

Is there a way to run hourly transaction log backups while backing up the database once a day in ONE maintenance plan (like SQL2000)? Or do we have to create one maintenance plan for the full backup and another one for the transaction log backups.

|||

No. A maintenance plan is generated as a single monolithic SSIS package. That means the entire package has a single schedule. If you have multiple tasks in the maintenance plan, they will all run on the same schedule. So, in 2005, you have to create a separate maintenance plan for each group of tasks that need to have a distinct schedule. It is for this reason, that I do not recommend using maintenance plans at all and simply writing the code for this stuff yourself. I also don't like the fact that I have to enlist the SSIS engine, so now running my backups has a dependency on the SSIS engine as well as the security access of the SSIS engine. If something happens to SSIS, my backups no longer run. Can you tell that I absolutely hate the new maintenance plans in 2005? It takes something that was very simple and very straightforward and turned it into a mess than has to transit multiple subsystems in order to perform and VERY straightforward task.

Below is the set of code that I use for backing up databases along with a procedure to clean up the backup history and backup files. All T-SQL. Simple, straightforward, and it is smart enough to not try to issue a tran log backup against a database that is in simple recovery model. All you have to do is create the jobs to call the backup and clean-up based on the type of backup you want to run and the frequency you want it to run on.

CREATE TABLE [dbo].[backupfiles](

[DatabaseName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BackupFileName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CreationDate] [datetime] NULL

) ON [PRIMARY]

GO

CREATE procedure [dbo].[asp_backup]

@.dir varchar(50), @.type char(1)

as

/****************************************************************/

/* Stored Procedure: asp_backup */

/* Creation Date: 4/01/2006 */

/* Written by: Mike Hotek - MHS Enterprises, Inc. */

/* http://www.mssqlserver.com */

/* Copyright: 2006 MHS Enterprises, Inc. All Rights Reserved */

/* Code can be used in its entirety, free of charge. Code can*/

/* not be incorporated into other products that are for sale. */

/* This copyright notice must remain intact when used. */

/* Code is provided as-is, without warranties. It is the */

/* user's sole responsibility to test this code for */

/* suitability in their environment. */

/* */

/* Purpose: Executes full, differential, and tran log backups */

/* */

/* Input Parameters: */

/* @.dir directory for the backup */

/* @.type backup type */

/* */

/* Output Parameters: None */

/* */

/* Return Status: 0 success */

/* -1 failure */

/* */

/* Usage: exec asp_backup 'c:\data','T' */

/* Executes a transaction log backup against each */

/* database that is in full recovery model and drops the */

/* backups in the directory c:\data */

/* */

/* Local Variables: */

/* */

/* Called By: SQL Server Agent job */

/* */

/* Calls: None */

/* */

/* Data Modifications: */

/* Inserts into the blockedprocess table */

/* */

/* Updates: */

/* Date Author Purpose */

/* 4/01/06 Mike Hotek Created */

/****************************************************************/

declare @.filename varchar(255),

@.database varchar(255),

@.recovery int,

@.command nvarchar(1000)

--Error checking

if @.dir is null

begin

print 'You must specify a directory path'

return

end

if @.type not in ('F','D','T')

begin

print 'Valid backup types are F, D, and T'

return

end

--Format directory path

if right(@.dir,1) != '\'

set @.dir = @.dir + '\'

set @.command = null

declare curdb cursor for

select name, status from master.dbo.sysdatabases where name not in ('tempdb','model')

for read only

open curdb

fetch curdb into @.database, @.recovery

while @.@.fetch_status = 0

begin

--Format filename. Functions needed to work around single digit month, day, minute, etc.

set @.filename = @.dir + @.database + '_' + cast(datepart(yyyy,getdate()) as char(4)) +

right('0' + rtrim(cast(datepart(mm,getdate()) as char(2))),2) +

right('0' + rtrim(cast(datepart(dd,getdate()) as char(2))),2) +

right('0' + rtrim(cast(datepart(hh,getdate()) as char(2))),2) +

right('0' + rtrim(cast(datepart(mi,getdate()) as char(2))),2) +

right('0' + rtrim(cast(datepart(ss,getdate()) as char(2))),2)

set @.filename = @.filename + case when @.type = 'F' then '_full.bak'

when @.type = 'D' then '_diff.bak'

else '.trn' end

--Format backup command

if @.type = 'F'

begin

set @.command = 'backup database [' + @.database + '] to disk = ''' + @.filename + ''' with init'

end

else if @.type = 'D' and @.database != 'master'

begin

set @.command = 'backup database [' + @.database + '] to disk = ''' + @.filename + ''' with differential, init'

end

else if @.type = 'T' and DATABASEPROPERTYEX(@.database, N'RECOVERY') = 'Full'

begin

set @.command = 'backup log [' + @.database + '] to disk = ''' + @.filename + ''' with init'

end

--Execute backup

if @.command is not null

begin

print @.command

exec sp_executesql @.command

end

insert into dbo.backupfiles

(DatabaseName, BackupFileName, CreationDate)

values(@.database, @.filename, getdate())

set @.command = null

fetch curdb into @.database, @.recovery

end

close curdb

deallocate curdb

GO

CREATE procedure [dbo].[asp_bakfilecleanup] @.backupretention int,

@.historyretention int

as

/****************************************************************/

/* Stored Procedure: asp_bakfilecleanup */

/* Creation Date: 4/01/2006 */

/* Written by: Mike Hotek - MHS Enterprises, Inc. */

/* http://www.mssqlserver.com */

/* Copyright: 2006 MHS Enterprises, Inc. All Rights Reserved */

/* Code can be used in its entirety, free of charge. Code can*/

/* not be incorporated into other products that are for sale. */

/* This copyright notice must remain intact when used. */

/* Code is provided as-is, without warranties. It is the */

/* user's sole responsibility to test this code for */

/* suitability in their environment. */

/* */

/* Purpose: Removes old backup files from the file system. */

/* */

/* Input Parameters: */

/* @.backupretention Number of days to retain backup files */

/* @.historyretention Number of days to retain backup history*/

/* */

/* Output Parameters: None */

/* */

/* Return Status: 0 success */

/* -1 failure */

/* */

/* Usage: exec asp_bakfilecleanup 3,15 */

/* Deletes any backup files older than 3 days and any */

/* backup history older than 15 days */

/* */

/* Local Variables: */

/* */

/* Called By: SQL Server Agent job */

/* */

/* Calls: None */

/* */

/* Data Modifications: */

/* Deletes rows from the backupfiles table */

/* */

/* Updates: */

/* Date Author Purpose */

/* 5/10/06 Mike Hotek Created */

/****************************************************************/

--This is wired to a logging table simply to avoid messy code in

--parsing file names or pulling fiel attributes to figure out which

--files to purge

declare @.file varchar(255),

@.command varchar(1000)

if @.backupretention is null

begin

print 'You must specify a retention interval'

return

end

if @.historyretention is null

begin

print 'You must specify a retention interval'

return

end

--Pull the list of files that are older than the retention

-- Construct a del command for each file and execute it.

declare curfile cursor for select BackupFileName from dbo.backupfiles

where CreationDate < dateadd(dd,-@.backupretention,getdate())

for read only

open curfile

fetch curfile into @.file

while @.@.fetch_status = 0

begin

set @.command = 'exec master.dbo.xp_cmdshell ''del ' + @.file + ''''

exec(@.command)

fetch curfile into @.file

end

--Clean up objects that were created

close curfile

deallocate curfile

delete from backupfiles

where CreationDate < dateadd(dd,-@.historyretention,getdate())

GO

Backup logs enterprise/restore standard?

We maintain a warm standby by backing up transaction logs on the production
machine, then restoring them to the warm standby. Both machines are
currently running 2000 Standard. If we upgrade the production machine to
Enterprise (we want to be able to allocate more than 2 GB memory), can we
keep restoring logs to Standard (so that we can defer the cost of upgrading
it until a later time)?
TIA
--
Scott NicholAll editions are binary on-disk compatible in SQL 2000. You can safely
restore a Standard Edition backup to an Enterprise Edition server. Your
"home-grown" log shipping will be just fine.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:uY7htr%23pFHA.3520@.tk2msftngp13.phx.gbl...
> We maintain a warm standby by backing up transaction logs on the
> production
> machine, then restoring them to the warm standby. Both machines are
> currently running 2000 Standard. If we upgrade the production machine to
> Enterprise (we want to be able to allocate more than 2 GB memory), can we
> keep restoring logs to Standard (so that we can defer the cost of
> upgrading
> it until a later time)?
> TIA
> --
> Scott Nichol
>

Backup logs enterprise/restore standard?

We maintain a warm standby by backing up transaction logs on the production
machine, then restoring them to the warm standby. Both machines are
currently running 2000 Standard. If we upgrade the production machine to
Enterprise (we want to be able to allocate more than 2 GB memory), can we
keep restoring logs to Standard (so that we can defer the cost of upgrading
it until a later time)?
TIA
Scott Nichol
All editions are binary on-disk compatible in SQL 2000. You can safely
restore a Standard Edition backup to an Enterprise Edition server. Your
"home-grown" log shipping will be just fine.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:uY7htr%23pFHA.3520@.tk2msftngp13.phx.gbl...
> We maintain a warm standby by backing up transaction logs on the
> production
> machine, then restoring them to the warm standby. Both machines are
> currently running 2000 Standard. If we upgrade the production machine to
> Enterprise (we want to be able to allocate more than 2 GB memory), can we
> keep restoring logs to Standard (so that we can defer the cost of
> upgrading
> it until a later time)?
> TIA
> --
> Scott Nichol
>

Backup logs enterprise/restore standard?

We maintain a warm standby by backing up transaction logs on the production
machine, then restoring them to the warm standby. Both machines are
currently running 2000 Standard. If we upgrade the production machine to
Enterprise (we want to be able to allocate more than 2 GB memory), can we
keep restoring logs to Standard (so that we can defer the cost of upgrading
it until a later time)?
TIA
--
Scott NicholAll editions are binary on-disk compatible in SQL 2000. You can safely
restore a Standard Edition backup to an Enterprise Edition server. Your
"home-grown" log shipping will be just fine.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:uY7htr%23pFHA.3520@.tk2msftngp13.phx.gbl...
> We maintain a warm standby by backing up transaction logs on the
> production
> machine, then restoring them to the warm standby. Both machines are
> currently running 2000 Standard. If we upgrade the production machine to
> Enterprise (we want to be able to allocate more than 2 GB memory), can we
> keep restoring logs to Standard (so that we can defer the cost of
> upgrading
> it until a later time)?
> TIA
> --
> Scott Nichol
>

Backup log, no automatic deletion of expired logs?

Hi there,
I use the following scenario-
Every day at 00:00 I backup the database:
BACKUP DATABASE c5win300sql TO c5win300sql_dat WITH FORMAT
Every half hour from 06:00 to 23:00 I backup the log:
declare @.expDate datetime
select @.expDate = dateadd(day,1,getdate())
BACKUP LOG c5win300sql TO c5win300sql_log WITH EXPIREDATE = @.expdate
From the above, you can see that I set a expiration date, RESTORE HEADERONLY
FROM c5win300sql_log confirms, that it works correctly.
However, the c5win300sql_log backup continues to grow and the expired logs d
oes
not get "deleted" from the file. How can I rectify this issue "properly"?
I doubt, therefore I might be.All that EXPIREDATE and RETAINDAYS does it that they prohibit you from overw
riting (using INIT) the
backup file before the desired date and time. It doesn't make any automatic
deletion of old backups.
In fact, you cannot delete only some backups from a backup file. It is all o
r nothing.
Maint Wiz has functionality to create new backup files with names based on d
ate and time (among
other). And Maint wiz can delete files other than x days. You can also write
similar code yourself,
but that takes some coding to do.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kim Noer" <kn@.nospam.dk> wrote in message news:uiiTdKb%23FHA.1032@.TK2MSFTNGP09.phx.gbl...[v
bcol=seagreen]
> Hi there,
> I use the following scenario-
> Every day at 00:00 I backup the database:
> BACKUP DATABASE c5win300sql TO c5win300sql_dat WITH FORMAT
> Every half hour from 06:00 to 23:00 I backup the log:
> declare @.expDate datetime
> select @.expDate = dateadd(day,1,getdate())
> BACKUP LOG c5win300sql TO c5win300sql_log WITH EXPIREDATE = @.expdate
> From the above, you can see that I set a expiration date, RESTORE HEADERON
LY FROM c5win300sql_log
> confirms, that it works correctly.
> However, the c5win300sql_log backup continues to grow and the expired logs
does not get "deleted"
> from the file. How can I rectify this issue "properly"?
> --
> I doubt, therefore I might be.[/vbcol]|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:%23yIEs7b%23FHA.600@.tk2msftngp13.phx.gbl

> Maint Wiz has functionality to create new backup files with names
> based on date and time (among other). And Maint wiz can delete files
> other than x days. You can also write similar code yourself, but that
> takes some coding to do.
Thanks for your answer. However, can I with get away with a simple 'WITH FOR
MAT'
at 06:00 (when the first log backup is being done)?
I got a 5 day tape backup and 2x 1 day disk backup, so my butt should be cov
ered
somewhat.
I doubt, therefore I might be.|||I never use FORMAT, unless I either need to overwrite a backup before EXPIRE
DATE (which never
happens as I don't use EXPIREDATE), or want to split up a media set. The nor
mal way to overwrite is
to specify INIT. As to whether you can overwrite every time, only you can de
cide based on how
covered you are (if and where you have put your old backups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kim Noer" <kn@.nospam.dk> wrote in message news:eCapq9n%23FHA.1676@.TK2MSFTNGP09.phx.gbl...[v
bcol=seagreen]
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:%23yIEs7b%23FHA.600@.tk2msftngp13.phx.gbl
>
> Thanks for your answer. However, can I with get away with a simple 'WITH F
ORMAT' at 06:00 (when
> the first log backup is being done)?
> I got a 5 day tape backup and 2x 1 day disk backup, so my butt should be c
overed somewhat.
> --
> I doubt, therefore I might be.[/vbcol]

Backup log, no automatic deletion of expired logs?

Hi there,
I use the following scenario-
Every day at 00:00 I backup the database:
BACKUP DATABASE c5win300sql TO c5win300sql_dat WITH FORMAT
Every half hour from 06:00 to 23:00 I backup the log:
declare @.expDate datetime
select @.expDate = dateadd(day,1,getdate())
BACKUP LOG c5win300sql TO c5win300sql_log WITH EXPIREDATE = @.expdate
From the above, you can see that I set a expiration date, RESTORE HEADERONLY
FROM c5win300sql_log confirms, that it works correctly.
However, the c5win300sql_log backup continues to grow and the expired logs does
not get "deleted" from the file. How can I rectify this issue "properly"?
I doubt, therefore I might be.
All that EXPIREDATE and RETAINDAYS does it that they prohibit you from overwriting (using INIT) the
backup file before the desired date and time. It doesn't make any automatic deletion of old backups.
In fact, you cannot delete only some backups from a backup file. It is all or nothing.
Maint Wiz has functionality to create new backup files with names based on date and time (among
other). And Maint wiz can delete files other than x days. You can also write similar code yourself,
but that takes some coding to do.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kim Noer" <kn@.nospam.dk> wrote in message news:uiiTdKb%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I use the following scenario-
> Every day at 00:00 I backup the database:
> BACKUP DATABASE c5win300sql TO c5win300sql_dat WITH FORMAT
> Every half hour from 06:00 to 23:00 I backup the log:
> declare @.expDate datetime
> select @.expDate = dateadd(day,1,getdate())
> BACKUP LOG c5win300sql TO c5win300sql_log WITH EXPIREDATE = @.expdate
> From the above, you can see that I set a expiration date, RESTORE HEADERONLY FROM c5win300sql_log
> confirms, that it works correctly.
> However, the c5win300sql_log backup continues to grow and the expired logs does not get "deleted"
> from the file. How can I rectify this issue "properly"?
> --
> I doubt, therefore I might be.
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:%23yIEs7b%23FHA.600@.tk2msftngp13.phx.gbl

> Maint Wiz has functionality to create new backup files with names
> based on date and time (among other). And Maint wiz can delete files
> other than x days. You can also write similar code yourself, but that
> takes some coding to do.
Thanks for your answer. However, can I with get away with a simple 'WITH FORMAT'
at 06:00 (when the first log backup is being done)?
I got a 5 day tape backup and 2x 1 day disk backup, so my butt should be covered
somewhat.
I doubt, therefore I might be.
|||I never use FORMAT, unless I either need to overwrite a backup before EXPIREDATE (which never
happens as I don't use EXPIREDATE), or want to split up a media set. The normal way to overwrite is
to specify INIT. As to whether you can overwrite every time, only you can decide based on how
covered you are (if and where you have put your old backups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kim Noer" <kn@.nospam.dk> wrote in message news:eCapq9n%23FHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:%23yIEs7b%23FHA.600@.tk2msftngp13.phx.gbl
>
> Thanks for your answer. However, can I with get away with a simple 'WITH FORMAT' at 06:00 (when
> the first log backup is being done)?
> I got a 5 day tape backup and 2x 1 day disk backup, so my butt should be covered somewhat.
> --
> I doubt, therefore I might be.

Backup log, no automatic deletion of expired logs?

Hi there,
I use the following scenario-
Every day at 00:00 I backup the database:
BACKUP DATABASE c5win300sql TO c5win300sql_dat WITH FORMAT
Every half hour from 06:00 to 23:00 I backup the log:
declare @.expDate datetime
select @.expDate = dateadd(day,1,getdate())
BACKUP LOG c5win300sql TO c5win300sql_log WITH EXPIREDATE = @.expdate
From the above, you can see that I set a expiration date, RESTORE HEADERONLY
FROM c5win300sql_log confirms, that it works correctly.
However, the c5win300sql_log backup continues to grow and the expired logs does
not get "deleted" from the file. How can I rectify this issue "properly"?
--
I doubt, therefore I might be.All that EXPIREDATE and RETAINDAYS does it that they prohibit you from overwriting (using INIT) the
backup file before the desired date and time. It doesn't make any automatic deletion of old backups.
In fact, you cannot delete only some backups from a backup file. It is all or nothing.
Maint Wiz has functionality to create new backup files with names based on date and time (among
other). And Maint wiz can delete files other than x days. You can also write similar code yourself,
but that takes some coding to do.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kim Noer" <kn@.nospam.dk> wrote in message news:uiiTdKb%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I use the following scenario-
> Every day at 00:00 I backup the database:
> BACKUP DATABASE c5win300sql TO c5win300sql_dat WITH FORMAT
> Every half hour from 06:00 to 23:00 I backup the log:
> declare @.expDate datetime
> select @.expDate = dateadd(day,1,getdate())
> BACKUP LOG c5win300sql TO c5win300sql_log WITH EXPIREDATE = @.expdate
> From the above, you can see that I set a expiration date, RESTORE HEADERONLY FROM c5win300sql_log
> confirms, that it works correctly.
> However, the c5win300sql_log backup continues to grow and the expired logs does not get "deleted"
> from the file. How can I rectify this issue "properly"?
> --
> I doubt, therefore I might be.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:%23yIEs7b%23FHA.600@.tk2msftngp13.phx.gbl
> Maint Wiz has functionality to create new backup files with names
> based on date and time (among other). And Maint wiz can delete files
> other than x days. You can also write similar code yourself, but that
> takes some coding to do.
Thanks for your answer. However, can I with get away with a simple 'WITH FORMAT'
at 06:00 (when the first log backup is being done)?
I got a 5 day tape backup and 2x 1 day disk backup, so my butt should be covered
somewhat.
--
I doubt, therefore I might be.|||I never use FORMAT, unless I either need to overwrite a backup before EXPIREDATE (which never
happens as I don't use EXPIREDATE), or want to split up a media set. The normal way to overwrite is
to specify INIT. As to whether you can overwrite every time, only you can decide based on how
covered you are (if and where you have put your old backups).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kim Noer" <kn@.nospam.dk> wrote in message news:eCapq9n%23FHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:%23yIEs7b%23FHA.600@.tk2msftngp13.phx.gbl
>> Maint Wiz has functionality to create new backup files with names
>> based on date and time (among other). And Maint wiz can delete files
>> other than x days. You can also write similar code yourself, but that
>> takes some coding to do.
> Thanks for your answer. However, can I with get away with a simple 'WITH FORMAT' at 06:00 (when
> the first log backup is being done)?
> I got a 5 day tape backup and 2x 1 day disk backup, so my butt should be covered somewhat.
> --
> I doubt, therefore I might be.

backup log files empty?

I'm using Lumigent Log Explorer to view logs of a database. But the result I
got was 'No log records found that match your selection'. I did not use any
filters.
In Enterprise Manager, View Content can list all the backups including log
backups in a backup file fine. From my understanding, no log backups would
have been made if no transactions had taken place. Is that right? Does SQL
back up logs anyway no matter whether or not there is a transaction?
Anybody want to help me understand this?
Thanks a lot.
Bing
Hi
If you tell it to make a backup, and there were no log records, SQL Server
still will makes a backup of some meta data that it always backups. LSN will
increment. You will notice a file < 50kb.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:60139E6F-5AB8-43C1-B883-0CCD515819FF@.microsoft.com...
> I'm using Lumigent Log Explorer to view logs of a database. But the
> result I
> got was 'No log records found that match your selection'. I did not use
> any
> filters.
> In Enterprise Manager, View Content can list all the backups including log
> backups in a backup file fine. From my understanding, no log backups
> would
> have been made if no transactions had taken place. Is that right? Does
> SQL
> back up logs anyway no matter whether or not there is a transaction?
> Anybody want to help me understand this?
> Thanks a lot.
> Bing
|||Ah, I see. Thanks much for the very helpful information.
Bing
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> If you tell it to make a backup, and there were no log records, SQL Server
> still will makes a backup of some meta data that it always backups. LSN will
> increment. You will notice a file < 50kb.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:60139E6F-5AB8-43C1-B883-0CCD515819FF@.microsoft.com...
>
>

backup log files empty?

I'm using Lumigent Log Explorer to view logs of a database. But the result
I
got was 'No log records found that match your selection'. I did not use any
filters.
In Enterprise Manager, View Content can list all the backups including log
backups in a backup file fine. From my understanding, no log backups would
have been made if no transactions had taken place. Is that right? Does SQL
back up logs anyway no matter whether or not there is a transaction?
Anybody want to help me understand this?
Thanks a lot.
BingHi
If you tell it to make a backup, and there were no log records, SQL Server
still will makes a backup of some meta data that it always backups. LSN will
increment. You will notice a file < 50kb.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:60139E6F-5AB8-43C1-B883-0CCD515819FF@.microsoft.com...
> I'm using Lumigent Log Explorer to view logs of a database. But the
> result I
> got was 'No log records found that match your selection'. I did not use
> any
> filters.
> In Enterprise Manager, View Content can list all the backups including log
> backups in a backup file fine. From my understanding, no log backups
> would
> have been made if no transactions had taken place. Is that right? Does
> SQL
> back up logs anyway no matter whether or not there is a transaction?
> Anybody want to help me understand this?
> Thanks a lot.
> Bing|||Ah, I see. Thanks much for the very helpful information.
Bing
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> If you tell it to make a backup, and there were no log records, SQL Server
> still will makes a backup of some meta data that it always backups. LSN wi
ll
> increment. You will notice a file < 50kb.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:60139E6F-5AB8-43C1-B883-0CCD515819FF@.microsoft.com...
>
>

backup log files empty?

I'm using Lumigent Log Explorer to view logs of a database. But the result I
got was 'No log records found that match your selection'. I did not use any
filters.
In Enterprise Manager, View Content can list all the backups including log
backups in a backup file fine. From my understanding, no log backups would
have been made if no transactions had taken place. Is that right? Does SQL
back up logs anyway no matter whether or not there is a transaction?
Anybody want to help me understand this?
Thanks a lot.
BingHi
If you tell it to make a backup, and there were no log records, SQL Server
still will makes a backup of some meta data that it always backups. LSN will
increment. You will notice a file < 50kb.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:60139E6F-5AB8-43C1-B883-0CCD515819FF@.microsoft.com...
> I'm using Lumigent Log Explorer to view logs of a database. But the
> result I
> got was 'No log records found that match your selection'. I did not use
> any
> filters.
> In Enterprise Manager, View Content can list all the backups including log
> backups in a backup file fine. From my understanding, no log backups
> would
> have been made if no transactions had taken place. Is that right? Does
> SQL
> back up logs anyway no matter whether or not there is a transaction?
> Anybody want to help me understand this?
> Thanks a lot.
> Bing|||Ah, I see. Thanks much for the very helpful information.
Bing
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> If you tell it to make a backup, and there were no log records, SQL Server
> still will makes a backup of some meta data that it always backups. LSN will
> increment. You will notice a file < 50kb.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:60139E6F-5AB8-43C1-B883-0CCD515819FF@.microsoft.com...
> > I'm using Lumigent Log Explorer to view logs of a database. But the
> > result I
> > got was 'No log records found that match your selection'. I did not use
> > any
> > filters.
> >
> > In Enterprise Manager, View Content can list all the backups including log
> > backups in a backup file fine. From my understanding, no log backups
> > would
> > have been made if no transactions had taken place. Is that right? Does
> > SQL
> > back up logs anyway no matter whether or not there is a transaction?
> >
> > Anybody want to help me understand this?
> >
> > Thanks a lot.
> >
> > Bing
>
>