August 31, 2012 by Mike Hillwig
Working in my environment, I’ll get the occasional odd requirement pop up. Frequently, clients will write a new version of a stored procedure and send it to us for implementation. Version control is the client’s responsibility, but we have decided that we want to take a backup of the previous version before implementing a new version.
Because we’re managing dozens of instances with a very small DBA team, this is a task that lends itself to automation. And that’s exactly what I wrote. By calling this procedure, it generates the SQL statement that produces the procedure body. And from there, I call BCP on the server to output the file.
You’ll see I’m calling a table called backup_config. This is a table I have in every instance that tells my backup script how to behave. I’m putting the output files on that same drive.
Note that you’re looking at an early version of this procedure, and it still needs a lot of error checking.
We keep xp_cmdshell turned off in our environments, so here you’ll see that I turn it on just long enough to call BCP and then turn it back off again.
Speaking of BCP, I want to thank my friend Christina Leo (blog | twitter) for her suggestion of using BCP. I was trying to use SQLCMD to generate the output file and it was truncating the output. She suggested BCP and that did the trick. And I want to thank Adam Machanic (blog | twitter) for his help in troubleshooting what we thought were some type conversion issues.
CREATE PROCEDURE procedure_backup (@p_database VARCHAR(30), @p_schema VARCHAR(30), @p_procedure VARCHAR(30)) AS DECLARE @v_code_sql VARCHAR(4000) DECLARE @v_instance VARCHAR(100) DECLARE @v_output_drive VARCHAR(1) DECLARE @v_output_path VARCHAR(50) DECLARE @v_bcp_command VARCHAR(4000) SELECT @v_instance = @@SERVERNAME WHERE @@SERVICENAME = 'MSSQLSERVER' SELECT @v_instance = @@SERVICENAME WHERE @v_instance IS NULL SELECT @v_output_drive = fullbackup_drive FROM dba.dbo.backup_config SELECT @v_output_path = @v_output_drive + ':\export\' + @v_instance + '\' SELECT @v_code_sql = 'SELECT m.definition FROM ' + @p_database + '.sys.schemas s JOIN ' + @p_database + '.sys.procedures p ON s.schema_id = p.schema_id JOIN ' + @p_database + '.sys.sql_modules m ON p.object_id = m.object_id where s.name = ''' + @p_schema + ''' and p.name = ''' + @p_procedure + '''' SELECT @v_bcp_command = 'BCP "' + @v_code_sql + '" queryout ' + @v_output_path + @p_database + '_' + @p_schema + '_' + @p_procedure + '_' + replace(convert(varchar, getdate(),111),'/','') + replace(convert(varchar, getdate(),108),':','')+'.txt -S' + @@servername + -T -c' EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE EXEC xp_cmdshell @v_bcp_command EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE