Tag Archives: SQL Server

How to tell if Virtual Machine backups are running full database backups on a SQL Server instance

Some Virtual Machine backup solutions use the SQL Server VSS writer service to make SQL Server database backups as part of the VM backup.

You may not have had visibility on this process so to confirm if this is happening in your environment open up SQL Server Management Studio (SSMS) then in Object Explorer right click on the instance name, click on new query and run the script below. 

This query looks for entries in the physical_device_name field of msdb.dbo.backupmediafamily that are not directory paths.

SELECT @@SERVERNAME AS ServerName
	,bs.backup_set_id AS BackupSetId
	,bs.database_name AS DbName
	,bs.backup_start_date AS BackupStartDate
	,bs.backup_finish_date AS BackupFinishDate
	,CAST(CAST(bs.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size]
	,bs.[type] AS BackupType
	,CASE bs.[type]
		WHEN 'D'
			THEN 'Full Backup'
		WHEN 'I'
			THEN 'Differential Backup'
		WHEN 'L'
			THEN 'TLog Backup'
		WHEN 'F'
			THEN 'File or filegroup'
		WHEN 'G'
			THEN 'Differential file'
		WHEN 'P'
			THEN 'Partial'
		WHEN 'Q'
			THEN 'Differential Partial'
		END AS BackupTypeDescription
	,bmf.physical_device_name AS BackupFilePath
	,CAST(bs.first_lsn AS VARCHAR(50)) AS FirstLogSeqNo
	,CAST(bs.last_lsn AS VARCHAR(50)) AS LastLogSeqNo
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK) ON bs.media_set_id = bmf.media_set_id
WHERE bs.[type] = 'D'
	AND bmf.physical_device_name NOT LIKE '%\%'
ORDER BY bs.database_name ASC
	,bs.backup_set_id ASC;

 

For the field BackupFilePath if you see GUID looking entries like {AQ8F5957-2804-4X05-9EE5-109EB87EBCAB}5 the VM backup is probably running full database backups on the SQL Server.

It might be time to review your recovery strategy as the VM backups could be covering the same ground as the SQL Server backups creating a lot of expensive redundancy due to unnecessary replication of work.

How to schedule a job to restore the last backup made of a SQL Server database

This post provides you with a script that will generate a restore script for a database using the latest full backup file that exists in a directory. (No need to state the filename explicitly)

You need to provide the following at the start of the script:

  • The target database i.e. the database you will restore to
  • The directory where the backup file is saved

If you are using the excellent Ola Hallengren maintenance solution (see link) the directory path will look something like below. If you’re not using Ola’s solution, you should be.

\\ServerWhereBackupsAreSaved\DriveName\InstanceName\TargetDatabase\Full\

This restore script is designed to work with Ola’s solution as it segregates the backup directory structure such that each database has an allocated folder and each full backup file is named with the date and time of the file creation.

The restore script determines which backup file is the latest backup file based on the max name. So for the script to work it is assumed you have an appropriate backup strategy (i.e. using Ola’s solution) were backup types are segregated into different folders, backup names have a date reference and the backup location is dedicated to backups and nothing else, i.e. no trash files in the location.

Some use cases for this solution might be:

  • Restoring a nightly backup to another instance for reporting purposes
  • Restoring backups to a development environment
  • Restoring backups to another server to test the backups

You can use the logic in a stored procedure or as the T-SQL in a job step and schedule accordingly.

/*
You need to reference the following:
* The target database i.e. the database you will restore to.
* The directory where the backup file is saved.
If you are using the Ola Hallengren backup scripts the directory path will look 
something like below.
\\ServerWhereBackupsAreSaved\DriveName\InstanceName\TargetDatabase\Full\

Find & Replace the follow text for the target database and directory:

TARGET_DATABASE
DIR_PATH
*/
/*
Declare Variables
*/
DECLARE @DatabaseToRestore AS VARCHAR(MAX);
DECLARE @DirToSearch AS VARCHAR(MAX);
DECLARE @ShellCommand AS VARCHAR(MAX);
DECLARE @BackupFile AS VARCHAR(MAX);
DECLARE @Sql AS VARCHAR(MAX);

