As a secondary precaution to doing regular SQL backups on our SQL Server, I'd like to be able export all stored procedures, functions and triggers to a file. That way, in case of something truly catastrophic, we at least have all of our code stored separately. I know that I can generate these scripts manually through SSMS and its export wizard. However since our code can potentially change quite often, I'd like to be able set up a nightly job to export all procedures to a .sql file so that we'd always have the most recent copies.
I've done some searching and can't seem to find or figure out a way to automate this. Any help would be much appreciated.
You can get the procedure definitions from a query against sys.sql_modules, and export the query results using BCP. But yeah, source control would be nicer. You could of course export via bcp and add a few git commands to push the output file.
Here I am joining sql_modules to procedures to limit the query only to stored procedure definitions to demonstrate the technique, but you could get other modules as well if you wanted. And of course you can add other filters... by schema, whatever.
bcp "select definition from sys.sql_modules m join sys.procedures p on p.object_id = m.object_id" queryout f:\procs.txt -SMyServer -dMyDatabase -T -c
This command will use integrated security (-T). You can use SQL auth instead. the -c is a shortcut for "character data with tab as the column separator (irrelevant here since we're only getting a single column) and \r\n as the row separator"
Stick that in a sql agent job with cmdexec as the step type, schedule as desired.