Are Your Database Backups Current and Available?
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tlarock/areyourdatabasebackupscurrentandavailable.asp
----------------------
https://thomaslarock.com -
I have a similar process which runs a sproc on each server which performs a LOAD HEADERONLY on each dump device which populates a central db with details of the backups for each server and database. A website and email notification lets me know if there are any databases without current backups on disk.
It's been working well for several years.
-
anyone got a job that will: 1. create a test database; 2. restore a backup into the test database; 3. verify that the restore succeeded; 4. delete the test database.
Now that would be a good test of current/available backups!
-
Thomas LaRock, you are my new hero!
Artificial Intelligence stands no chance against Natural Stupidity.
-
I can't seem to find either the download or DBA_Verify_File_Exists procedure.
-
Although I am new at this, it didn't take long to get this set up on my workstation, and its great!. The question I have is, how do I get this to check all of the servers, or do I just have to set it up on each server?
N Lytle
NLytle -
Where can I find the zip files that contains the store procs? Thanks.
-
My apologies!!!!
The code is now linked at the bottom of the article.
-
But as I understand it, in SQL2K5 they are really restricting xp_cmdshell. Or has that changed?
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Thanks Mom, always nice to hear from you.
----------------------
https://thomaslarock.com -
This was designed to be distributed, so yes you would have to push this out to each of your servers.
It could be modified to run from a central location, as well as to generate only one email report each morning. But for something like this my preference is to have it reside on each server, and generate separate emails. I guess I prefer to have an email from each server in my inbox, so that i don't lose track of something during the day.
We have some reports that are centrally located, and some that are distributed. Perhaps for my next article I will include something that is done centrally instead.
----------------------
https://thomaslarock.com -
In SS2K5 they have truned it off by default. You can turn it back on rather easily, and lock it down to specific users as well.
I know, it is not the preferred method, and I am looking to figure out a way to do this using SQLCMD in the future. If I get the time to put it all together, I'll put together another article as well.
----------------------
https://thomaslarock.com -
Voxinforma:
I use a script to automatically find the lastest full backup, copy the file to a different server and restore the database. I am using LiteSpeed, so I would need to create another script for native restores to post it for those interested.
Great job, Tom!!!
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Thanks Kathi!
As for your script, why not pass a parameter (say, @use_native = 'Y'), and have the code issue a native backup command, otherwise you would call the litespeed xp proc.
We use litespeed here as well, but we often have a need to ship a native backup to a vendor, so we ended up adding a parameter to our procs to handle the different dumps.
----------------------
https://thomaslarock.com -
That's a good idea. I'll think about doing that. Thought, I might want to write an article about how to get started writing your own scripts and include it as part of the article.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply