Monday 20 July 2015

SQL Server Security Bulletin MS15-058 Released

A SQL Server security bulletin (MS15-058) was released yesterday (14-Jul-2015).
This update resolves vulnerabilities in Microsoft SQL Server that could allow remote code execution if an authenticated attacker runs a specially crafted query that is designed to execute a virtual function from a wrong address. This leads to a function call to uninitialized memory.
It applies to:
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014

Tuesday 14 July 2015

Standardizing DBMAIL Profile & Account Setup for Notification

Copy the script to SSMS
Change the parameter values mentioned in the procedure argument lists
Run the procedure.
Once its successfully setup, you'll receive an alert.
 
USE [db_maint]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------------------------------------------------------
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'usp_dbMAilAccount')
                    AND type =  N'P')

DROP PROCEDURE [usp_dbMAilAccount];
GO
---------------------------------------------------------------------------------------------------------------------
Create Proc usp_dbMAilAccount (@profile_name nvarchar(400) = 'DBMail_Global_Profile', --newly added.
                                  @account_name nvarchar(400) = 'DBMail_Account', --newly added.
                                 -- @email_address nvarchar(400) = 'MSSQL_DB_Mail@mail.com',
                                  @display_name nvarchar(400) = 'DBMail',
                                  @description nvarchar(100) = 'Database Mail Account',
                                  @mailserver_name nvarchar(400) = 'mail_server_name',
                                  @mailserver_type nvarchar(100) = 'SMTP',
                                  @replyto_address nvarchar(100) = NULL,
                                  @port INT = 25,
                                  @recipients nvarchar(500) = 'youremail@mail.com',
                                  --@recipients nvarchar(500) = 'xyz@mail.com',
                                  @Option nvarchar(5) = 'C')

as
--------------------------------------------------------------------------------------------------------------------
/*                   
                    Purpose: The purpose of this procedure is to create / setup/ delete/ update database mail account.
            
                             First it will cleanup all existing accounts and followed by setting up the fresh accounts.
----------------------------------------------------------------------------------------------------------------------
                    **RUN :
                            -- To Create the DB mail account
                            EXEC usp_dbMAilAccount @recipients = 'xyz@mail.com'

                            -- To Delete the DB mail account
                            EXEC usp_dbMAilAccount @Option ='C'
----------------------------------------------------------------------------------------------------------

Important Procedures: 1. sysmail_add_profileaccount_sp
                      2. sysmail_help_principalprofile_sp
                      3. sp_send_dbmail
----------------------------------------------------------------------------------------------------------
EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
---------------------------------------------------------------------------------------------------------------------

Key Procedures to Note & Remember:
1. sysmail_add_account_sp
2. sysmail_add_profile_sp
3. sysmail_add_profileaccount_sp
4. sysmail_add_principalprofile_sp
5. sp_send_dbmail
6. sysmail_delete_profileaccount_sp
7. sysmail_delete_profile_sp
8. sysmail_delete_account_sp
------------------------------------------------------------------------
--Profiles
SELECT * FROM msdb.dbo.sysmail_profile
------------------------------------------------------------------------------
--Accounts
SELECT * FROM msdb.dbo.sysmail_account
------------------------------------------------------------------------------
--Profile Accounts
select * from msdb.dbo.sysmail_profileaccount
------------------------------------------------------------------------------
--Principal Profile
select * from msdb.dbo.sysmail_principalprofile
------------------------------------------------------------------------------
--Mail Server
SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_servertype
SELECT * FROM msdb.dbo.sysmail_configuration
------------------------------------------------------------------------------
--Email Sent Status
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_faileditems
--------------------------------------------------------------------------------------------------------------------------------
--Email Status
SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',       fail.mailitem_id,       LOG.descriptionFROM msdb.dbo.sysmail_event_log LOG
join msdb.dbo.sysmail_faileditems fail ON fail.mailitem_id = LOG.mailitem_id WHERE event_type = 'error'
--------------------------------------------------------------------------------------------------------------------------------
 --Mail Queues
EXEC msdb.dbo.sysmail_help_queue_sp
--------------------------------------------------------------------------------------------------------------------------------
--DB Mail Status
EXEC msdb.dbo.sysmail_help_status_sp
--------------------------------------------------------------------------------------------------------------------------------------   
Key Queries:

select * from msdb.dbo.sysmail_profileaccount
select * from msdb.dbo.sysmail_profile
select * from msdb.dbo.sysmail_account

--SELECT * FROM msdb.dbo.sysmail_profileaccount pa
--      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

----------------------------------------------------------------------------------------------------------------------------------------------
-- Delete Unsent Emails.
 SELECT * FROM msdb.dbo.sysmail_event_log;
-- To get number of unsent emails
select count(*) from msdb.dbo.sysmail_unsentitems;
-- remove all the unsent emails
delete from msdb.dbo.sysmail_unsentitems;

----------------------------------------------------------------------------------------------------------------------------------------------
-- Delete failed alerts older than 2 days.
DECLARE @Date datetime
SELECT @Date = DateAdd(dd,-2,Getdate())
SELECT @Date
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @Date , @sent_status = 'failed'
------------------------------------------------------------------------------------------------------------------------------------------
-- Here is how we will delete all events in the log older than two days
DECLARE @Date datetime
SELECT @Date = DateAdd(dd,-2,Getdate())

EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @Date
----------------------------------------------------------------------------------------------------------------------------------------------
*/
BEGIN
BEGIN TRY
SET NOCOUNT ON

--Is db mail enabled for use?
IF NOT EXISTS (SELECT value_in_use
               FROM sys.configurations
               WHERE name = 'Database Mail XPs'
               AND value_in_use = 1)
BEGIN
   EXEC ('sp_configure ''show advanced options'', 1')
   RECONFIGURE;
   EXEC ('sp_configure ''Database Mail XPs'', 1')
   RECONFIGURE;
   EXEC ('sp_configure ''show advanced options'', 0')
   RECONFIGURE;
END
----------------------------------------------------------------------------------------------------------------------------
-- Generate logic to build account name.
declare @servername sysname = (select @@servername);
--declare @account_name nvarchar(1200) = 'DBMail' ;
-- declare @profile_name nvarchar(1200);
declare @email_address nvarchar(1200)= 'DBMAIL';

--DBMail_LVDB0001_SPE
--select @backup_path
          DECLARE @len   INT,
                  @i     INT,
                  @Left  nVARCHAR(4000),
                  @right nVARCHAR(4000),
                  @full_string nVARCHAR(4000);

          --SET @SERVERNAME = (SELECT @@servername);
          --select @backup_path AS [TLOG Backup PATH]
          --PRINT @backup_path
          IF @SERVERNAME LIKE '%\%'
            BEGIN
                SET @Left= (SELECT LEFT(@SERVERNAME, Charindex('\', @SERVERNAME)
                                                     - 1))
                --SELECT @Left AS [DEFAULT SERVER NAME]
                SET @len = (SELECT Len(@SERVERNAME))
                --SELECT @LEN
                SET @right = (SELECT Substring(@SERVERNAME,
                                     Charindex ('\', @SERVERNAME) +
                                     1
                                     , @len))
             --SELECT @right AS [SQL INSTANCE NAME]
             SET @full_string = @Left + '_'+ @right;
                --PRint @full_string
            END
          ELSE
            BEGIN
                SELECT @left = @SERVERNAME;
                SET @full_string = @left;
                --PRint @full_string

            END

-- Build the final account name.
SET @email_address = @email_address + '_'+ @full_string+'@mail.com';
--Print @email_address

SET @display_name = @display_name + '_' + @full_string;
--Print @display_name

---------------------------------------------------------------------------------------------------------------------
--declare @ProfileID INT;

----Does the profile exist already?
--SELECT @ProfileID = p.profile_id
----SELECT p.name, *
--FROM msdb.dbo.sysmail_profileaccount pa
--JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--full outer JOIN msdb.dbo.sysmail_account a ON a.account_id=pa.account_id
--WHERE p.name = @profile_name;
---------------------------------------------------------------------------------------------------------------------
--SELECT * FROM msdb.dbo.sysmail_profileaccount pa
--      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

----------------------------------------------------------------------------------------------------------------------
-- Clean up the unnecesassary db accounts and profiles. We need one global profile to send email alerts. [Mandatory Step]

--IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_profileaccount pa       JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE       p.name = @ProfileName AND
--      a.name = @AccountName)
--BEGIN
--      PRINT 'Deleting Profile Account'
--      EXECUTE sysmail_delete_profileaccount_sp       @profile_name = @ProfileName,       @account_name = @AccountName
--END
-- IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @ProfileName)
--BEGIN
--      PRINT 'Deleting Profile.'      EXECUTE sysmail_delete_profile_sp       @profile_name = @ProfileName
--END
-- IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @AccountName)
--BEGIN
--      PRINT 'Deleting Account.'        EXECUTE sysmail_delete_account_sp       @account_name = @AccountName
--END

---------------------------------------------------------------------------------------------------------------------------------------
select * from msdb.dbo.sysmail_profile
select * from msdb..sysmail_profileaccount
select * from msdb.dbo.sysmail_account
---------------------------------------------------------------------------------------------------------------------------------------

create table #temp2 (profile_id INT, account_id INT, Profile_name nvarchar(1000), account_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))

INSERT INTO #temp2
SELECT p.profile_id, a.account_id, p.name,a.name, a.display_name,a.last_mod_datetime,a.last_mod_user
    FROM msdb.dbo.sysmail_profileaccount pa
      full outer JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
     full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

--select * from #temp2

declare @profile_id3 INT, @Profile_name3 nvarchar(1000),@account_name3 nvarchar(1000);

select @profile_id3 = profile_id from #temp2;

select @Profile_name3 = Profile_name from #temp2;

select @account_name3 = account_name from  #temp2;

IF ((@profile_id3 IS NULL) and (@Profile_name3 IS NULL))
BEGIN
 
        DELETE FROM msdb.dbo.sysmail_account
        WHERE name = @account_name3

  --   PRINT 'Deleting Account, when there''s NO Profile Available (i.e. PRofile_ID and Profile_NAme Both NULL) := ' + @account_name3;
     --EXECUTE msdb..sysmail_delete_account_sp       @account_name = @account_name3;  

--declare @cmd2 nvarchar(4000)
--if not exists ( select d.is_encrypted, d.name, k.* from sys.dm_database_encryption_keys k inner join sys.databases d on d.database_id = k.database_id
--                where d.name = @db_nm and is_encrypted = 1)
--begin
--    select @cmd2 = 'USE ' + quotename(@db_nm) + ';' + CHAR(13)+
--        'CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE ' + quotename(@cert_nm) + ';'
  
--  Print @cmd2
--   EXEC sp_executesql @cmd2;
--END
--ELSE
--BEGIN
-- PRINT 'DATABASE ENCRYPTION KEY Already Exists Against the Database:= ' + @db_nm
--END

END
--select * from #temp2

drop table #temp2
------------------------------------------------------------------------------------------------------------------------------------------
create table #temp (profile_id INT, account_id INT, Profile_name nvarchar(1000), account_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))

INSERT INTO #temp
SELECT p.profile_id, a.account_id, p.name,a.name, a.display_name,a.last_mod_datetime,a.last_mod_user
    FROM msdb.dbo.sysmail_profileaccount pa
      full outer JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
     full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
--SELECT p.profile_id, a.account_id, p.name as 'Profile_name' ,a.name as 'account_name', a.display_name,a.last_mod_datetime,a.last_mod_user
--    FROM msdb.dbo.sysmail_profileaccount pa
--      inner JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--     full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
    --where p.profile_id IS NOT NULL and a.account_id IS NOT NULL;

--select * from #temp

---------------------------------------------------------------------------------------------------------------------------------------
declare @profile_id1 INT, @account_id1 INT, @Profile_name1 nvarchar(1000), @account_name1 nvarchar(1000);

DECLARE Cur1 CURSOR FOR
SELECT profile_id, account_id , Profile_name , account_name FROM #temp

OPEN Cur1;
FETCH NEXT FROM Cur1 INTO @profile_id1 ,  @account_id1, @Profile_name1, @account_name1;

WHILE @@FETCH_STATUS = 0
   BEGIN
   
     --print @profile_id1
     --print @account_id1
---------------------------------------------------------------------------------------------------------------------------------------
--IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_profileaccount pa       JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE       p.name = @ProfileName AND
--      a.name = @AccountName)
--BEGIN
--      PRINT 'Deleting Profile Account'
--      EXECUTE sysmail_delete_profileaccount_sp       @profile_name = @ProfileName,       @account_name = @AccountName
--END
-- IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @ProfileName)
--BEGIN
--      PRINT 'Deleting Profile.'      EXECUTE sysmail_delete_profile_sp       @profile_name = @ProfileName
--END
-- IF EXISTS(
--SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @AccountName)
--BEGIN
--      PRINT 'Deleting Account.'        EXECUTE sysmail_delete_account_sp       @account_name = @AccountName
--END
---------------------------------------------------------------------------------------------------------------------------------------
     --Delete from msdb.dbo.sysmail_profileaccount
     --where profile_id = @profile_id1 and account_id = @account_id1;

      IF EXISTS(
        SELECT * FROM msdb.dbo.sysmail_profileaccount pa       JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
              JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE       p.name = @Profile_name1 AND
              a.name = @account_name1)
        BEGIN

      PRINT 'Deleting Profile Account:= ' + @Profile_name1;
      EXECUTE msdb..sysmail_delete_profileaccount_sp   @profile_name = @Profile_name1,  @account_name = @account_name1;

      END
 
     --DELETE FROM msdb.dbo.sysmail_profile
     --where profile_id = @profile_id

       IF EXISTS(
        SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @Profile_name1)
        BEGIN

      PRINT 'Deleting Profile:= ' + @Profile_name1   
      EXECUTE msdb..sysmail_delete_profile_sp       @profile_name = @Profile_name1;
      END

     --DELETE FROM msdb.dbo.sysmail_account
     --WHERE account_id  = @account_id1;

    IF EXISTS(
    SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @account_name1)
    BEGIN

     PRINT 'Deleting Account:= ' + @account_name1     
     EXECUTE msdb..sysmail_delete_account_sp       @account_name = @account_name1;
     END

FETCH NEXT FROM Cur1 INTO @profile_id1 ,  @account_id1, @Profile_name1, @account_name1;
   END;

CLOSE Cur1;
DEALLOCATE Cur1;

drop table #temp
---------------------------------------------------------------------------------------------------------------------
create table #temp1 (profile_id INT, account_id INT, Profile_name nvarchar(1000), account_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))

INSERT INTO #temp1
SELECT p.profile_id, a.account_id, p.name,a.name, a.display_name,a.last_mod_datetime,a.last_mod_user
    FROM msdb.dbo.sysmail_profileaccount pa
      full outer JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
     full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

--select * from #temp1

/*
declare @profile_id2 INT, @Profile_name2 nvarchar(1000);

select @profile_id2 = profile_id from #temp1;

select @Profile_name2 = Profile_name from #temp1;

IF (@profile_id2 IS NULL) and (@Profile_name2 IS NULL)
BEGIN
 
     PRINT 'Deleting Account, when there''s NO Profile Available (i.e. PRofile_ID and Profile_NAme Both NULL) := ' + @account_name1     
     EXECUTE msdb..sysmail_delete_account_sp       @account_name = @account_name1;  
END

*/

drop table #temp1
-----------------------------------------------------------------------------------------------------------------------------
IF @Option = 'C'
BEGIN

IF exists (select * from msdb.dbo.sysmail_account where name = @account_name)
BEGIN
PRint 'Database Mail Account ' + quotename (@account_name) + ' is already setup in the Server:= ' + quotename(@servername);
Print 'NOT Allowed to Create DB Mail Account Multiple Time.'

--select * from msdb.dbo.sysmail_account;

--select * from msdb.dbo.sysmail_account where name = @account_name;

END

ELSE IF NOT exists (select * from msdb.dbo.sysmail_account where name = @account_name)
BEGIN
PRINT 'Creating Account: ' + @account_name;

--select * from msdb.dbo.sysmail_account where name = @account_name
-----------------------------------------------------------------------------------------------------------------------------------------
-- To setup database mail.

-- Steps1:  EXECUTE msdb.dbo.sysmail_add_profile_sp
-- step 2:  EXECUTE msdb.dbo.sysmail_update_account_sp
-- Step 3:  EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
-- Step 4:  EXECUTE msdb.dbo.sysmail_add_account_sp
-- Step 5:  EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
-----------------------------------------------------------------------------------------------

--EXECUTE msdb.dbo.sysmail_add_profile_sp
--       @profile_name = @profile_name,
--       @description = @description;

-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<              >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- STEP 1 Create Email Account in SQL Server:
-----------------------------------------------------------------------------------------------------------------
-- Setup mail account
declare @account_id int
execute msdb.dbo.sysmail_add_account_sp
@account_name= @account_name,
@email_address= @email_address,
@display_name= @display_name,
@description = @description,
@mailserver_name= @mailserver_name,
@mailserver_type = @mailserver_type,
@port = @port,
@username=null,
@password=null,
@enable_ssl=0,
--@username=null,
--@password=null,
@use_default_credentials=1,
@account_id=@account_id output;
---------------------------------------------------------------------------------------------------------------------
--select @account_id
--select * from msdb.dbo.sysmail_account

------------------------------------------------------------------------------
-- to update the above details to add user_name, pswd = NULL, use the updte script.
--declare @account_id int
execute msdb.dbo.sysmail_update_account_sp
@account_id= @account_id, -- Pass the correct account ID.
@account_name=@account_name,
@email_address=@email_address,
@display_name=@display_name,
@description=@description,
@replyto_address= @replyto_address,
@mailserver_name=@mailserver_name,
@mailserver_type=@mailserver_type,
@port=@port,
@username=null,
@password=null,
@use_default_credentials=1,
@enable_ssl=0;
------------------------------------------------------------------------------
-- STEP 2 Creat Email Profile in SQL Server:
-- CREATE PROFILE

SET @description = 'Database Mail Profile';
EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = @profile_name,
       @description = @description;
------------------------------------------------------------------------------
-- Set the New Profile as the Default
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = @profile_name,
    @principal_name = 'public',
    @is_default = 1 ;
---------------------------------------------------------------------------------------------------------------------
-- STEP 3 Link email account to email profile:
-- LINK ACCOUNT TOPROFILE
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = 1
---------------------------------------------------------------------------------------------------------------------
PRint 'Database Mail Account Has been Setup ' + quotename (@account_name) + ' is already setup in the Server:= ' + quotename(@servername);
---------------------------------------------------------------------------------------------------------------------------------------

--Create primary profile; we always want a profile for DBOPS team.
--IF @ProfileID IS NULL
--BEGIN
--      PRINT 'Creating profile: ' + @account_name;
--      EXEC msdb.dbo.sysmail_add_profile_sp
--            @profile_name = @account_name,
--            @description = @description,
--            @profile_id = @ProfileID output

--Print @ProfileID
---------------------------------------------------------------------------------------------------

   --IF (@profile_id IS NOT NULL) -- use id
   --BEGIN
      --SELECT @profileid = profile_id FROM msdb.dbo.sysmail_profile WHERE profile_id=@profile_id
      --IF (@profileid IS NULL) -- id is invalid
      --BEGIN
      --   RAISERROR(14606, -1, -1, 'profile')
      --   RETURN(3)
      --END    

-- SET @ProfileName = (select name from msdb..sysmail_profile where profile_id = @cnt)

--SET @Sub = 'Testing Email From ' + quotename(@Server) + ' from profile : ' + @ProfileName

--declare @Profile_Name1 nvarchar(400);
/*
SELECT @profileid = profile_id FROM msdb.dbo.sysmail_profile WHERE name like '%DBMail_%';

select @Profile_Name = Name FROM msdb.dbo.sysmail_profile WHERE name like '%DBMail_%';

 IF (@Profile_Name1 is not null)
 BEGIN

       Print @profileid;
      Print @Profile_Name

      -- give everybody access to use this profile and make it the default
      EXEC msdb.dbo.sysmail_add_principalprofile_sp
            @profile_id = @profileid,
            @principal_name = 'public',
            @is_default = 1

END
*/
---------------------------------------------------------------------------------------------------------------------------------------
END

/*
ELSE IF @Option = 'D'
BEGIN
Print 'delete'
IF exists (select * from msdb.dbo.sysmail_account where name = @account_name)
BEGIN

print 'db mail account ' + quotename (@account_name) + ' exists. Can be Deleted.'
-- Delete the mail account:=
--delete from msdb.dbo.sysmail_account
--where name = @account_name;

    Print @profile_id
    Print @account_id1
    Print @account_name;

     Delete from msdb.dbo.sysmail_profileaccount
     where profile_id = @profile_id and account_id = @account_id1;

      --PRINT 'Deleting Profile.'      EXECUTE msdb..sysmail_delete_profile_sp       @profile_name = @Profile_name;

     DELETE FROM msdb.dbo.sysmail_profile
     where profile_id = @profile_id

     --PRINT 'Deleting Account.'        EXECUTE msdb..sysmail_delete_account_sp       @account_name = @account_name1;

     DELETE FROM msdb.dbo.sysmail_account
     WHERE account_id  = @account_name;


print quotename (@account_name) + ' is successfully Deleted From the Server:= ' + quotename(@servername);

END


ELSE IF NOT exists (select * from msdb.dbo.sysmail_account where name = @account_name)
BEGIN


Print 'db mail account ' + quotename (@account_name) + ' deleted already From the Server:= ' + quotename(@servername);
Print 'Can''t be deleted again.'
END
END
*/
---------------------------------------------------------------------------------------------------------------------
--- Clean Up All Other DB Mail Accounts Other than Server DB Mail Account.

select * from msdb.dbo.sysmail_profileaccount
select * from msdb.dbo.sysmail_profile
select * from msdb.dbo.sysmail_account
---------------------------------------------------------------------------------------------------------------------
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
---------------------------------------------------------------------------------------------------------------------
/*
IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE
      p.name != @account_name AND
      a.name != @account_name)
BEGIN

--declare @count INT = 1, @tot INT;

--SET @tot = (SELECT count(*) FROM msdb.dbo.sysmail_profileaccount pa
--      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
--      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
--WHERE
--      p.name != @account_name AND
--      a.name != @account_name)
------------------------------------------------------------------------------------------------------

create table #temp (profile_id INT, account_id INT, Profile_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))

INSERT INTO #temp
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id


select * from #temp

--WHILE(@count <= @tot)
 --BEGIN
      PRINT 'Deleting Profile Account';
     
--   IF (@profileid IS NOT NULL AND @accountid IS NOT NULL) -- both parameters supplied for deletion

      DELETE FROM msdb.dbo.sysmail_profileaccount

      WHERE profile_id=@profileid AND account_id=@accountid

      select * from msdb.dbo.sysmail_profileaccount;


      select * from msdb.dbo.sysmail_profile

   --ELSE IF (@profileid IS NOT NULL) -- profile id is supplied

   --   DELETE FROM msdb.dbo.sysmail_profileaccount

   --   WHERE profile_id=@profileid



   --ELSE IF (@accountid IS NOT NULL) -- account id is supplied

   --   DELETE FROM msdb.dbo.sysmail_profileaccount

   --   WHERE account_id=@accountid



   --ELSE -- no parameters are supplied for deletion

   --BEGIN

   --   RAISERROR(14608, -1, -1, 'profile', 'account') 

   --   RETURN(3)  

   --END



      --EXECUTE msdb..sysmail_delete_profileaccount_sp
      --@profile_name = @account_name,
      --@account_name = @account_name
--END
------------------------------------------------------------------------------------------------------
IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profile p
WHERE p.name = @account_name)
BEGIN
      PRINT 'Deleting Profile.'
     
     DELETE FROM msdb.dbo.sysmail_profile
     WHERE name ! = @account_name

      --EXECUTE msdb..sysmail_delete_profile_sp
      --@profile_name = @account_name
END

IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_account a
WHERE a.name = @account_name)
BEGIN
      PRINT 'Deleting Account.'

         DELETE FROM msdb.dbo.sysmail_account

        WHERE name != @account_name


      --EXECUTE msdb..sysmail_delete_account_sp
      --@account_name = @account_name
END

--END
END
*/
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Test whether the dbmail account has been setup correctly. It should have one entry, so recipient should receive only a single email alert from each server.

DECLARE
 @Total int
 ,@cnt int
 ,@ProfileName nvarchar(1000)
 --,@AccountName nvarchar(4000)
 ,@Server varchar(500)
 ,@Sub nvarchar(4000)
 ,@body nvarchar(4000),
 @cnt_Accounts INT;

SET @Total = (SELECT MAX(profile_id) FROM msdb..sysmail_profile)
 SET @cnt = 1

--SET @cnt_Accounts = (select count(*) from msdb..sysmail_account)

--DECLARE @Table TABLE (account_name varchar(400),account_id INT)

--INSERT INTO @TABLE
--select name, account_id from msdb..sysmail_account
--Order by Account_id

--select * from @TABLE

DECLARE @tableHTML  NVARCHAR(4000) ;
-------------------------------------------------------
--select * from msdb.dbo.sysmail_profileaccount
--select * from msdb.dbo.sysmail_profile
--select * from msdb.dbo.sysmail_account
------------------------------------------------------

