All tables row count

Useful code to gather the rowcount of all tables, worth doing a reindex prior to this code

SELECT
SCHEMA_NAME(t1.schema_id) AS [schema],
t1.name AS [table],
i1.rows AS [row_count]
FROM sys.tables AS t1 INNER JOIN
sys.sysindexes AS i1 ON t1.object_id = i1.id AND i1.indid < 2

Space used for all databases

This is a useful script I use to list the space used for all databases on a given sql server

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

The purpose of passing the argument : @updateusage , is it updates the usage information

Quick uninstall of SQL Server 2005 instance

The following command line brings up all instance names and asks to select
an instance and then a few button clicks.

C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe"
/Remove

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

Move MDF/LDF files to a designated path

Even though I'd prefer to Backup a database and then move , often a third party supplier
will need it as a MDF or LDF , which they then will have  a script to reattach .
The main steps are :

Detach DB
Move to designated path
ReAttach DB
2 input parameters: Name of DB on SAN, targeted path

The main issue to consider is permissions on network to actually transport the files .

The 'xp_cmdshell' is used (which I don't often recommend , but it does serve its purpose on this script

--------------------CODE START----------------------------------
     DECLARE  @DB_NAME NVARCHAR(128);
     DECLARE @FILE_PATH NVARCHAR(256);
     SET   @DB_NAME = 'MY_DB'
     SET   @FILE_PATH = 'MY_FILE_PATH'
    
      --variables
       DECLARE @SQL_A VARCHAR(300);
       DECLARE @SQL_B VARCHAR(300);
       DECLARE @SQL_C VARCHAR(300);
       DECLARE @SQL_D VARCHAR(300);
       DECLARE @SQL_E VARCHAR(2000);
       DECLARE @DATA_FILE VARCHAR(600);
       DECLARE @LOG_FILE VARCHAR(600);
       DECLARE @MDF_FILE NVARCHAR(256);
       DECLARE @LDF_FILE NVARCHAR(256);
       DECLARE @FILENAME_TBL TABLE(fileType INT, FilePath NVARCHAR(256));
       --establish physical path and filename of MDF and LDF files
       SET @SQL_A = 'SELECT 1,filename from '+@DB_NAME+'.sys.sysfiles WHERE fileid = 1';
       SET @SQL_B = 'SELECT 2,filename from '+@DB_NAME+'.sys.sysfiles WHERE fileid = 2';
      
       INSERT INTO @FILENAME_TBL EXEC (@SQL_A);
    INSERT INTO @FILENAME_TBL EXEC (@SQL_B);
       SET @MDF_FILE = (SELECT FilePath FROM  @FILENAME_TBL WHERE fileType=1);
       SET @LDF_FILE = (SELECT FilePath FROM  @FILENAME_TBL WHERE fileType=2);
    
 
       ---- DETACH DB
       PRINT 'Detach DB : START';
        --set the DB to SINGLE USER
    SET @SQL_C = 'ALTER DATABASE ' + @DB_NAME + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;';
    EXEC (@SQL_C);
      
       EXEC sp_detach_db @DB_NAME,'true'
       PRINT 'Detach DB : FINISH';
      

        --MOVE MDF file
        PRINT 'MOVE MDF & LDF : START';
        DECLARE @dataFile1Cmd VARCHAR(200),
     @dataFile2Cmd VARCHAR(200)
       
        SET  @dataFile1Cmd = 'copy "'+@MDF_FILE + '" "' +  @FILE_PATH + '"';
       
  SET  @dataFile2Cmd = 'copy "'+@LDF_FILE + '" "' +  @FILE_PATH + '"';
        EXEC master..xp_cmdshell @dataFile1Cmd
        EXEC master..xp_cmdshell @dataFile2Cmd
        PRINT 'MOVE MDF & LDF : FINISH';
    
      --REATTACH DB
        PRINT 'Attach DB : START';
        SET @SQL_E = 'EXEC sp_attach_db @dbname = N'''+@DB_NAME +''',@filename1 = N'''+@MDF_FILE+''',@filename2 = N'''+@LDF_FILE+'''';
        PRINT @SQL_E;
        EXEC (@SQL_E);
        SET @SQL_D = 'ALTER DATABASE ['+@DB_NAME+'] SET MULTI_USER WITH ROLLBACK IMMEDIATE';
        EXEC (@SQL_D);
        PRINT 'Attach DB : FINISH';
--------------------CODE END----------------------------------

scripts vacancies

Kill all connections to a database

I had to copy a whole bunch of MDF\LDF files from a server ,
but was experiencing some problems regarding user connections.
I was trying to detach using this code:

-------------------------code start-------------------------------
USE [master]
GO
EXEC master.dbo.sp_detach_db
@dbname = N'MY_DB',
@keepfulltextindexfile=N'true'
GO
------------------------code end----------------------------------

which was not allowing me to detach . The following code allowed me to clear the connnections and
do the detaching

----------------------code start------------------------------------

alter database MY_DB set single_user with rollback immediate 

alter database MY_DB set multi_user with rollback immediate 
--------------------code end -----------------------------------------

DBA jobs

Space Integrity Check

There was a requirement to allow  non -technical  users , to manage archiving
their databases on a SQL Server 2005 platform. They wanted to have the capacity to move the databases from a SAN drive

to SATA drive, but they continued to have  a problem regarding space on the destination drive. In other words , having

knowledge of the existing database and comparing to the space available on the destination drive.

The following code satisfied the requirement .

Firstly , create the following procedure (fnSpace_Integrity) , which has 2 input parameters .
@db  (which is the db name)
@intendedDrive   (the targe drive )
Secondly , call the procedure with code similar to the CODE 2 section. You can do whatever you want from the result

-------------------------------------CODE 1 START-------------------------------------
CREATE PROCEDURE [dbo].[fnSpace_Integrity]
@db VARCHAR(200) ,
@intendedDrive char(1),
@spResult int OUTPUT

AS

DECLARE @dbsize bigint,@logsize bigint,
@MB_Free int,
@reservedpages  bigint,
@database_size VARCHAR(100)

--establish db size in MB
select   @database_size   = convert(int,
                                    case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...
                                    when convert(bigint, sum(s_mf.size)) >= 268435456
                                    then null
                                    else (sum(s_mf.size)*8)/1024 -- Convert from 8192 byte pages to Kb then to MB
                                    end)
from   sys.master_files s_mf
where
        s_mf.state = 0 and -- ONLINE
        has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
        and db_name(s_mf.database_id) = @db
    group by s_mf.database_id
    order by 1

PRINT @database_size

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
select @MB_Free = MB_Free  from #FreeSpace WHERE drive = @intendedDrive
drop table #FreeSpace

--compare intended drive space with the database size 
if @MB_Free > @database_size
begin
select  @spResult = 1
end
else
begin
select  @spResult = 0
end
--------------------------------CODE 1 END-----------------------------------------------------

--------------------------------CODE 2 START------------------------------------------------------

declare @spaceResult INT
exec fnSpace_Integrity  @DB_NAME_EXISTING,@BackupDrive,@spaceResult OUT
select @spaceResult

if @spaceResult = 0
begin
   
  PRINT 'There is not enough space on drive ' + @BackupDrive + ' to move the database : ' +

@DB_NAME_EXISTING

end

------------------------------CODE 2 END----------------------------------------------------------

sata jobs

Synchronise Users and Logins after SQL Server 2005 restore

I'm working on a whole bunch of moving SQL Server 2005  around . I'm backing up and restoring from
ServerA to ServerB .
A script I've found useful (courtesy of sqlservercentral.com) . This will synchronise the Database Users with the
SQL Server Logins, which tend to become orphaned .

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

DECLARE @UserName nvarchar(255)

DECLARE Cursor_OrphanedUser cursor for

SELECT NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name

OPEN Cursor_OrphanedUser

FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName

WHILE (@@fetch_status = 0)

BEGIN

PRINT @UserName + ' Synchronization of Logins in Progress'

EXEC sp_change_users_login 'Update_one', @UserName, @UserName

FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName

END

CLOSE Cursor_OrphanedUser

DEALLOCATE Cursor_OrphanedUser

Go
------------------------------------------CODE END--------------------------------------------------