Category: SQL Scripts

SQL Server – Backup Verification Script – Based on conditions

Problem: There are scenarios where you have to verify the multiple SQL Server instances backup within limited time frame whether it meet your organization backup criteria standards or not.

Solution:
You can execute below T-SQL script to check the backup status as per your organization standard by assigning backup conditions value to backup criteria variable. You can execute below script either by connecting each SQL Server Instance or execute it by registering all the SQL Server Instances in Central Management Server (CMS).

Below T-SQL Script work with all the versions of MSSQL Server and verify FULL, Differential and Transaction Log Backup. It will check the max backup date and evaluate result based on condition as backup is Passed or Not.

Backup Verification Script will return below values. Column names are self-explanatory.
• SQLInstanceName
• DatabaseName
• db_create_ddate
• DB_Recovery_Model
• dbstatus
• windowsservername
• productversion
• productlevel
• edition
• current_datetime
• last_full_backup_date
• last_diff_backup_date
• last_tran_backup_date
• days_since_last_full_backup
• days_since_last_diff_backup
• hours_since_last_tranlog_backup
• Full_Backup_Stauts
• Diff_Backup_Stauts
• Log_Backup_Stauts
• full_backup_location
• diff_backup_location
• tlog_backup_location

You have to specify the backup condition based on your organization standard by assigning values to below variables.

<br />
declare @full_backup_criteria_in_days int<br />
declare @diff_backup_criteria_in_days int<br />
declare @log_backup_criteria_in_hours int</p>
<p>-- specify the backup criteria<br />
set @full_backup_criteria_in_days = 7 -- 7 day older Full Backup is Pass<br />
set @diff_backup_criteria_in_days = 1 -- 1 day older Diff Backup is Pass<br />
set @log_backup_criteria_in_hours = 2 -- 2 hours older Log Backup is Pass<br />

Complete Backup Verification Script

<br />
declare @full_backup_criteria_in_days int<br />
declare @diff_backup_criteria_in_days int<br />
declare @log_backup_criteria_in_hours int</p>
<p>-- specify the backup criteria<br />
set @full_backup_criteria_in_days = 7 -- 7 day older Full Backup is Pass<br />
set @diff_backup_criteria_in_days = 1 -- 1 day older Diff Backup is Pass<br />
set @log_backup_criteria_in_hours = 2 -- 2 hours older Log Backup is Pass</p>
<p>select<br />
           serverproperty('servername') as SQLInstanceName,<br />
           quotename(bkup_full.[database_name]) as DatabaseName,<br />
            ( select    sdb.crdate<br />
              from      [master]..[sysdatabases] sdb<br />
              where     sdb.name = bkup_full.[database_name]<br />
            ) as [db_create_ddate],<br />
           databasepropertyex(bkup_full.[database_name],'recovery')  as DB_Recovery_Model,<br />
           databasepropertyex(bkup_full.[database_name],'status')  as dbstatus,<br />
           case serverproperty('isclustered')<br />
                        when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)<br />
                        when 0 then cast(serverproperty('machinename') as varchar)<br />
           end as windowsservername,<br />
           serverproperty('productversion') as productversion,<br />
           serverproperty('productlevel') as productlevel,<br />
           serverproperty('edition') as edition,</p>
<p>           current_timestamp as current_datetime,</p>
<p>		   bkup_full.[backup_finish_date] as [last_full_backup_date],<br />
		   bkup_diff.[backup_finish_date] as [last_diff_backup_date] ,<br />
		   bkup_log.[backup_finish_date] as [last_tran_backup_date] ,</p>
<p>           datediff(dd, bkup_full.[backup_finish_date], current_timestamp) as [days_since_last_full_backup] ,<br />
           datediff(dd, bkup_diff.[backup_finish_date], current_timestamp) as [days_since_last_diff_backup] ,<br />
           datediff(hh, bkup_log.[backup_finish_date], current_timestamp) as [hours_since_last_tranlog_backup] ,</p>
<p>           case<br />
                        when datediff(dd, bkup_full.[backup_finish_date], current_timestamp) &lt;= @full_backup_criteria_in_days<br />
                        then 'Pass'<br />
                        else 'Fail'<br />
                        end as Full_Backup_Stauts,</p>
<p>            case<br />
                        when datediff(dd, bkup_diff.[backup_finish_date], current_timestamp) &lt;= @diff_backup_criteria_in_days  then 'Pass'<br />
                        else case when quotename(bkup_full.[database_name]) IN ('[master]') then 'N/A' else 'Fail' end<br />
                        end as Diff_Backup_Stauts,</p>
<p>            case<br />
                        when datediff(hh, bkup_log.[backup_finish_date], current_timestamp) &lt;= @log_backup_criteria_in_hours  then 'Pass'<br />
                        else case when databasepropertyex(bkup_full.[database_name],'recovery') = 'SIMPLE' then 'N/A' else 'Fail' end<br />
            end as Log_Backup_Stauts,            </p>
<p>            ( select top 1 [physical_device_name]<br />
              from      [msdb]..[backupmediafamily] bkup_media_family<br />
              where     bkup_media_family.[media_set_id] = bkup_full.[media_set_id]<br />
            ) as [full_backup_location] ,<br />
            ( select top 1  [physical_device_name]<br />
              from      [msdb]..[backupmediafamily] bkup_media_family<br />
              where     bkup_media_family.[media_set_id] = bkup_diff.[media_set_id]<br />
            ) as [diff_backup_location] ,<br />
            ( select top 1  [physical_device_name]<br />
              from      [msdb]..[backupmediafamily] bkup_media_family<br />
              where     bkup_media_family.[media_set_id] = bkup_log.[media_set_id]<br />
            ) as [tlog_backup_location]</p>
