Unscheduled Backups?
-
I appear to have a ghost in the machine... I run weekly full backups and daily transaction log backups using SQL Agent and a script. However, checking the logs this morning, I found a bunch of messages that started around 3:59PM yesterday and ran most of the evening. For example:
Date 5/8/2007 10:45:22 PM
Log SQL Server (Current - 5/9/2007 12:00:00 AM)
Source Backup
Message
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
Of course, my trans log backup failed,
Date 5/9/2007 12:00:01 AM
Log Job History (Backup Transaction Logs)
Step ID 1
Server Myserver\MSMSQL05
Job Name Backup Transaction Logs
Step Name Backup Logs
Duration 00:00:08
Sql Severity 16
Sql Message ID 3013
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: NT AUTHORITY\SYSTEM. BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
I know I have no maintenance plans, scheduled backups, etc. that are setup to truncate logs. The NT Admin swears he is not doing any backups on the machine either... nothing in the NT event logs, no scheduled processes, etc.
Anyone have any suggestions?
My backup log script (I am still not living in a cursor-less world!
 :SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 6
OPEN AllDatabases
DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(2000)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Statement = N'BACKUP LOG [' + @DBNameVar + N'] TO DISK = ''E:\Backups\'
+ @DBNameVar + N'_log_' + CONVERT(varchar(23),getdate(), 112) + '.bak'' WITH RETAINDAYS = 7, INIT'; -- NO Need for INIT, RDs
--Print @Statement
EXEC sp_executesql @Statement
--PRINT CHAR(13)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
END
CLOSE AllDatabases
DEALLOCATE AllDatabases
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
This caught my eye: BACKUP LOG cannot be performed because there is no current database backup.
Did a full backup fail earlier?
Greg
Greg
-
Yes... did a full backup Sunday night (scheduled). I have a script that checks the last backup date (4 days ago from today, backed up 5/6/07). The problem is with the "rogue" process running a "backup log with TRUNCATE_ONLY or with NO_LOG).
Since the DBs are in full backup mode, truncating the logs (needed to help do a proper recovery of a database to a point in time, rather than when the last full backup was done). When a log is TRUNCATED, all of the transactions cannot be recovered and a "backup log" cannot be done again until the next full DB backup is performed.
Other than sticking around after hours to see if I can catch something running, I am not sure how to try and find out where this process is coming from!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
Use SQL Profiler. Under Security Audit there is a Audit Backup/Restore Event. Will tell you, among other things, HostName from where the backup was initiated, the login name and the actual statement issued to run the backup.
-
Do you have backup s/w installed on the server? I recently realized that our copy of Backup Exec was trying to run a backup of our SQL databases nightly.
Have a good day,Norene Malaney
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply