SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 non-scheduled diffiential backup job
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vanbasten
Starting Member

Netherlands
7 Posts

Posted - 07/10/2012 :  19:03:31  Show Profile  Reply with Quote
We are running daily full backup and hourly tlog backup on this 10-databases 2K8 Standard SQL instance . The two backups are setup through Maintenance plans and scheduled/executed through two Jobs in SQL Agent. Those two backups are working fine.

However, I noticed that 1 of the 10 databases have an extra differential backup that runs periodically. This differential backup is not part of the jobs or maintenance plan that we setup. I am trying to get rid of this job but I don't know where to look. The only information I have is through the logs it shows:

2012-07-09 23:15:11.80 Backup Database differential changes were backed up. Database: db1, creation date(time): 2012/05/16(10:56:18), pages dumped: 266, first LSN: 35609:369:103, last LSN: 35609:381:1, full backup LSN: 35609:135:36, number of dump devices: 1, device information: (FILE=11, TYPE=DISK: {'E:\MSSQLSERVER INSTANCE\Backup\Db1AutomatedBackup2012-07-09.bak'}). This is an informational message. No user action is required.

We did copy this db from a Vendor (originally a 2K5 Express db) so we are not sure if any config was copied over. Any help is appreciated.

tkizer
Almighty SQL Goddess

USA
37460 Posts

Posted - 07/10/2012 :  19:34:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
If you don't see it as an Agent job, then I'd suggest running a trace to capture the information about who/what is calling it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/10/2012 :  23:59:37  Show Profile  Reply with Quote
A RESTORE HEADERONLY will tell you the user name that performed the backup operation, and name of the computer that performed the backup operation.

RESTORE HEADERONLY
FROM DISK='E:\MSSQLSERVER INSTANCE\Backup\Db1AutomatedBackup2012-07-09.bak'


It is possible that the backup is scheduled on some other computer using the windows scheduler or some other type of job scheduler, and that would help you track that down.







CODO ERGO SUM

Edited by - Michael Valentine Jones on 07/11/2012 00:04:04
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 07/11/2012 :  13:41:45  Show Profile  Visit jackv's Homepage  Reply with Quote
You can also pick up some clues from the msdb.dbo.backupset table.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

vanbasten
Starting Member

Netherlands
7 Posts

Posted - 07/11/2012 :  17:40:43  Show Profile  Reply with Quote
I tired both the restore headeronly command and looked at the msdb.backupset table. Both MachineName and ServerName show the name of the local server. The UserName shows a sql login that maps to, and have db_owner role to this database "db1". So basically the differential backup job is being run locally on the db server by a sql login every 2 hours. Is there anything else I can check to track down this job? Thanks.

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37460 Posts

Posted - 07/11/2012 :  18:00:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
And you don't see it as an Agent job? If it's not in there, then check the Windows scheduler.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 07/12/2012 :  01:56:07  Show Profile  Visit jackv's Homepage  Reply with Quote
If there are no references on the Windows Scheduler or SQL Agent, check with another Adminmistrator , that some centralised scheduler is not running , such as Autosys. That could be triggering , another script on the server

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000