February 8, 2011 at 7:32 am
Hi,
On 1 of my sql server 2005, there are 5 databases. I wanted to check out the frequency of backup of all the databases on the server. I saw all the maintenance plans and jobs for backups of a particular database but couldn't see its backup plan.
So i thought it was not backed up.
Then, when i saw the database properties, it says last backup time, as last night 2:22 am..
I couldnt find where and what job was doing hte backup of tht particular database.
Is there a way to find out who's doing the backup of that database.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 8, 2011 at 7:52 am
When i run the query to find out the last backup time of all databases, i can see that, physical device name as
DBName_00__8b079130_1ff6_4712_9a8e_c5c8f8a1448c_
where the last backup was done.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 8, 2011 at 8:04 am
A backup can be run from anywhere. You can run one from the command line in sqlcmd, you can run them from Query Analyzer, and most often, it's stored as a command inside of SQL Agent and scheduled from there. That's where you should start looking to find out what is being run and how. No guarantees it'll be there, but that's a start. If you didn't set this up, who did? Can you ask them if they set up backups?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2011 at 8:09 am
can it be a tape backup ??
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 8, 2011 at 8:17 am
It can be, but I thought you were more interested in what was running the backup process so you can get some control & understanding.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2011 at 8:27 am
I couldn't get wat you meant by command inside a sql agent ???
I am the only guy who does the backup so im pretty sure that the backups are not done from SSMS..
The only thing is that, it can be a tape backup which the network guy does/scheduled every night
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 8, 2011 at 8:59 am
SKYBVI (2/8/2011)
I couldn't get wat you meant by command inside a sql agent ???I am the only guy who does the backup so im pretty sure that the backups are not done from SSMS..
The only thing is that, it can be a tape backup which the network guy does/scheduled every night
Regards,
Sushant
No, you can't backup databases that way. That's a file level backup. SQL Server just doesn't support it. In fact, SQL Server locks the files and prevents that style of backup from occurring. You have to run a backup from SQL Server using 'BACKUP DATABASE' commands (unless you've got a third party solution such as Red Gate SQL Backup or SQL Hyperbac, or you're using SAN technology to do low-level transactional aware snapshots of the disks). What I'm saying is, you can run that command from lots of sources, bu the most common source is from a job within SQL Agent. That's just how most people set it up. That's why I'm suggesting that you take a look at SQL Agent to see what jobs you have and if any of them are running your backups.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2011 at 9:05 am
ok, i got it ...
but
thats wat i said at hte starting that i chekced all the maintennace plans and jobs , but no backup was done to that database.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 8, 2011 at 9:18 am
SKYBVI (2/8/2011)
I couldn't get wat you meant by command inside a sql agent ???I am the only guy who does the backup so im pretty sure that the backups are not done from SSMS..
The only thing is that, it can be a tape backup which the network guy does/scheduled every night
Regards,
Sushant
The network guy could be backing up the databases. If he's using something like BackupExec's SQL agent, then he could backup SQL databases using the "backup database" command. We use it here to back up our 1.2 T database directly to tape because we don't have enough free disk space. The other databases are "normal" backups, backed up locally with Redgate or native SQL and scheduled through SQL Agent.
February 8, 2011 at 9:26 am
Check this article from SQL Server Magazine. It shows how you can query msdb tables to see backup information. It won't tell you how the call was made, but it will tell you if the database was backed up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2011 at 7:37 pm
Is there a way to find out who's doing the backup of that database.
You could set up a Profiler trace and filter on all commands containing the BACKUP command. That will tell you who, when, from where, and possibly what application was used to back up the database.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
February 9, 2011 at 6:09 am
profiler trace will trace from now, i mean i can start it wen i come to work,
but wat about the backups being done in night...
how to know who,when and wich application did it in the night.
Regards
Sushant Kumar
MCTS,MCP
February 9, 2011 at 6:48 am
SKYBVI (2/9/2011)
profiler trace will trace from now, i mean i can start it wen i come to work,but wat about the backups being done in night...
how to know who,when and wich application did it in the night.
Sounds like you're running the Profiler GUI. Don't run that against your productoin system. It can cause problems. Instead, use the gui to create a script and create a server-side trace. That can run all night long. You'll just have lots of data to deal with. Here's an article on how to set it up[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 9, 2011 at 6:54 am
Thanks Grant
I want to know how come profiler GUI can harm wen we run it on prod systems.
Regards,
Regards
Sushant Kumar
MCTS,MCP
February 9, 2011 at 7:26 am
It has to do with how the GUI collects data. Here's a good article on it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply