capnhector (11/13/2012)
Nice DDL Trigger on the create database however you can avoid the cross join to sys.databases by using the following query:Makes no difference in the long run im just personally a fan of shortening code.
Ah yes of cousre. That's just lazy coding on my part. The cross join is a left over from a previous query where I generated the original backup schedule for all existing user databases.
capnhector (11/13/2012)
As far as the stored procedure goes, most people around here cringe when they see DECLARE CURSOR however i think those who do will also agree this is one of the few cases where you have to use the cursor. The other thing i may look into is a TRY CATCH block for error handling so if one backup fails it does not take out the rest and you can log the failure into a table or have an email alert generated by using something like sp_send_dbmail. It also looks like you went and read Jeff Moden's article on tally tables, if you did not find it here is the link http://www.sqlservercentral.com/articles/T-SQL/62867/ Its a really good read.The one thing i would suggest though is to move the backup schedule out of the master database and into a utility or tools database. again personal pet peve of any sort of user generated data in the system databases.
Yup... all valid points. Will make the suggested changes.
Thanks very much for your input.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn