Script Out / Back Up All the Store Procedures

My lovely developers like to chanllenge my scripting skills. Today I was asked to restore one database on Dev server by using the backup taken from Prod. However, they wanted to keep all the store process in Dev. In order to avoid the store procs were removed during the dateabase restoring by prod backup,  I had to script them out and then run them after restoring completed. The following codes help me to do that in one go without repeating the same process.                                   

SET NOCOUNT ON

DECLARE @SP TABLE ( Id INT IDENTITY (1, 1),

                     Code VARCHAR (MAX) );

Who Dropped The Tables?

Recently, when I did daily checks on ETL processes, and then found one of ETLs failed on a few tables. Later on I realised those tables had been dropped somehow. However no one knew who did it, when did it and why did it. To investigate the crime, SQL Server default trace became the most useful tool, I found.

Firstly, you need to check if ‘default trace enabled’ through the following two ways.

1.      Run sp_configure

-- 'default trace enabled' only shows when 'show advanced options' is enabled

sp_configure 'show advanced options', 1

reconfigure

GO

-- check if 'default trace enabled'has 1 for both 'config_value' and 'run value'

sp_configure

   

2.      Or run the following script to check the setting

 SELECT* FROM sys.configurations WHERE configuration_id = 1568

Useful Undocumented sp_MSforeachdb/sp_MSforeachtable

Finding new stuff about SQL Server always excites me. Some SQL professionals may be aware of these two store procedures. However I just knew them a couple of months ago and I absolutely love them!

sp_MSforeachdb is undocumented store proc which presents in master database on SQL Server by default. It allows you to process a single command or multiple commands against every database on the incidence.

Sample 1 Check orphan users on all databases

EXECUTE sp_MSforeachdb @command1 = '?.dbo.sp_change_users_login ''Report''';