SET @tableHTML =
    N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd)    { background-color:#eee; }
tr:nth-child(even)    { background-color:#fff; }   
</style>'
+


    N'<H1><body><font color = red> Global Mail Profile & Accounts Configured (Server :  ' + @full_string + ')' + ' </font></H1>' + '<BR>' + '<BR>' +
   
    N'<table id="box-table"  border="1">' +
    N'<tr><th>Profile_ID</th>'+
    N'<th>Profile_Name</th>'+
    N'<th>Profile_Description</th>'+
    N'<th>Last_Mod_Datetime</th>'+
    N'<th>Last_Mod_User</th></tr>' +
    CAST ( ( SELECT td = profile_id, '',
                    td = name, '',
                    td = [description], '',
                    td = convert(varchar(19), [last_mod_datetime],121) , '',
                    td = [last_mod_user], ''
              FROM msdb.dbo.sysmail_profile
              ORDER BY profile_id ASC
             FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
        N'</table>'

        +
    '<BR>' + '<BR>' + '<BR>' +
    N'<table id="box-table"  border="1">' +
    N'<tr><th>Account_ID</th>'+
    N'<th>Account_Name</th>'+
    N'<th>Account_Description</th>'+
    N'<th>Email_Address</th>'+
    N'<th>Display_Name</th>'+
    N'<th>Last_Mod_Datetime</th>' +
    N'<th>Last_Mod_User</th></tr>' +
 CAST ( ( SELECT td = account_id, '',
                    td = name, '',
                    td = [description], '',
                    td = email_address, '',
                    td = display_name, '' ,
                    td = convert(varchar(19), [last_mod_datetime],121) , '',
                    td = last_mod_user, ''
              FROM msdb.dbo.sysmail_account
              ORDER BY account_id ASC
             FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>';

select @tableHTML;


------------------------------------------------------------------------------------------------------
SET @Server = @@SERVERNAME

WHILE(@cnt <= @Total)
 BEGIN
 SET @ProfileName = (select name from msdb..sysmail_profile where profile_id = @cnt)

 --SET @AccountName = (select * from msdb..sysmail_account where @account_id = @cnt)

SET @Sub = 'Testing Email From ' + quotename(@Server) + ' from profile : ' + @ProfileName;

 IF (@ProfileName is not null)
 BEGIN

--Print '------------------------------'
--PRINT @ProfileName
--Print @AccountName
--PRINT @Sub
--Print '------------------------------'

----------------------------------------------------------------------------------------------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
   @recipients = @recipients
  , @subject = @Sub
  , @body = @tableHTML
  , @profile_name = @ProfileName
 , @body_format = 'HTML' ;
 END

SET @cnt = @cnt + 1
END

END
------------------------------------------------------------------------------------------------------------------------------------
SET NOCOUNT OFF
END try

    BEGIN catch
        DECLARE @ErrorNumber INT;
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;
        DECLARE @ErrorLine INT;
        DECLARE @ErrorProcedure NVARCHAR(4000);
        DECLARE @ErrorMessage NVARCHAR(4000);

        SELECT @ErrorNumber = Error_number(),
               @ErrorSeverity = Error_severity(),
               @ErrorState = Error_state(),
               @ErrorLine = Error_line(),
               @ErrorProcedure = Error_procedure();

        SELECT @ErrorMessage =
               N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
               + 'Message: ' + Error_message();

        SELECT @ErrorMessage AS [Error_Message];

        SELECT @ErrorProcedure AS [Error_Procedure];

        PRINT 'Error '
              + CONVERT(VARCHAR(50), Error_number())
              + ', Severity '
              + CONVERT(VARCHAR(5), Error_severity())
              + ', State '
              + CONVERT(VARCHAR(5), Error_state())
              + ', Procedure '
              + Isnull(Error_procedure(), '-') + ', Line '
              + CONVERT(VARCHAR(5), Error_line());

        PRINT Error_message();
    END catch

    SET nocount OFF
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
 

Disk Free Space Check

Create the table "Capacity_DiskSpaceTracking". Script is available in the commented section.
Build the procedure
Run it:
      exec [usp_Disk_Free_Space_Check]
     @dbmail_profile= 'Operators', 
     @dbmail_recipient = 'youremail@domain.com' ,
     @deleteEntry = 360; -- No Of Days!
 
 
USE [DB_Maint]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID(N'[dbo].[usp_Disk_Free_Space_Check]') IS NOT NULL
BEGIN
  DROP PROCEDURE [dbo].[usp_Disk_Free_Space_Check]
END
GO

CREATE PROC [dbo].[usp_Disk_Free_Space_Check] (@dbmail_profile sysname = NULL, @dbmail_recipient sysname = NULL, @deleteEntry int = 360)

AS
BEGIN
  SET NOCOUNT ON
  BEGIN TRY


    /*                          
                          Desc:    1. Calcualates each drive's growth rate and report them as Alerts.
                                2. If free disk space <=30, it will throw alert.
                                3. If free disk space <=20, it will throw alert.
                                4. If free disk space <=10, it will throw WARNING!! alert.
           
                          RUNNING Instructions :=
                                  exec [usp_Disk_Free_Space_Check]
                                  @dbmail_profile= 'Operators',
                                  @dbmail_recipient = 'youremail@domain.com' ,
                                  @deleteEntry = 360; -- No Of Days!
    */
   
   
    /*           
    -----------------------------------------------------------
    Step 1: Create the table.
   
    USE [DB_Maint]
    GO
   
    SET ANSI_NULLS ON
    GO
   
    SET QUOTED_IDENTIFIER ON
    GO
   
    SET ANSI_PADDING ON
    GO
   
    CREATE TABLE [dbo].[Capacity_DiskSpaceTracking](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Drive] varchar(10) NOT NULL,
        [ServerName] nvarchar(4000) NULL,
        [Free(MB)] varchar(1000) NULL,
        [Total(MB)] varchar(1000) NULL,
        [Free(%)] varchar(1000) NULL,
        [Date_Entered] datetime NULL,
        [login_sname] [sysname] NOT NULL DEFAULT (suser_sname())
    ) ON [PRIMARY]
   
    GO
   
    SET ANSI_PADDING OFF
    GO
   
                           
                           
    */

    -- xp_fixeddrives


    DECLARE @hr int
    DECLARE @fso int
    DECLARE @drive char(1)
    DECLARE @odrive int
    DECLARE @TotalSize varchar(20)
    DECLARE @MB bigint;

    SET @MB = 1048576 -- 1 GB

    CREATE TABLE #drives (
      drive char(1) PRIMARY KEY,
      FreeSpace int NULL,
      TotalSize int NULL
    )
    INSERT #drives (drive, FreeSpace)
    EXEC master.dbo.xp_fixeddrives
    EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',
                           @fso OUT
    IF @hr <> 0
      EXEC sp_OAGetErrorInfo @fso
    DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR
    SELECT
      drive
    FROM #drives
    ORDER BY drive
    OPEN dcur
    FETCH NEXT FROM dcur INTO @drive
    WHILE @@FETCH_STATUS = 0
    BEGIN
      EXEC @hr = sp_OAMethod @fso,
                             'GetDrive',
                             @odrive OUT,
                             @drive
      IF @hr <> 0
        EXEC sp_OAGetErrorInfo @fso
      EXEC @hr = sp_OAGetProperty @odrive,
                                  'TotalSize',
                                  @TotalSize OUT
      IF @hr <> 0
        EXEC sp_OAGetErrorInfo @odrive

      UPDATE #drives
      SET TotalSize = @TotalSize / @MB
      WHERE drive = @drive
      FETCH NEXT FROM dcur INTO @drive
    END

    CLOSE dcur
    DEALLOCATE dcur

    EXEC @hr = sp_OADestroy @fso
    IF @hr <> 0
      EXEC sp_OAGetErrorInfo @fso

    SELECT
      @@servername AS ServerName,
      drive,
      FreeSpace AS 'Free(MB)',
      TotalSize AS 'Total(MB)',
      CAST((FreeSpace / (TotalSize * 1.0)) * 100.0 AS int) AS 'Free(%)',
      GETDATE() AS Date_Entered INTO #result_set
    FROM #drives

    -- Print Intermediate Results
    --select * from #result_set


    INSERT INTO [DB_Maint].[DBO].[Capacity_DiskSpaceTracking] ([ServerName],
    [drive],
    [Free(MB)],
    [Total(MB)],
    [Free(%)],
    [Date_Entered])
      SELECT
        ServerName,
        drive,
        [Free(MB)],
        [Total(MB)],
        [Free(%)],
        Date_Entered
      FROM #result_set


    --INSERT INTO Capacity_DiskSpaceTracking
    --(DriveLetter, Label, FreeSpaceMB,UsedSpaceMB,TotalSpaceMB,FreeSpacePercentage)
    --SELECT DriveLetter
    --    , Label
    --    , FreeSpace
    --    , (TotalSpace - FreeSpace) AS [UsedSpace MB]
    --    , TotalSpace
    --    , ((CONVERT(NUMERIC(9,2),FreeSpace) / CONVERT(NUMERIC(9,2),TotalSpace)) * 100) AS [Percentage Free]
    --FROM ##_DriveInfo
    --ORDER BY [DriveLetter] ASC   


    -- Display Results.

    SELECT
      *
    FROM [DB_Maint].[DBO].[Capacity_DiskSpaceTracking]


    DECLARE @servername nvarchar(100),
            @drive1 nvarchar(2),
            @freeMB int,
            @totalMB int,
            @free int,
            @date_entered nvarchar(50)

    DECLARE db_crsr_T CURSOR FOR
    SELECT
      [ServerName],
      [drive],
      [Free(MB)],
      [Total(MB)],
      [Free(%)],
      [Date_Entered]
    FROM #result_set

    OPEN db_crsr_T
    FETCH NEXT FROM db_crsr_T INTO @servername, @drive1, @FreeMB, @totalMB, @free, @date_entered
    WHILE @@fetch_status = 0
    BEGIN

      IF @free < 30
        AND @free > 10
      BEGIN
        DECLARE @msg1 nvarchar(1000),
                @subject nvarchar(4000)

        SELECT
          @subject = 'DISK SPACE ALERT !! ' + '  ' + SUBSTRING(@@servername, 1, 20) + ' Drive has < 30% Free Space '
        -- + ' ' + 'in Database: ' + @DBNAME;


        --declare @body1 nvarchar(max)
        SET @msg1 = N'<H1> <Font Color = "red"> DRIVE FREE SPACE CHECK: </font> </H1>' +
        N'<body><H3> <Font Color = "red"> Server: ' + @@servername + '</H3></font>' +
        '<BR>' + '<BR>';

        SET @msg1 = @msg1 + 'Instance ' + RTRIM(@servername) + ' only has ' + CONVERT(nvarchar(9), @freeMB) + ' MB free on disk ' + CONVERT(char(1), @drive1) + ':\. The percentage free is ' + CONVERT(nvarchar(3), @free) + '. Drive ' + CONVERT(char(1), @drive1) + ':\ has a total size of ' + LTRIM(CONVERT(nvarchar(10), @totalMB)) + ' MB and ' + CONVERT(nvarchar(9), @freeMB) + ' MB free.'
        EXEC msdb.dbo.sp_send_dbmail @profile_name = @dbmail_profile, -- CHANGE THIS TO YOUR SERVERS MAIL PROFILE NAME...
                                     @recipients = @dbmail_recipient, --CHANGE THIS TO YOUR EMAIL ADDRESS...
                                     @body = @msg1,
                                     @subject = @subject,
                                     @body_format = 'HTML',
                                     @importance = 'HIGH';
      END

      IF @free < 10
      BEGIN
        DECLARE @msg2 nvarchar(1000),
                @subject2 nvarchar(4000);

        SELECT
          @subject2 = 'DISK SPACE ALERT !! ' + '  ' + SUBSTRING(@@servername, 1, 20) + ' Drive has < 10% Free Space ';

        SET @msg2 = N'<H1> <Font Color = "red"> DRIVE FREE SPACE CHECK:  WARNING !!! One Or more Drive has <10% free space !!! </font> </H1>' +
        N'<body><H3> <Font Color = "red"> Server: ' + @@servername + '</H3></font>' +
        '<BR>' + '<BR>';

        SET @msg2 = @msg2 + '<Font Color = "red">WARNING!! </font> Instance ' + RTRIM(@servername) + ' only has ' + CONVERT(nvarchar(9), @freeMB) + ' MB free on disk ' + CONVERT(char(1), @drive1) + ':\. The percentage free is ' + CONVERT(nvarchar(3), @free) + '. Drive ' + CONVERT(char(1), @drive1) + ':\ has a total size of ' + LTRIM(CONVERT(nvarchar(10), @totalMB)) + ' MB and ' + CONVERT(nvarchar(9), @freeMB) + ' MB free.'
        EXEC msdb.dbo.sp_send_dbmail @profile_name = @dbmail_profile,  -- CHANGE THIS TO YOUR SERVERS MAIL PROFILE NAME...
                                     @recipients = @dbmail_recipient, --CHANGE THIS TO YOUR EMAIL ADDRESS...
                                     @body = @msg2,
                                     @subject = @subject2,
                                     @body_format = 'HTML',
                                     @importance = 'HIGH';
      END

      FETCH NEXT FROM db_crsr_T INTO @servername, @drive1, @FreeMB, @totalMB, @free, @date_entered
    END

    CLOSE db_crsr_T
    DEALLOCATE db_crsr_T


    DROP TABLE #drives
    DROP TABLE #result_set

    -- Cleanup from physical table.

    DECLARE @NumRecords varchar(20)

    PRINT @deleteEntry;

    -- Retain records for 60 days Old. Keep @deleteEntry = 60
    DELETE FROM [DB_Maint].[DBO].[Capacity_DiskSpaceTracking]
    WHERE [Date_Entered] <= (GETDATE() - @deleteEntry)

    SELECT
      @NumRecords = @@ROWCOUNT

    SELECT
      @NumRecords

    IF @NumRecords > 0
    BEGIN
      --DELETE FROM Capacity_DiskSpaceTracking WHERE DATEDIFF(DAY, TimeCollected, GETDATE()) > @deleteEntry
      DBCC CHECKIDENT ([DB_Maint.dbo.Capacity_DiskSpaceTracking], RESEED, 0);
    END

  END TRY
  BEGIN CATCH
    DECLARE @ErrorNumber int;
    DECLARE @ErrorSeverity int;
    DECLARE @ErrorState int;
    DECLARE @ErrorLine int;
    DECLARE @ErrorProcedure nvarchar(4000);
    DECLARE @ErrorMessage nvarchar(4000);

    SELECT
      @ErrorNumber = ERROR_NUMBER(),
      @ErrorSeverity = ERROR_SEVERITY(),
      @ErrorState = ERROR_STATE(),
      @ErrorLine = ERROR_LINE(),
      @ErrorProcedure = ERROR_PROCEDURE();

    SELECT
      @ErrorMessage =
      N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
      + 'Message: ' + ERROR_MESSAGE();

    SELECT
      @ErrorMessage AS [Error_Message];

    SELECT
      @ErrorProcedure AS [Error_Procedure];

    PRINT 'Error '
    + CONVERT(varchar(50), ERROR_NUMBER())
    + ', Severity '
    + CONVERT(varchar(5), ERROR_SEVERITY())
    + ', State '
    + CONVERT(varchar(5), ERROR_STATE())
    + ', Procedure '
    + ISNULL(ERROR_PROCEDURE(), '-') + ', Line '
    + CONVERT(varchar(5), ERROR_LINE());

    PRINT ERROR_MESSAGE();
  END CATCH

  SET NOCOUNT OFF
END

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

Script to estimate the Size of log backups

We have an environment where we constantly need to estimate the size of log backups(.Trn file size). I use this script to achieve it.  

create table #dbcclogspace([Database Name] nvarchar(200),[Log Size (MB)] float,[Log Space Used (%)] float,[Status] int)
     insert into #dbcclogspace([Database Name],[Log Size (MB)],[Log Space Used (%)],[Status])
      exec('DBCC sqlperf(logspace)')
     select [Database Name],([Log Space Used (%)]*[Log Size (MB)])/100 as [LogBackupSize in MB],(([Log Space Used (%)]*[Log Size (MB)])/100)/1024 as [LogBackupSize in GB] from #dbcclogspace
     drop table #dbcclogspace

Move database files to another Drive

We had a couple of times the demand to transfer SQL related files from C:\ drive to D:\ drive because system backups need them to be elsewhere and the person who installed SQL Server (and the databases) was not DBA.
Here is the way I recommand to run the script if you are not used to use it :
  1. Copy-Paste the code of the script in a connection to the SQL Server instance of your choice
  2. Modify the #CHANGEME to 1 so that the script does not change anything on the server
  3. Execute the script
  4. Copy the result in another window and run the commands
We ran it a couple of times and it seems to be working as expected.
Hope this helps.
 
-- https://msdn.microsoft.com/en-us/library/ms345483.aspx
-- https://msdn.microsoft.com/fr-be/library/ms345408%28v=sql.105%29.aspx
-- OK for SQL Server 2008 R2 Express Edition
/*
!!! REPLACE #CHANGEME by 1 if just to test and by 0 if execution !!!
*/

-- ---------------------------------------------------------------------------------------------------
PRINT 'Setting user databases offline'
-- ---------------------------------------------------------------------------------------------------

DECLARE @JustTest BIT = #CHANGEME
DECLARE GetDDL CURSOR FOR
    select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET OFFLINE' from master.sys.databases
    where name not in ('master','tempdb','model','msdb') -- system databases cannot be set offline !

DECLARE @tsql NVARCHAR(MAX);   
OPEN GetDDL ;

fetch next from GetDDL into @tsql;

WHILE @@FETCH_STATUS = 0
BEGIN
    if(@JustTest = 1)
    BEGIN
        PRINT @tsql ;
    END
    ELSE
    BEGIN
        exec sp_executesql @tsql ;
    END
    fetch next from GetDDL into @tsql;
END

CLOSE GetDDL
DEALLOCATE GetDDL
GO

   
PRINT 'Move data and log files to D:\ drive'

DECLARE @JustTest BIT = #CHANGEME
DECLARE GetDDL CURSOR FOR
    select
        'ALTER DATABASE '
            + QUOTENAME(DB_NAME(database_id)) +   
        ' modify FILE ( NAME = ' + name + ', FILENAME=''' + REPLACE(physical_name,'C:\','D:\') + ''')' as DDLs
    from master.sys.master_files
    where DB_NAME(database_id) <> 'master' ; -- master needs a setting at service startup level
   
DECLARE @tsql NVARCHAR(MAX);   
OPEN GetDDL ;

fetch next from GetDDL into @tsql;

WHILE @@FETCH_STATUS = 0
BEGIN
    if(@JustTest = 1)
    BEGIN
        PRINT @tsql ;
    END
    ELSE
    BEGIN
        exec sp_executesql @tsql ;
    END
    fetch next from GetDDL into @tsql;
END

CLOSE GetDDL
DEALLOCATE GetDDL
GO    


-- ---------------------------------------------------------------------------------------------------
PRINT 'YOU MUST COPY FILES TO NEW LOCATION then execute the following commands'
PRINT '!!! If an error occurs when restarting => adjust file permission to allow SQL Service account to access those files'
-- ---------------------------------------------------------------------------------------------------

PRINT 'Bringing user databases ONLINE'

DECLARE @JustTest BIT = #CHANGEME
DECLARE GetDDL CURSOR FOR
    select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET ONLINE' from master.sys.databases
    where name <> 'master'

DECLARE @tsql NVARCHAR(MAX);   
OPEN GetDDL ;

fetch next from GetDDL into @tsql;

WHILE @@FETCH_STATUS = 0
BEGIN
    if(@JustTest = 1)
    BEGIN
        PRINT @tsql ;
    END
    ELSE
    BEGIN
        exec sp_executesql @tsql ;
    END
    fetch next from GetDDL into @tsql;
END

CLOSE GetDDL
DEALLOCATE GetDDL
GO

if(exists(select * from master.sys.master_files where DB_NAME(database_id) = 'master' and SUBSTRING(physical_name,0,4) = 'C:\'))
BEGIN
    PRINT '!!!!! MASTER Database needs to be moved too !!!!'
    PRINT 'Here is the procedure : '
    PRINT ''
    PRINT '1) Open SQL Server Configuration Manager'
    PRINT '2) On "SQL Server Services" tab, right-click on the service related to the instance you need to take care of'
    PRINT '3) Choose "Properties" in the pop-up menu'
    PRINT '4) Go to advanced settings and edit startup parameters'
    PRINT '5) Edit at least "-d"(master.mdf) and "-l" (master.ldf) parameters. Optionnally also edit "-e" (errorlog) option'
    PRINT '   Example : -dD:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eD:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lD:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf'
    PRINT '6) Stop SQL Server services'
    PRINT '7) Move master.mdf and master.ldf to new location'
    PRINT '8) Restart database engine'
    PRINT '9) Check all is OK'
END

PRINT 'Just to be sure : Restart SQL Server instance (and dependant services)'

Delete Backup Files From Default Backup Location

Copy the script to SSMS
Create the procedure
Run it
(Note: If required change the retention days)
RUN:
EXEC [usp_retention_backup]
 
 
USE [db_maint]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'usp_retention_backup')
                    AND type =  N'P')

DROP PROCEDURE [usp_retention_backup];
GO

--EXEC sp_configure 'xp_cmdshell', 1

--GO

--RECONFIGURE

--GO


Create PROC [dbo].[usp_retention_backup]

(@days AS VARCHAR(4) = 3 -- Pass number of days

)



/*******************************************************************************************************

**    DESCRIPTION:    DELETE BACKUP FILES FROM DEFAULT BACKUP LOCATION

**    WRITTEN BY:        SM

**    DATE:            13/03/2015



***

1.    No more hard code of the backup location. Instead it will extract the backup location from registry.

2.  If in the registry the backupdirectory is not created, it will throw

3.  Pass the right parameters which signifies the days before which you want to delete the backup files.

    For E.g. if @days = 3, it will delete all the backup files which are 3 days older than today's back.

             if @days = 0, the procedure will delete all backup files from the backup location.



4. It will tell you the count of the backup files deleted from the location by running this script.

5. If you want to retain any of the backup files from being removed, please change the extension of the backup files from

   ".BAK" to ".XXX" (XXX = Any other name)

******************************************************************************************************



** RUNNING THE PROCEDURE INSTRUCTIONS... **



*/

AS

SET NOCOUNT ON

BEGIN

--BEGIN TRY



DECLARE @backup_path nvarchar(2048);

DECLARE @backupfile nvarchar(1000);

DECLARE @BackupDirectory NVARCHAR(2048);

--DECLARE @days AS VARCHAR(2) -- days for retention

DECLARE @path AS VARCHAR(128) -- the path for deletion

DECLARE @cmd AS VARCHAR(512) -- the actually command

DECLARE @currentDateTime datetime;

DECLARE @filename nvarchar(256);

DECLARE @return_value INT;

DECLARE @return_value1 INT;

DECLARE @counter int;



--DECLARE @min INT;

--SET @currentDateTime = GetDate();



EXEC @return_value =

                        MASTER..XP_INSTANCE_REGREAD @rootkey = 'HKEY_LOCAL_MACHINE', 

                        @key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER',

                        @value_name = 'BackupDirectory', @BackupDirectory = @BackupDirectory OUTPUT ; 

                        SET @backup_path = (SELECT @BackupDirectory AS [SQL Server default backup Value])

                        select @backup_path as "Backup Path"

                        --SELECT @backup_path

                        -- If the backup path has a "\" at the end, then remove it, as it will not work to delete files per the syntax of forfiles.
                        IF (SELECT RIGHT(@backup_path,1) ) ='\'

                        BEGIN
                        SET @backup_path = LEFT(@backup_path, LEN(@backup_path) - 1)

                        END
            ELSE
            select @backup_path as "Backup Path"

IF @return_value <> 0 -- It's a failure

BEGIN

    PRINT 'Unable to retrieve a valid Backup directory from Registry'

   

    --RETURN(1) --Exits unconditionally from a query or procedure

    RETURN @@ERROR

END



-- Query to check number of backup files in the location.



IF OBJECT_ID('#DirOutput') IS NOT NULL

DROP TABLE #DirOutput;



DECLARE @cmd1 nvarchar(500),

        @count1 INT;



SET @cmd1 = 'dir ' +@backup_path+' /A:A'

--PRINT @cmd1

CREATE TABLE #DirOutput(

     files varchar(500))



INSERT INTO #DirOutput

EXEC master.dbo.xp_cmdshell @cmd1



SELECT @count1 = COUNT(*)

FROM #DirOutput

WHERE files LIKE '[0-9][0-9]/%'



SELECT @count1 'Number of Backup Files before delete'

DROP TABLE #DirOutput



--SET @days = '3' -- change the days here, remember it is type VARCHAR

--SET @days  = @days;

SET @cmd = 'forfiles /P "' + @backup_path + '" /s /m *.bak /d -' + @days + ' /c "cmd /c del @path"'


-- forfiles /P "I:\BACKUP\UAT\" /S /M *.bak /D -3 /C "cmd /c del @PATH"

--Print @cmd

EXEC    @return_value1 = master.dbo.xp_cmdshell @cmd

--PRINT @return_value1

 IF @return_value1=0

 BEGIN

--SELECT @@ROWCOUNT AS DELETED;  

--PRINT @backup_path

SELECT 'BACKUP FILES ARE SUCCESSFULLY DROPPED FROM SERVER: ' + @@SERVERNAME;



-- Query to check number of backup files in the location.

--PRINT @backup_path

IF OBJECT_ID('#DirOutput10') IS NOT NULL

DROP TABLE #DirOutput10

DECLARE @cmd10 nvarchar(500),

        @count10 INT,

        @files10 INT;



SET @cmd10 = 'dir ' +@backup_path+' /A:A'

--PRINT @cmd10

--PRINT @cmd1

CREATE TABLE #DirOutput10(

     files varchar(500))



INSERT INTO #DirOutput10

EXEC master.dbo.xp_cmdshell @cmd10



SELECT @count10 = COUNT(*)

FROM #DirOutput10

WHERE files LIKE '[0-9][0-9]/%'



SET @files10= @count1 - @count10;

SELECT @files10 'Number of Backup Deleted';

DROP TABLE #DirOutput10



END

ELSE

RETURN @@ERROR

SET NOCOUNT OFF
END

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Recent Restore History

Simply execute on your instance to receive the most recent info about any database restores that have taken place on the server.

SET NOCOUNT ON
SELECT
     destination_database_name
    ,bmf.physical_device_name
    ,restore_date   
FROM        msdb.dbo.restorehistory
INNER JOIN    msdb.dbo.backupset            as bs    ON bs.backup_set_id    = msdb.dbo.restorehistory.backup_set_id
INNER JOIN    msdb.dbo.backupmediafamily    as bmf    ON bs.media_set_id    = bmf.media_set_id
WHERE restore_history_id IN
    (
    SELECT MAX(restore_history_id)
    FROM msdb.dbo.restorehistory
    WHERE restore_type = 'D'
    AND destination_database_name IN
        (
        SELECT DISTINCT destination_database_name
        FROM msdb.dbo.restorehistory
        )
    GROUP BY destination_database_name
    )
ORDER BY restore_date DESC
SET NOCOUNT OFF

List schemas in a database

Run the script to see which schemas are available in a database.
[SchemaName] = the schema's name
[SchemaOwner] = authorization specified at creation
[Source] is either User, System, or Fixed Role
 
-- Works on MS SQL Server 2005+
SELECT
    a.name As [SchemaName]    -- The schema's name
    , b.name As [SchemaOwner]    -- Set at creation
    , CASE
        WHEN a.schema_id <5 THEN 'SYSTEM'
        WHEN a.schema_id >16000 THEN 'FIXED ROLE'
        ELSE 'User'
    END As [Source]    -- User or system?
FROM sys.schemas a
    INNER JOIN sys.schemas b
        ON a.principal_id = b.schema_id
ORDER BY
    [Source] DESC    -- Sort 'user' schemas to the top
 

Check Overdue Databases Backup List For Multiple SQL Servers

We can use this SQL Script to get Overdue databases list which have not been backed-up from last 24 hrs.
Note: We can change the Rotation in this SQL Script.
We can use this SQL Script as input in SQLCMD/OSQL command as:
Put Overdue database SQL query into "C:\temp_rahul\Overdue.sql"
-S = Server Name here is "local\sql"
-U = User Name here is SA
-d = Database Name here is master
-i = Input SQL file here as "C:\temp_rahul\Overdue.sql"
-n = Print path here as C:\temp_rahul\Overdue.txt
osql -Slocal\sql -Usa -Ppass@#123 -dmaster  -i "C:\temp_rahul\Overdue.sql"-w180 -n >> "C:\temp_rahul\Overdue.txt"

DECLARE    @li_rowcount int
DECLARE @ldt_timelimit    datetime
DECLARE    @lvc_msg varchar(2000)
DECLARE @hidden varchar(50)

SET NOCOUNT ON
SET ROWCOUNT 0


SET ANSI_WARNINGS OFF

-- Create and populate temp table with most recent full backup date


create table #tempexc (CustID varchar (150),Rotation int, DOW int )
if exists (select * from master.dbo.sysobjects where id = object_id(N'[master].[dbo].[_OverdueBackups_Exceptions]') )
begin
  insert into #tempExc (CustID,Rotation,DOW) (select * from [master].[dbo].[_OverdueBackups_Exceptions])
  select @hidden = ' * '
end
else
  select @hidden = '   '
insert into #tempexc (CustID,[Rotation],[DOW]) values ('pubs',0,0)
insert into #tempexc (CustID,[Rotation],[DOW]) values ('tempdb',0,0)
insert into #tempexc (CustID,[Rotation],[DOW]) values ('Northwind',0,0)


SELECT    sdb.name, Max(backup_start_date) AS backup_start_date, 0 Rotation
INTO    #OverdueBackups
FROM    master.sys.databases sdb LEFT OUTER JOIN
    msdb.dbo.backupset bs on bs.database_name = sdb.name
WHERE    (bs.type ='D' OR bs.type ='I' OR bs.type IS NULL) AND state_desc IN ('ONLINE') AND replica_id is null
GROUP BY sdb.name
ORDER BY sdb.name

-- Remove anything with a Rotation of 0 i.e. to be ignored
delete #OverdueBackups
where name in  (select  CustID from #tempexc where Rotation = 0)

-- Set default Rotation = 24hrs (We can change it)
update #OverdueBackups
set #OverdueBackups.Rotation = -24

--Set Rotation period of any exceptions
update #OverdueBackups
set #OverdueBackups.Rotation = #tempexc.Rotation
from  #tempexc
where name =  CustID and #tempexc.Rotation < 0
-- Remove databases backed up within the time limit
-- Note Rotations defined as Negative values

DELETE    #OverdueBackups
WHERE    backup_start_date > DateAdd( hh, Rotation, GetDate() )
-- Anything left is overDue

PRINT CHAR(13)+CHAR(10)
SELECT    @li_rowcount = COUNT(name) FROM    #OverdueBackups

-- If any rows left
IF @li_rowcount > 0
  BEGIN
   PRINT @@Servername + @hidden+ space(25- len(@@Servername)) + + CHAR(9) +
        ' - SOME or ALL BACKUPS OVERDUE (NOTE: Take backup of databases wherever required.)'  +
         CHAR(13)+CHAR(10)
    SELECT CAST ('' + name + '''' AS char(100)) AS '                    Overdue backups',
        CASE WHEN backup_start_date IS NULL THEN '-- Never backed up'
                                            ELSE '-- '+CAST(backup_start_date AS varchar(19))
        END  AS '   Last Full backup', Rotation as [Rotation period]
    FROM #OverdueBackups
    ORDER BY CASE WHEN backup_start_date IS NULL THEN '2' ELSE '1' END, name
  END
ELSE
  PRINT @@Servername + SPACE(26- len(@@Servername)) + ' - ALL OK' +@hidden+ CHAR(13)+CHAR(10)


DROP TABLE #tempexc
DROP TABLE #OverdueBackups
GO
 

Monitor SQL Server and Service

Create a .txt file with all the server name and keep it in safe location (C:\ServerList.txt). 
Save the powershell script to a preferred location as .ps1 file.
Create a new task on Windows Task Scheduler to run the powershell script every 5 or 10 minutes.
 
 
$servers = gc 'C:\ServerList.txt'
foreach($server in $servers)
{$body=get-wmiobject win32_service -computername $server |
select name,state | where {($_.name -eq "MSSQLSERVER")-and $_.state -match "Stopped"} |
  Out-String
    
if ($body.Length -gt 0)
{
  $smtp = new-object Net.Mail.SmtpClient("xxxxx.xxxxx.com")
  $subject="SQL service is down on " + $server
  $smtp.Send("xxxxx@xxxxx.com", "mashrurs@shuvo.com", $subject, $body)
 
}
}

foreach($server in $servers)
     {
       $result = (Test-Connection $server -Quiet)
        if ($result -match 'False')
            {
                $smtp = new-object Net.Mail.SmtpClient("XXXX.XXXX.com")
                $subject="SQL Server $Server is down"
                $smtp.Send("XXXX@xxxx.com", "mashrurs@shuvo.com", $subject, $result)
            }
      }