/*
Set User Variables
*/
SET @DatabaseToRestore = 'TARGET_DATABASE';
SET @DirToSearch = 'DIR_PATH';
SET @ShellCommand = 'dir ' + @DirToSearch;

/*
Create Temp Table To Hold xp_cmdshell Output
*/
IF OBJECT_ID('tempdb..#DirList') IS NOT NULL
	DROP TABLE #DirList;

CREATE TABLE #DirList (
	Id INT identity(1, 1)
	,line NVARCHAR(1000)
	);

/*
Enable Advanced Options To Enable xp_cmdshell Temporarily
*/
EXEC master.dbo.sp_configure 'show advanced options'
	,1;

RECONFIGURE
WITH OVERRIDE;

EXEC master.dbo.sp_configure 'xp_cmdshell'
	,1;

RECONFIGURE
WITH OVERRIDE;

/*
Run The Shell Command To Capture And Write Dir Info To Temp Table
*/
SET @Sql = '
INSERT INTO #DirList (line)
EXEC xp_cmdshell ' + '''' + @ShellCommand + '''' + ';';

EXEC (@Sql);

EXEC master.dbo.sp_configure 'xp_cmdshell'
	,0;

/*
Disable Advanced Options And xp_cmdshell Again
*/
RECONFIGURE
WITH OVERRIDE;

EXEC master.dbo.sp_configure 'show advanced options'
	,0;

RECONFIGURE
WITH OVERRIDE;

/*
Get The Last Backup File Name And Save To A Variable
*/
WITH CTE
AS (
	SELECT SUBSTRING(line, 37, 100) [FileName]
	FROM #DirList
	WHERE Id > (
			SELECT MIN(Id)
			FROM #DirList
			WHERE line LIKE '%<DIR>%..%'
			)
		AND Id < (
			SELECT MAX(Id) - 2
			FROM #DirList
			)
	)
SELECT @BackupFile = [FileName]
FROM CTE
WHERE [FileName] = (
		SELECT MAX(FileName)
		FROM CTE
		);

/*
Create The Restore Script
*/
SET @BackupFile = @DirToSearch + @BackupFile
SET @Sql = '
ALTER DATABASE ' + QUOTENAME(@DatabaseToRestore) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ' + QUOTENAME(@DatabaseToRestore) + ' FROM DISK = ' + '''' + @BackupFile + '''' + '
WITH NORECOVERY
,REPLACE;
RESTORE DATABASE ' + QUOTENAME(@DatabaseToRestore) + ' WITH RECOVERY;
'

/*
To Directly Execute The Script Uncomment The EXEC Statement And Delete The Select Statement
*/
/*
EXEC (@Sql)
*/
SELECT @Sql

 

How to generate a random password with T-SQL

The following script will generate a random 10 character password that meets the complexity requirement for Microsoft Windows. To generate a password just run the script in a new SQL Server Management Studio window. The logic can also be easily turned into a function.

The option of symbol characters is limited to what’s shown below as dealing with quotes and obscure characters in a password is often more trouble than it is worth. The password generated however should still be very secure as it will be 10 characters long with a guaranteed number, lowercase letter, uppercase letter and a symbol.

!
#
$
%
&
(
)
*
+

/*Declare Variables*/
DECLARE @i INT;
DECLARE @Pw VARCHAR(MAX);
DECLARE @Numbers TABLE (Characters CHAR(1));
DECLARE @LowerCase TABLE (Characters CHAR(1));
DECLARE @UpperCase TABLE (Characters CHAR(1));
DECLARE @Symbols TABLE (Characters CHAR(1));
DECLARE @BaseCharacters TABLE (Characters CHAR(1));
DECLARE @GuaranteedCharacters TABLE (Characters CHAR(1));
DECLARE @PwCharacters TABLE (Characters CHAR(1));

/*Generate Numbers*/
SET @i = 0;

