SQL Server backup review with Powershell

I had  to produce a report to the Backup team outlining the last backup of every database on every instance.
This had to cover : full , incremental and log type backups. The idea was to compare this against their schedule , checking for disparities .

The sql server inventory in the organisation is 750 sql server instances , using SQL server 2000 and 2005. The solution had to involve iterating through a list of sql server instances , and executing some t-sql code, adding the results to an output file.Following is the steps , with relevant details

STEP 1
Create a .txt file with a list of all relevant instances , such as

MYSERVER1\INSTANCE1
MYSERVER2\INSTANCE2
MYSERVER3\INSTANC3

STEP 2
Create a .sql file with the following code , and save as backups_last_good_backups.sql. In this case I wanted to raise an ALERT , if a backup hadn't occured in 7 days , or if no backup existed at all.:

/******************************************************************
*
* SQL Server - Last known good backup
*
* Run this script against a sql server instance to check when last known
* good backups were taken
* The script displays : name of db , backup type , backup set db name , last backup
*A
******************************************************************/
SELECT  (SELECT   RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as instance,
        sd.name as name,
        bs.type,
        bs.database_name,
        max(bs.backup_start_date) as last_backup,
        note = CASE
   WHEN max(bs.backup_start_date) < GETDATE() - 7 THEN 'ALERT'
   WHEN ISNULL(max(bs.backup_start_date),0) = 0 THEN 'ALERT'
   ELSE '---'
   END
FROM    master..sysdatabases sd
        Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
        left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
        WHERE sd.name NOT LIKE '%tempdb%'
Group by sd.name,
        bs.type,
        bs.database_name
Order by sd.name,last_backup

 

STEP 3
Create a powershell file , and place in the following code . Note :
on line 1 is the location of the file you created in Step 1
Line 2 , invokes the SQLCMD (which needs to be installed on whichever computer you are execuring this powershell code.)
It accepts the the $svr variable , and runs the .sql code , saved in step 2. This outputs the results into "backupAudit_SQL_Server_output.txt"
in an iterative mode.

foreach ($svr in get-content "C:\instances_backup.txt"){
        invoke-expression "SQLCMD -E -S $svr -i backups_last_good_backups.sql -v inst_name=$svr -Y30 >> backupAudit_SQL_Server_output.txt"
 } 

 

STEP 4
Ensuring your powershell code and SQL code are in the same directory ,Run the powershell code in your Powershell command line - and then check the output , it should be something like :

instance                       name                           type database_name                  last_backup             note
------------------------------ ------------------------------ ---- ------------------------------ ----------------------- -----
MYSERVER1_INST1               master                         D    master                         2009-06-27 23:59:53.000 --- 
MYSERVER1_INST1               model                          D    model                          2009-06-28 00:03:01.000 --- 
MYSERVER1_INST1               msdb                           D    msdb                           2009-06-28 00:03:07.000 --- 
MYSERVER1_INST1               Rightdbs                       I    Rightdbs                       2009-06-26 20:30:26.000 --- 
MYSERVER1_INST1               Rightdbs                       D    Rightdbs                       2009-06-28 00:03:18.000 --- 

(5 rows affected)
instance                       name                           type database_name                  last_backup             note
------------------------------ ------------------------------ ---- ------------------------------ ----------------------- -----
MYSERVER2_INST2             master                         NULL NULL                                              NULL ALERT
MYSERVER2_INST2             model                          NULL NULL                                              NULL ALERT
MYSERVER2_INST2             msdb                           NULL NULL                                              NULL ALERT
MYSERVER2_INST2             db3nn                          D    db3nn                          2009-06-18 19:56:10.000 ALERT

 

SQL Server detected a DTC/KTM in-doubt transaction with UOW

There was an unexpected server shutdown , on a sql server box . Upon restart one of the databases went into an IN RECOVERY state ,
and once the database had reached 99% complete  the following message appeared in the SQL Server log files .

SQL Server detected a DTC/KTM in-doubt transaction with UOW  {63E15310-78DA-42DA-8BB1-38B59FDA866E}.Please resolve it following the guideline for Troubleshooting DTC Transactions.

Initially I tried the  : KILL '{63E15310-78DA-42DA-8BB1-38B59FDA866E}' WITH COMMIT   ,but this didn't resolve the issue.

The resolution was achieved by :

Type "mmc.exe" (without the quotation marks) in the Run dialog box to open Microsoft Management Console (MMC).
In MMC, on the Console menu, click Add/Remove Snap-in.
In the Add/Remove Snap-in dialog box, click Add.
In the Add Standalone Snap-in dialog box, click Component Services, and then click Add to add the snap-in.
Click Close.
In the Add/Remove Snap-in dialog box, click OK.
In the console root under Component Services, expand Component Services, point to Computers, point to My Computer, and then click Distributed Transaction Coordinator.
Select Transaction List. You see transactions in the details pane. Right-click the transaction, and then click Resolve. You now have three options: Commit, Abort, or Forget. Choose the one that is appropriate for your situation.
DTC jobs

SQL Server blocked access to procedure 'dbo.sp_sqlagent_get_startup_info' of component 'Agent XPs'

SQL Server blocked access to procedure 'dbo.sp_sqlagent_get_startup_info' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

I was getting a "the user cannot access resource due to not having rights on Locked in Memory"

The solution to the problem is:

On the Start menu, click Run. In the “Open” box, type gpedit.msc.
On the “Group Policy” console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.
In the right details pane, double-click Lock pages in memory.

Add the relevant account to the permissions list on this policy, the account should be the start up account for the SQL Service

Windows jobs

SQL and Markov Chains in Tennis

I’ve just completed a project at tennisMatchOdds.com which required the analysing probabilities of  outcomes in the sport  of  tennis. An algorithm created the likely outcome of the event. A further requirement of the project was to calculate likely outcomes of the event , from different points and scores during the event. For example, if player A was 1 set up what was the probability of them winning the match.

I analysed the data within SQL Server 2000 and the algorithms were created using T-SQL. There was a possibility to use Java 1.4, but the customer requested (for different commercial reasons)  for the analysis to occur in SQL. Initially , I investigated the possibility of a script library that could be imported. I could push the data through and get an immediate result. No luck. I decided to simulate the Markov Chain modelling through T-SQL.

A Markov chain model simulates changes in a finite number of states at regular time intervals. For my purposes , it was effective because I was dealing with a large set of objects which required local manipulation . For example, modify an object slightly to get a new one, also as a way to discover patterns within the different result sets. In other words, you can compute the probability of the match being in any given state.

The following code , between START CODE and END CODE, should be copied and pasted into Query Analyzer . A result set will appear that will allow you to view  , how you can programmatically start off with an initial figure and go through every possibility.

The data in “#tempNum” represents all the possibilities within my model.

-------------------------START CODE----------------------------------------

--the code is an abstraction for educational purposes .

--It is an example of simulating Markov Chain modelling with SQL Server 2000

--the key Markov Chain elements are represented with lower case SQL

--particuarly between the comments --start loop and --end loop

CREATE TABLE #tempNum (

            [id] [int] NULL ,

            [a] [int] NULL ,

            [b] [int] NULL

) ON [PRIMARY]

GO

INSERT INTO #tempNum ([id],[a],[b])VALUES(1,4,0)

INSERT INTO #tempNum ([id],[a],[b])VALUES(2,4,1)

INSERT INTO #tempNum ([id],[a],[b])VALUES(3,4,2)

INSERT INTO #tempNum ([id],[a],[b])VALUES(4,4,3)

INSERT INTO #tempNum ([id],[a],[b])VALUES(5,4,4)

INSERT INTO #tempNum ([id],[a],[b])VALUES(6,3,4)

INSERT INTO #tempNum ([id],[a],[b])VALUES(7,3,3)

INSERT INTO #tempNum ([id],[a],[b])VALUES(8,3,2)

INSERT INTO #tempNum ([id],[a],[b])VALUES(9,3,1)

INSERT INTO #tempNum ([id],[a],[b])VALUES(10,3,0)

INSERT INTO #tempNum ([id],[a],[b])VALUES(11,2,3)

INSERT INTO #tempNum ([id],[a],[b])VALUES(12,2,2)

INSERT INTO #tempNum ([id],[a],[b])VALUES(13,2,1)

INSERT INTO #tempNum ([id],[a],[b])VALUES(14,2,0)

 

GO

 

 

 

DECLARE @playerBReturn DECIMAL(10,4)

DECLARE @playerAserver DECIMAL(10,4)

SET @playerAserver = .50

SET @playerBReturn = 1-@playerAserver

 

begin

   set nocount on

   DECLARE @initCalc DECIMAL(10,8)

   SET @initCalc = .33

  

   create table #tmp (recno int identity, a int,b int,pv DECIMAL(10,4))

   insert into #tmp (a,b,pv)

   select a,b,1 from #tempNum  order by id ASC

   declare @t int,@c int

   DECLARE @tmpPV DECIMAL(10,8),@tmpPVUpdate DECIMAL(10,8)

   DECLARE @tmpAplus INT, @tmpBplus INT  --+1

   DECLARE @tmpA INT, @tmpB INT  --+1

   set @t = (select count(*) from #tmp)

   set @c = 1

   --start loop

   while @c <= @t

   begin

      IF @c < 7

      BEGIN

                         update #tmp

                 set pv = 1.00

                 where recno=@c

      END

 

      IF @c = 7

      BEGIN

             SET @tmpPV = @initCalc

      END

      ELSE IF @c > 7

      BEGIN

             SET @tmpPV = (select pv from #tmp x where x.recno=@c-1)

      END

     

     

      IF @c = 7

      BEGIN

             update #tmp

         set pv = @initCalc

         where recno=@c

        

      END

      ELSE IF @c > 7

      BEGIN

             --deal with 0

             IF  ((SELECT b FROM #tmp x WHERE  x.recno=@c) IN (3)) SET @tmpPV =0

         SET @tmpA = (SELECT a FROM #tmp x WHERE  x.recno=@c)

         SET @tmpB = (SELECT b FROM #tmp x WHERE  x.recno=@c)

             SET @tmpAplus = ((SELECT a FROM #tmp x WHERE  x.recno=@c) + 1)

         SET @tmpBplus = ((SELECT b FROM #tmp x WHERE  x.recno=@c) + 1)

         IF @tmpAplus = 3 AND @tmpBplus = 4  SET @tmpBplus=3

         IF @tmpAplus = 2 AND @tmpBplus = 4  SET @tmpBplus=3

         IF @tmpAplus = 1 AND @tmpBplus = 4  SET @tmpBplus=3

         IF @tmpA = 2 AND @tmpB = 2  SET @tmpBplus = 2

             IF @tmpA = 2 AND @tmpB = 1  SET @tmpBplus = 1

             IF @tmpA = 2 AND @tmpB = 0  SET @tmpBplus = 0

             IF @tmpA = 1 AND @tmpB = 2  SET @tmpBplus = 2

         IF @tmpA = 1 AND @tmpB = 1  SET @tmpBplus = 1

         IF @tmpA = 1 AND @tmpB = 0  SET @tmpBplus = 0

         IF @tmpA = 0 AND @tmpB = 2  SET @tmpBplus = 2

         IF @tmpA = 0 AND @tmpB = 1  SET @tmpBplus = 1

         IF @tmpA = 0 AND @tmpB = 0  SET @tmpBplus = 0

         SET @tmpPVUpdate = (SELECT pv FROM #tmp x WHERE  a = @tmpAplus  AND b = @tmpBplus)

             update #tmp

         set pv =  ((@playerAserver * @tmpPVUpdate) +  (@playerBReturn * @tmpPV))

         where recno=@c

       

       END

      set @c = @c + 1

   end

   --end loop

   select recno,a,b,pv = pv from #tmp where a <> 4 AND b <> 4

  

   DROP TABLE #tempNum

   DROP TABLE #tmp

   

end

-------------------------END CODE---------------

sp_spaceused not refreshing

I ran the command

EXEC sp_msforeachdb @command1="use ? exec sp_spaceused"

which returned all the relevant information per database on a SQL Server instance I was troubleshooting.
On the basis of the information I adjusted one of the db sizes .

I then ran the same command and notices that the information was not refreshing .

Adding "@updateusage = N'TRUE'" derived the latest information , and reflected the changes I made.

EXEC sp_msforeachdb @command1="use ? exec sp_spaceused @updateusage = N'TRUE';"

hardware jobs

Viewing legacy DTS packages in SSMS

I installed SQL Server Management Studio  , to view some a SQL Server instance 2000.
I attempted to access the packages via the : "Management | Legacy | Data Tramsformation Services"
and got the following message:

SELECT permission denied on table sysdtspackages, database 'master', owner 'dbo'

I was perplexed , because when I attempted to view the DTS packages via SQL Server 2000 Enterprise Manager - I was to view tehm.

I got around the problem , via creating a new role called "DTS_user" and assigned
EXECUTE on sp_enum_dtspackages and sp_get_dtspackage in msdb.

I then went into the user and assigned that role.

DTS jobs

SQL Server Surface Area Configuration command line interface

The SQL Server Surface Area Configuration command-line interface, sac.exe, allows you to import and export settings.
In a recent server consolidation exercise I exported the setting and used it as a form of backup , in case of any errors
during the consolidation process.
You can import and export settings on a per-instance basis and also on a per-service basis by using the command line parameters.

Using this command line utility requires sysadmin privileges

The following code is an example of exporting all settings from the default instance of SQL Server on server1 and importing them into server2:

sac out MYSERVER1.out –S MYSERVER2.out –U admin –I MSSQLSERVER
sac in MYSERVER1.out –S MYSERVER2

 

ole automation configuring for SQL Server 2005

There was a requirement to allow Ole Automation Procedures within SQL Server 2005 ,
this code will enable the feature.

-------------------CODE START------------------------
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

-------------------CODE FINISH--------------------------

ole jobs

Powershell and Batch files

Executing batch files throgh Powershell - is not doable by design. The cmd.exe is the runtime environment for .bat files , and it is
possible to issue a .bat call via the Powershell prompt

PS> cmd /c mybatchfile.bat

System administrator jobs

Powershell : SQLCMD and invoke-expression

There was a requirement to add a SQL Server login to a group of SQL Servers.
The SQL Server login was to be uniform across all the servers. I tend to use SQLCMD for most
DBA tasks - and as my account is Windows based and with high level of privileges , I didn't
have to worry about supplying a different username / password for every SQL Serve instance.

This powershell script is very simple , iterating through a list of SQL Server instances which are
stored in a text file . For every instance a SQLCMD is run . This is done through the "invoke-expression" call.
This allows the DBA to maintain their sql scripts separately , which can be executed against any number of instances

-----------------CODE STARTS---------------------------

foreach ($svr in get-content "C:\MyInstances.txt"){
    $svr
    invoke-expression "SQLCMD -E -S $svr -i createMyuser.sql"
   
}

-------------CODE FINISHES----------------

sqlcmd jobs