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) );

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''';

Who Fired Up The 'Disabled' SQL Job?

At my work, there are a lot SQL agent jobs that look like disabled or not scheduled. However, the reality is that they run every day and are fired up by the other SQL jobs on the same or different servers. On the other hand, they might also fire up other jobs when processing their job steps. When I first time troubleshooted job failures, it was hard to find the dependencies between those ‘disabled’ jobs without any workflow provided. The following T-SQL script helped me to identify job dependencies.

SELECT   j.name AS CallingJobName,

         s.step_name AS CallingStepName,

         s.step_id AS CallingStepID,

         s.command AS SQLCommand

FROM     msdb..sysjobsteps AS s

         INNER JOIN

         msdb..sysjobs AS j

         ON j.job_id = s.job_id

WHERE    s.command LIKE '%sp_start_job%'

ORDER BY CallingJobName

This is just a simple syntax. However you can get more job information by the combination of  msdb..sysjobsteps and msdb..sysjobs.

Create User Friendly Backup Failure Alter Email

At my current work, daily database backup is one of many steps in a SQL Agent Job. Because of business requirement, the job is required to continue if one or many steps fail.  However it would cause problems…The main issue in the past was that DBA wouldn’t be informed if the backup job failed, as the job would go on the next step on the failure action. Furthermore, when we re-run the backup job after failure, it is difficult to tell which databases have been backed up and which databaes are left, and we don’t want to multi backup the same databases.

Therefore, my solution is to use ‘sp_send_dbmail’ and ‘RAISERROR’ inside the Catch block of a TRY…CATCH construct.

Use Extractor Utility with Litespeed Backups

Can I restore Litespeed backups on the server that does not have Litespeed installed? The answer is ‘Yes, you can.’  Litespeed provides an Extractor.exe utility that allows extracting Litespeed backups to native SQL Server backups, so that you can restore the converted native backups on any SQL Servers without Litespeed installation required.

There is a simple example. I used ‘Exetractor.exe’ to convert a 5GB Litespeed backup to the native backup that is about 34.5 after extracting from the local disk drive (Sample: U:\MSSQL\Backup) to the backup folder on a share server (Sample: \\lofmcdfiler01\sql_backups).