WHILE @i <= 9
BEGIN
	INSERT INTO @Numbers
	SELECT @i

	SET @i = @i + 1
END;

/*Generate Lowercase Letters*/
SET @i = 97;

WHILE @i <= 122
BEGIN
	INSERT INTO @LowerCase
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*Generate Uppercase Letters*/
SET @i = 65;

WHILE @i <= 90
BEGIN
	INSERT INTO @UpperCase
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*Generate Symbols*/
SET @i = 33;

WHILE @i <= 43
BEGIN
	IF (
			@i = 34
			OR @i = 39
			)
	BEGIN
		SET @i = @i + 1
	END

	INSERT INTO @Symbols
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*
Randomly Select A Number, Lowercase Letter,
Uppercase Letter And A Symbol So Four Character Types
Are Guaranteed To Be Present Somewhere In The Password
*/
INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @Numbers
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @LowerCase
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @UpperCase
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @Symbols
ORDER BY NEWID();

/*
Randomly Select Another 6 Characters
*/
INSERT INTO @BaseCharacters
SELECT TOP 6 Characters
FROM (
	SELECT Characters
	FROM @Numbers
	
	UNION ALL
	
	SELECT Characters
	FROM @LowerCase
	
	UNION ALL
	
	SELECT Characters
	FROM @UpperCase
	
	UNION ALL
	
	SELECT Characters
	FROM @Symbols
	) AS Characters
ORDER BY NEWID()

/*Generate A 10 Character Password*/
INSERT INTO @PwCharacters (Characters)
SELECT Characters
FROM (
	SELECT Characters
	FROM @BaseCharacters
	
	UNION ALL
	
	SELECT Characters
	FROM @GuaranteedCharacters
	) AS Characters
ORDER BY NEWID()

/*Save The Password To A String*/
SELECT @Pw = COALESCE(@Pw + Characters, Characters)
FROM @PwCharacters

SELECT @Pw AS PW;

 

If you found this post helpful please like, comment and share.

How to move and or rename Database files in SQL Server

An example use case for the process below could be you need to move database files to a new drive. Another example might be your organisation intends to run a legacy database along side a new updated database with both sharing the same database name in the same instance with the files located in the same directory with the same names. Obviously this cannot be done and requires the database names to differ and the files to be renamed or not exist in the same directory.

For example AdventureWorks might become AdventureWorks_Legacy while a new and improved AdventureWorks database retains the original database name. The associated database file names would also need to be changed/moved to reflect this.

Someone might also want to do something like this for test purposes but obviously having test resources in a live environment would not be recommended if avoidable.

The first step to moving and renaming the files is to copy and modify the script below. Note the script below assumes you want to move and change the names of the files. To avoid any database conflicts you only need to do one or the other.

/* 
Find & Replace DbName with the name of the Database you are working with
*/
USE [DbName];

/*
Changing Physical names and paths
Replace 'C:\...\NewDbName.mdf' with full path of new Db file to be used
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = ' DbName '
	,FILENAME = 'C:\...\NewDbName.mdf'
	);

/*
Replace 'C:\...\NewDbName_log.ldf' with full path of new Db log file to be used
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = ' DbName _log'
	,FILENAME = 'C:\...\NewDbName_log.ldf'
	);

/*
Changing logical names
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = DbName
	,NEWNAME = NewDbName
	);

ALTER DATABASE DbName MODIFY FILE (
	NAME = DbName_log
	,NEWNAME = NewDbName_log
	);
Once the script has been set up as desired follow the steps below:
  1. Open Microsoft SQL Server Management Studio (SSMS).
  2. Connect to the server that houses the Db you are working with.
  3. Run the modified script
  4. Right click on the Db in SSMS and select Tasks > Take Offline
  5. If you are moving the database files log into the server that houses the database files and copy and move the MDF and LDF files to the location you specified in first two alter commands. If the script specifies new names rename the copied files to match the names given in the script exactly.
  6. Go back to SSMS and right click on the Db and select Tasks > Bring Online.
  7. If you have moved the files once the database is back online and confirmed working as expected the unused original files can be deleted.
  8. Now you can rename the Db to the new name if you wish using SSMS.

How to filter sp_who2 to create KILL statements

Until now the only way to kill wayward commands or queries in SQL Server was to run sp_who or sp_who2, look for the record with abnormal CpuTime or DiskIO readings (or look for the login of the guy who never knows what he’s doing), and take note of the corresponding Spid number by running your finger along the screen tracing it back to the ID so you don’t get confused and end up killing the wrong Spid from another record by mistake.

But now there’s a better way if you know the the likely culprit you want to kill in advance. The script below will allow you to filter the results of sp_who2 based on any of the returned columns. You’ll now be able to specify the database name or login name etc. and the query will return only the rows that match your constraints. Narrow the results down enough and you’ll be left with one record to kill. Then copy the results of the KillSpid column and paste to a new SSMS window. Now you should be left with one Kill command to run with no possibility of killing the wrong Spid.

IF OBJECT_ID('tempdb..#sp_who2') IS NOT NULL DROP TABLE #sp_who2
GO

CREATE TABLE #sp_who2 (
	Spid INT
	,Status VARCHAR(255)
	,LoginName VARCHAR(255)
	,HostName VARCHAR(255)
	,BlkBy VARCHAR(255)
	,DbName VARCHAR(255)
	,Command VARCHAR(255)
	,CpuTime INT
	,DiskIO INT
	,LastBatch VARCHAR(255)
	,ProgramName VARCHAR(255)
	,Spid2 INT
	,RequestId INT
	)

INSERT INTO #sp_who2
EXEC sp_who2

SELECT 'Kill ' + CONVERT(VARCHAR(MAX), SPID) AS KillSpid
	,Spid 
	,Status 
	,LoginName 
	,HostName 
	,BlkBy 
	,DbName 
	,Command 
	,CpuTime 
	,DiskIO 
	,LastBatch 
	,ProgramName 
	,Spid2
	,RequestId
FROM #sp_who2
-- Add any filtering of the results here :
WHERE DBName NOT IN ('master')
-- Add any sorting of the results here :
-- AND ProgramName = ''
-- AND DbName = ''
-- AND LoginName = ''
-- AND HostName = ''
-- AND Status = ''
ORDER BY Spid ASC
,DBName ASC;

DROP TABLE #sp_who2

 

How to get the date & time a job or schedule was modified in SQL Server

The script below has one parameter, @SD or start date. The logic of the script will return two tables showing the modified jobs and schedules from the start date onward.

By default the start date will be set to one year ago to the day which will return a full years worth of data up to the time the script is run. To enter a date manually change the line SET @SD = NULL; –‘20180101 00:00:00’; removing NULL; — and changing the date time text. 

USE msdb;
GO

DECLARE @SD AS DATETIME;

SET @SD = NULL; --'20180101 00:00:00';

IF @SD IS NULL
BEGIN
SET @SD = DATEADD(YEAR, - 1, GETDATE());
END;

SELECT @SD AS StartDate;

-- Jobs that have been modified
SELECT [Name] AS JobName
	,[Enabled]
	,[Date_created]
	,[Date_modified]
FROM sysjobs
WHERE [date_modified] > @SD
--AND enabled = 0
ORDER BY [date_modified] DESC;

-- Schedules that have been modified
SELECT [Name] AS ScheduleName
	,[Enabled]
	,[Date_created]
	,[Date_modified]
FROM sysschedules
WHERE [date_modified] > @SD
--AND enabled = 0
ORDER BY [date_modified] DESC;
GO

 

How to assess a SQL Server instance for GDPR compliance by writing every table and column to Excel

This post uses the script I had written before here LINK modified slightly to include a count of each table. If you are a DBA you’re likely assisting compliance offers to find personal identifiable data within the databases at this time. This script will allow you to provide them with a record of every database, table and column on an entire instance. The compliance offer can then sieve through all the columns and highlight any columns that look like they contain personal data for further investigation.

Below is a SQL query that will return the following metadata about each table from each database located on a SQL server database server:

  • ServerName
  • DatabaseName
  • SchemaName
  • TableName
  • CountOfRows
  • ColumnName
  • KeyType

The output is provided in a table format with these additional formatted lines of text which can be used as queries or as part of queries.

  • A Select table query
  • A Count table row columns
  • A Select column query
  • Each column bracketed
  • Each table and column bracketed

The table returned by the query can be exported to excel. Using excels filter option applied to the columns of the table makes finding and selecting specific tables and columns very easy.

This process can be repeated for every SQL Server instance used by the business to generate a single mapped servers master excel file allowing the user to find any table or column available to the organization quickly.

Applying some colour coding like below adds to the ease of use.

Image of excel file with mapped database server structure

How to use:

Simply open SQL Server Management Studio and from object explorer right click on the server name and select new query. This will open a window set to the master database of the server. Copy and paste the SQL below into this SQL Server window and execute. When the query is finished you will have created the table above.

/*
SCRIPT UPDATED
20180316
*/

USE [master]
GO

/*DROP TEMP TABLES IF THEY EXIST*/
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
	DROP TABLE #DatabaseList;

IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL
	DROP TABLE #TableStructure;

IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
	DROP TABLE #ErrorTable;

IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL
	DROP TABLE #MappedServer;
	
IF OBJECT_ID('tempdb..#TableCount') IS NOT NULL
	DROP TABLE #TableCount;
	
IF OBJECT_ID('tempdb..#Count') IS NOT NULL
	DROP TABLE #Count;

DECLARE @ServerName AS SYSNAME
DECLARE @Count INT

SET @ServerName = @@SERVERNAME

CREATE TABLE #DatabaseList (
	Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,ServerName SYSNAME
	,DbName SYSNAME
	);

CREATE TABLE [#TableStructure] (
	[DbName] SYSNAME
	,[SchemaName] SYSNAME
	,[TableName] SYSNAME
	,[ColumnName] SYSNAME
	,[KeyType] CHAR(7)
	) ON [PRIMARY];
	
CREATE TABLE [#TableCount] (
	[Id_TableCount] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,[ServerName] SYSNAME
	,[DatabaseName] SYSNAME
	,[SchemaName] SYSNAME
	,[TableName] SYSNAME
	,[SQLCommand] VARCHAR(MAX)
	,[TableCount] INT
	);

CREATE TABLE #Count (ReturnedCount INT);

/*THE ERROR TABLE WILL STORE THE DYNAMIC SQL THAT DID NOT WORK*/
CREATE TABLE [#ErrorTable] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY];

/*
A LIST OF DISTINCT DATABASE NAMES IS CREATED
THESE TWO COLUMNS ARE STORED IN THE #DatabaseList TEMP TABLE
THIS TABLE IS USED IN A FOR LOOP TO GET EACH DATABASE NAME
*/
INSERT INTO #DatabaseList (
	ServerName
	,DbName
	)
SELECT @ServerName
	,NAME AS DbName
FROM master.dbo.sysdatabases WITH (NOLOCK)
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC

/*VARIABLES ARE DECLARED FOR USE IN THE FOLLOWING FOR LOOP*/
DECLARE @sqlCommand AS VARCHAR(MAX)
DECLARE @DbName AS SYSNAME
DECLARE @i AS INT
DECLARE @z AS INT

SET @i = 1
SET @z = (
		SELECT COUNT(*) + 1
		FROM #DatabaseList
		)

/*WHILE 1 IS LESS THAN THE NUMBER OF DATABASE NAMES IN #DatabaseList*/
WHILE @i < @z
BEGIN
	/*GET NEW DATABASE NAME*/
	SET @DbName = (
			SELECT [DbName]
			FROM #DatabaseList
			WHERE Id = @i
			)
	/*CREATE DYNAMIC SQL TO GET EACH TABLE NAME AND COLUMN NAME FROM EACH DATABASE*/
	SET @sqlCommand = 'USE [' + @DbName + '];' + '

INSERT INTO [#TableStructure]
SELECT DISTINCT ' + '''' + @DbName + '''' + ' AS DbName
	,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
	,T.NAME AS TableName	
	,C.NAME AS ColumnName
	,CASE 
		WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsPrimaryKey'') = 1 
			THEN ''Primary'' 
		WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsForeignKey'') = 1 
			THEN ''Foreign''
		ELSE NULL 
		END AS ''KeyType''
FROM SYS.TABLES AS t WITH (NOLOCK)
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS iskcu WITH (NOLOCK) 
ON SCHEMA_NAME(SCHEMA_ID) = iskcu.TABLE_SCHEMA 
	AND T.NAME = iskcu.TABLE_NAME
	AND C.NAME = iskcu.COLUMN_NAME
ORDER BY SchemaName ASC
	,TableName ASC
	,ColumnName ASC;
';

	/*ERROR HANDLING*/
	BEGIN TRY
		EXEC (@sqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH

	SET @i = @i + 1
END

/*
JOIN THE TEMP TABLES TOGETHER TO CREATE A MAPPED STRUCTURE OF THE SERVER
ADDITIONAL FIELDS ARE ADDED TO MAKE SELECTING TABLES AND FIELDS EASIER
*/
SELECT DISTINCT @@SERVERNAME AS ServerName
	,DL.DbName
	,TS.SchemaName
	,TS.TableName
	,TS.ColumnName
	,TS.[KeyType]
	,',' + QUOTENAME(TS.ColumnName) AS BracketedColumn
	,',' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) AS BracketedTableAndColumn
	,'SELECT * FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectTable]
	,'SELECT ' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) + ' FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectColumn]
	,'SELECT COUNT(*) FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + 'WITH (NOLOCK)' AS [PerformTableCount]
INTO #MappedServer
FROM [#DatabaseList] AS DL
INNER JOIN [#TableStructure] AS TS ON DL.DbName = TS.DbName
ORDER BY DL.DbName ASC
	,TS.SchemaName ASC
	,TS.TableName ASC
	,TS.ColumnName ASC

/*
HOUSE KEEPING
*/
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
	DROP TABLE #DatabaseList;

IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL
	DROP TABLE #TableStructure;
	
INSERT INTO #TableCount (
	[ServerName]
	,[DatabaseName]
	,[SchemaName]
	,[TableName]
	,[SQLCommand]
	)
SELECT DISTINCT [ServerName]
	,[DbName]
	,[SchemaName]
	,[TableName]
	,[PerformTableCount]
FROM #MappedServer
ORDER BY [ServerName] ASC
	,[DbName] ASC
	,[SchemaName] ASC
	,[TableName] ASC

SET @i = 1
SET @z = (
		SELECT COUNT(*) + 1
		FROM #TableCount
		)
	
WHILE @i < @z
BEGIN
	SET @SQLCommand = (
			SELECT SQLCommand
			FROM #TableCount
			WHERE Id_TableCount = @i
			)

	--ERROR HANDLING
	BEGIN TRY
		INSERT INTO #Count
		EXEC (@SqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH

	SET @Count = (
			SELECT ReturnedCount
			FROM #Count
			)

	TRUNCATE TABLE #Count

	UPDATE #TableCount
	SET TableCount = @Count
	WHERE Id_TableCount = @i;

	SET @i = @i + 1
END

SELECT *
FROM #ErrorTable;

IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
	DROP TABLE #ErrorTable;

/*
THE DATA RETURNED CAN NOW BE EXPORTED TO EXCEL
USING A FILTERED SEARCH WILL NOW MAKE FINDING FIELDS A VERY EASY PROCESS
*/
SELECT DISTINCT ms.ServerName
	,ms.DbName
	,ms.SchemaName
	,ms.TableName
	,ts.TableCount AS CountOfRows
	,ms.ColumnName
	,ms.KeyType
	,ms.BracketedColumn
	,ms.BracketedTableAndColumn
	,ms.SelectColumn
	,ms.SelectTable
FROM #MappedServer AS ms
LEFT JOIN #TableCount AS ts ON ms.ServerName = ts.ServerName
AND ms.DbName = ts.DatabaseName
AND ms.SchemaName = ts.SchemaName
AND ms.TableName = ts.TableName
ORDER BY ms.DbName ASC
	,ms.SchemaName ASC
	,ms.TableName ASC
	,ms.ColumnName ASC;
	
IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL
	DROP TABLE #MappedServer;

IF OBJECT_ID('tempdb..#TableCount') IS NOT NULL
	DROP TABLE #TableCount;
	
IF OBJECT_ID('tempdb..#Count') IS NOT NULL
	DROP TABLE #Count;

 

How to get the default error log path for SQL Server with T-SQL

Below is a script to get the default error log path for SQL Server and set it as a variable. 

USE MASTER;
GO

DECLARE @LogPath AS VARCHAR(MAX)
DECLARE @ErrorLogPath TABLE (
	LogDate DATETIME
	,ProcessInfo VARCHAR(255)
	,PathText VARCHAR(MAX)
	);

INSERT INTO @ErrorLogPath
EXEC xp_readerrorlog 0
	,1
	,N'Logging SQL Server messages in file';

SET @LogPath = (
		SELECT REPLACE(REPLACE(REPLACE(PathText, 'Logging SQL Server messages in file ', ''), '''', ''), 'ERRORLOG.', '')
		FROM @ErrorLogPath
		);

SELECT @LogPath AS DefaultLogPath;
GO

 

How to copy a large result set from SQL Server Management Studio to Excel

So you’ve tried copying and pasting the results of a query into an excel file only to get the out of memory exception. Now the fun starts!

There are multiple options to achieve copying data from SQL Server Management Studio to excel however most of them are a pain.

You’ve tried the save as option but the csv and text files have jumbled up content. You could use the Management Studio export function, but this is intended for physical tables not results sets and you might not have the permissions to create tables in the environment. You could use the bcp Utility but you’ve probably read leaving this option turned on represents a security risk. You could create an SSIS package . . . yeah that’s an efficient and effective option when you just want the damn results of an ad hoc query!!!

I’d suggest splitting the result set into chunks. You can then copy and paste the chunks into the excel file without running out of memory. Sure it’s kinda manual but trust me it’ll take less time than the options above.

I’d wager you probably only need the result set split into two, so you’ve to copy and paste twice rather than once. Not that big a deal right? I’ve even provided some code below that will really move things along.

Start by writing your query results into a temporary tablet called #QueryResult, for example SELECT * INTO #QueryResult FROM TableName.

Then all you need to do is determine how many segments you need. NTILE(n) is a function that allocates your output into n segments, each of the same size (give or take rounding when the number of rows isn’t divisible by n).

So this produces an output like:

Id Name Ntile
1 Mickey 1
2 Leo 1
3 Raph 2
4 Donnie 2

Start by leaving n set to the default of 2. Once the data is written to the table #QueryResult run the code below in the same SSMS window the temp table was created in. Running the code should produce the same number of returned result sets as the n value you provided. Use a higher n number to create more segments if you still run out of memory when you try to copy and paste the first segment.

/*
Write your query results to a temp table here
i.e. SELECT * INTO #QueryResult FROM TableName
*/
DECLARE @n INT
DECLARE @i INT

/*
Set n to how many segments/results set returned you need
*/
SET @n = 2
SET @i = 1

SELECT *
	,NTILE(@n) OVER (
		ORDER BY RowNum
		) AS NtileGroup
INTO #Export
FROM (
	SELECT ROW_NUMBER() OVER (
			ORDER BY (
					SELECT NULL
					)
			) AS RowNum
		,*
	FROM #QueryResult
	) AS a

WHILE @i <= @n
BEGIN
	SELECT *
	FROM #Export
	WHERE NtileGroup = @i
	ORDER BY RowNum ASC

	SET @i = @i + 1
END

DROP TABLE #QueryResult

DROP TABLE #Export

 

So that’s it, you should now be able to copy and paste your results. Maybe someday in the future Microsoft will add the option of saving results directly to excel . . .