<p>    from    [msdb]..[backupset] as bkup_full<br />
            left join [msdb]..[backupset] as bkup_log on bkup_log.[database_name] = bkup_full.[database_name]<br />
                                                       and bkup_log.[server_name] = bkup_full.[server_name]<br />
                                                       and bkup_log.[type] = N'L'<br />
                                                       and bkup_log.[backup_finish_date] = ( (select  max([backup_finish_date])<br />
                                                                                            from    [msdb]..[backupset] b2<br />
                                                                                            where   b2.[database_name] = bkup_full.[database_name]<br />
                                                                                                    and b2.[server_name] = bkup_full.[server_name]<br />
                                                                                                    and b2.[type] = N'L') )<br />
            left join [msdb]..[backupset] as bkup_diff on bkup_diff.[database_name] = bkup_full.[database_name]<br />
                                                       and bkup_diff.[server_name] = bkup_full.[server_name]<br />
                                                       and bkup_diff.[type] = N'I'<br />
                                                       and bkup_diff.[backup_finish_date] = ( (select max([backup_finish_date])<br />
                                                                                            from    [msdb]..[backupset] b2<br />
                                                                                            where   b2.[database_name] = bkup_full.[database_name]<br />
                                                                                                    and b2.[server_name] = bkup_full.[server_name]<br />
                                                                                                     and b2.[type] = N'I') )<br />
    where   bkup_full.[type] = N'D'<br />
            and bkup_full.[backup_finish_date] = ( (select  max([backup_finish_date])<br />
                                                 from   [msdb]..[backupset] b2<br />
                                                 where  b2.[database_name] = bkup_full.[database_name]<br />
                                                        and b2.[server_name] = bkup_full.[server_name]<br />
                                                        and b2.[type] = N'D') )<br />
            and exists ( select [name]<br />
                         from   [master]..[sysdatabases]<br />
                         where  [name] = bkup_full.[database_name] )<br />
            and bkup_full.[database_name] &lt;&gt; N'tempdb'<br />

This script is the “Sustain” dimension

A backup you haven’t verified is a hope, not a plan — and this script is exactly the kind of continuous, boring verification that keeps a data platform honest. In my AI risk framework, this is Sustain: monitor and govern, so the other controls hold up in production. The framework: R-I-S-K-S: The 5-Dimension Framework for AI Risk Management

August 29, 2014

Script to check SQL Case Sensitivity
You can execute below script to check whether SQL Server is case sensitive or not
```
SELECT
case when 'a' <> 'A' 
then 'Case-Sensitive' 
else 'case-insensitive' end  AS 'Case Sensitivity'
```
March 21, 2014

Script to get data file size, used space and free space

While troubleshooting the disk space issue, you can use below script to check the data file size, used space and free size.

select 
		DBName,
		name,
		[filename],
		size as 'Size(MB)',
		usedspace as 'UsedSpace(MB)',
		(size - usedspace) as 'AvailableFreeSpace(MB)'
from		
(	
SELECT
db_name(s.database_id) as DBName,
s.name AS [Name],
s.physical_name AS [FileName],
(s.size * CONVERT(float,8))/1024 AS [Size],
(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
) DBFileSizeInfo

February 19, 2014

Steps to recover access/reset SA Password to SQL Server Instance without downtime
Problem: There may be situation where you are unable to access the SQL Server. In this article I will guide you how you regain the access to SQL Server instance.
Below are the possible reasons why you wouldn’t be able to access the SQL Server instance
– Built in Administrator is removed
– Built in Administrator is not available
– Forgot SA password
– Forgot add the DBA account on the SQ Server instance

Most of the time to resolve this kind of issue we are restarting the SQL Server in single user mode, which requires down time, here we will follow the approach where we don’t need any downtime to get the access to SQL Server instance.

Solution:
You can follow below steps to get the access to SQL Server instance without downtime.
Step 1: Make sure you have administrator level access on windows
Step 2: Download the PSExec, PSExec utility allows you to connect the SQL Server instance under the context of NT AUTHORTY\system account, which has default sysAdmin access on SQL Server.
Step 3: Open the command prompt as administrator and type the below command, it will open SSMS and under NT AUTHORITY\SYSTEM account context and you can change password or add account etc.
```
PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"
```
Step 4: Open new query window from SSMS and either you can change SA account password or add new account.
```
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'password'
GO
```
January 6, 2014

Stripping SQL Server Database Backup to Multiple Files

Stripping Database backup to multiple files and on different drives will make the backup speed faster and will reduce the backup duration.

Check the below sample script for the backup and restore. You can perform the same task using SSMS GUI as well.
Backup Script

BACKUP DATABASE [SQLDBPool] TO  
DISK = N'C:\JSpace\Backup\SQLDBPool1.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool2.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool3.bak'
WITH NOFORMAT, 
NOINIT,  
NAME = N'SQLDBPool-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Restore Script

RESTORE DATABASE [SQLDBPool] FROM  
DISK = N'C:\JSpace\Backup\SQLDBPool1.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool2.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool3.bak'
WITH  FILE = 1,  NOUNLOAD,  STATS = 10,replace
GO

January 2, 2014