October 21, 2013 at 7:01 am
Hello Masters,
I have more than 1000+ User created databases in different SQL server with, everyday windows team taking file system backup of SQL backup files. Now I have given responsiblity to know which database did not backed up.
I am looking for any query which gives me the name of database, which is not backed up Or whose backup is failing. Is it possible ?
If anyone can share such a query or Scipt to know the database name whose backup failed ?
October 21, 2013 at 7:17 am
this is a script I have based mine on when wanting similar information. SQL2000 compatible but still works on 2008.
---------------------------------------------------------------------
October 21, 2013 at 7:26 am
Thanks a lot George..!!
But my most of the SQL Server are SQL Server 2005 and SQL Server 2008.
October 21, 2013 at 7:51 am
it will still work.
If you have no SQL2000 change master.dbo.sysdatabases to master.sys.databases, and you could use the column recovery_model_desc rather than databasepropertyex, but either work.
The table names in msdb have not changed.
---------------------------------------------------------------------
October 21, 2013 at 11:13 am
You can also use Policy Based Management to look at how many days it has been since a backup was run on a database. That's a great way to double check that backups are being run. Also, your backup process should be able to alert you when backups fail. You might want to look at testing those backups too for any that are critical to the database. Just because the backup succeeded doesn't mean you'll be able to restore 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
October 22, 2013 at 9:22 am
This may be a good place to use Powershell. I have a scheduled job, that runs daily, that checks last backup date\time, and if it is older than a designated number of hours\days, I send our team an email to indicate which servers\databases have not been backed up.
Leonard
October 24, 2013 at 4:18 am
Hello Grant,
Can you please describe me in detail how to use "Policy based management" to pull report for backup failed databases. ?
October 24, 2013 at 4:45 am
If you look at Policy Based Management, it has backup monitoring built in. But there are examples online. Here's one from MSSQLTips[/url]. As far as reports goes, I'd suggest taking a look at the Enterprise Policy Management Framework[/url]. I was using this to manage a couple of hundred servers, specifically to track backups, but also to do other things you can do through PBM.
"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
October 24, 2013 at 6:05 am
Grant.. Its perfect one..! Thanks a lot.
I tried PBM on one server and see result. Now the last point, Can I automate this task ? Can I automatically export the result of this task ?
October 24, 2013 at 6:08 am
Sure. It's a part of PBM. You can make calls from Agent. Keep reading in the documentation. PBM is great.
"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
October 24, 2013 at 6:20 am
hmm I read it. It requires some powershell script that will pull evalulution output and insert into one sql table and than we can pull the report frm that table. Its seems simple but I need to learn powershell scripting .. :-D.
Let me know if you have any such script which will capture the evalution output at centralised point ( I have about 15-20 server in a domain, so will use Centralised server option to run this script at one server).
October 24, 2013 at 6:49 am
See this post, for some Powershell scripts: http://www.sqlservercentral.com/Forums/Topic1506486-3451-1.aspx.
There is a zip file attached to the last reply. It doesn't have the code you need to execute a PBM, but it may give you some additional ideas. Haven't googled it, but I would imagine there are articles on how to do that.
Leonard
October 24, 2013 at 7:40 am
The EPM does all the work for you. But the other stuff is documented on Microsoft. It's very easy. Yes, for more complete direct control you'll do PowerShell scripting, but it's not vital for the basics.
"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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply