Category Archives: SQL Scripts

How to restart an Interrupted Database Restore in SQL Server?

Steps to restart an Interrupted Database Restore in SQL Server

There may scenario when you are restoring VLDB database backup and the restore database command interrupted due network failure, power failure, SQL Server Service restart or cluster failover. In this kind of scenario you can easily start the interrupted restore from the point of failure using WITH RESTART command.

Before you start restoring the database using WITH RESTART option check database is in Restoring State not in any other state.

You can check the database state either from SSMS object explorer or by executing below T-SQL command

select name,state_desc from sys.databases

Once confirmed that database is in restoring state execute the backup command WITH RESTART clause

RESTORE DATABASE [AdventureWorks]
FROM DISK ='D:\Backup\AdventureWorks.bak'
WITH RESTART, STATS = 20
GO

OpenDataSouce function – to query OLEDB Data Source

OPENDATASOURCE
OpenDataSouce function helps you to get ad hoc connection information as part of a four-part object name as an one time alternative of linked server. You don’t have to specify or create the linked server to query other data sources (i.e. MS Excel, MS Access, MSSQL Older version to newer version etc.) if you are querying it infrequently.

You can use OPENDATASOURCE for the OLEDB data sources those are accessed infrequently, for several time use linked server as it provides more functionality.

You can get more information about the arguments of OpenDataSource function on MSDN site.

To use the OPENDATASOURCE you have to enable the ad hoc distributed queries. You required to have execute permission to use OPENDATASOURCE fucntion.

Execute below query to enable it

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE WITH OVERRIDE
GO

Make sure Provider AllowInProcess and DynamiceParameters value is checked. For example lets enable it for SQLNCLI10 provider.

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'SQLNCLI10', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'SQLNCLI10', N'DynamicParameters', 1
GO

OpenDataSource Examples

-- SQL Server 2000/2005/2008
-- You can use SQLNCLI10 provider for SQL Server 2008 as well
SELECT
    * FROM
OPENDATASOURCE (
   'SQLNCLI', 
   'Data Source=SQLInstanceName;Catalog=DBName;User ID=SQLLogin;Password=Password;').DBName.SchemaName.TableName
   
   
SELECT *
FROM OPENROWSET('SQLNCLI',
   'DRIVER={SQL Server};SERVER=SQLInstanceName;UID=SQLLogin;PWD=Password',
   'select * from DBName..TableName')  
   

-- SQL Server 2012
SELECT
    * FROM
OPENDATASOURCE (
   'SQLNCLI11', 
   'Data Source=SQLInstanceName;Catalog=DBName;User ID=SQLLogin;Password=Password;').DBName.SchemaName.TableName
   
   
SELECT *
FROM OPENROWSET('SQLNCLI11',
   'DRIVER={SQL Server};SERVER=SQLInstanceName;UID=SQLLogin;PWD=Password',
   'select * from DBName..TableName')  

--Access DB
SELECT * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0', 
                              'Data Source=D:\MyDB\MyAccessDB.accdb')...TableName   

Common Errors
Error 1#
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider “SQLNCLI11” has not been registered.
Solution: You will get above error if you have mentioned SQLNCLI11 while running OPENDATASOURCE query on SQL Server 2008 or lower version, it will work fine on SQL Server 2011. You can check list of registered provider by browsing Server Objects -> Linked Servers -> Provider in SSMS
Error 2#
Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.

Solution: Enable the ad hoc distributed queries by executing above SP_CONFIGURE query.

T-SQL Script to check SQL Server Instance TCP/IP Port Number

SQL Server Error Log

-- Check the error log for port number
EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
GO

Executing DMV Script

-- you must connect remotely using TCP/IP
SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

For Dynamic Port Number
— Read the registry for port number

-- Execute below script if SQL Server is configured with dynamic port number
DECLARE       @portNo   NVARCHAR(10)
 
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value      = @portNo OUTPUT
 
SELECT [PortNumber] = @portNo
GO

For Static Port Number

-- Execute below script if SQL Server is configured with static port number
DECLARE       @portNo   NVARCHAR(10)
 
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value      = @portNo OUTPUT
 
SELECT [PortNumber] = @portNo
GO

T-SQL Script to Check if LiteSpeed is installed or not on the server

You can execute below script against the SQL Server instance and check whether LiteSpeed is installed on the server or not.

T-SQL script is checking registry to check the LiteSpeed version value.

declare @regkeyval varchar(20), @value varchar(255), @rc int
exec @rc=master.dbo.xp_regread 
  @rootkey= 'hkey_local_machine',
  @key='software\imceda\sqllitespeed\engine',
  @value_name='version',
  @regkeyval=@value output

set @value =isnull(@value,'-') 

if @value <> '-'
select 
 @@servername as SQLInstanceName
,case serverproperty('IsClustered') when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
 else cast(serverproperty('machinename') as varchar)
 end as WindowsServerName
,@value as LiteSpeedVersion 
,'LiteSpeed is installed' as LiteSpeedStautsCheck
else
select 
 @@servername as SQLInstanceName
,case serverproperty('IsClustered') when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
 else cast(serverproperty('machinename') as varchar)
 end as WindowsServerName
,'LiteSpeed is not installed' as LiteSpeedStautsCheck

T-SQL Script to Check the Native Compressed backup file size and Backup Compression Ratio

First of all make sure backup compression is enabled on the SQL Server. You can execute below query to check the backup compression.

select name,[description],value_in_use from sys.configurations where name like '%backup%'

Execute below script to check the compressed backup file size and backup compression ratio.

Declare @FromDate as datetime
-- Specify the from date value
set @FromDate = GETDATE() -1

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SQLServerName, 
   msdb.dbo.backupset.database_name,  
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
       WHEN 'I' THEN 'Differential'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   DATEDIFF (SECOND, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) 'Backup Elapsed Time (sec)',
   msdb.dbo.backupset.compressed_backup_size AS 'Compressed Backup Size in KB',
  (msdb.dbo.backupset.compressed_backup_size/1024/1024) AS 'Compress Backup Size in MB',
   CONVERT (NUMERIC (20,3), (CONVERT (FLOAT, msdb.dbo.backupset.backup_size) /CONVERT (FLOAT, msdb.dbo.backupset.compressed_backup_size))) 'Compression Ratio',
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE 
CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= @FromDate
AND msdb.dbo.backupset.backup_size > 0 
ORDER BY  
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_finish_date