:::: MENU ::::


  • May 10 / 2017
  • 0

SQL Server Management Studio (SSMS) 2017 available now

SQL Server Management Studio (SSMS) 2017 is now available for download now. Click here to get it. Download page has further information about new features and compatibility.

Following are some highlights

This release features improved compatibility with previous versions of SQL Server, a stand-alone web installer, and toast notifications within SSMS when new releases become available.

SSMS 17.x can be installed side-by-side with SSMS 16.X or SQL Server 2014 SSMS and earlier.

Support SQL Server 2008 and onward. There is no explicit block for SQL Server 2000 or SQL Server 2005, but some features may not work properly.

Supports Windows 10, Windows 8, Windows 8.1, Windows 7 (SP1), Windows Server 2016, Windows Server 2012 (64-bit), Windows Server 2012 R2 (64-bit), Windows Server 2008 R2 (64-bit)

Click here to get full list of changes

Click here for list of known issues and work arounds

  • Apr 11 / 2017
  • 0
System Stored Procedures, T-SQL Tips and Tricks

Considerations while using SP_Rename to change name of object

Before going to concerned point that i want to share through this post i.e. considerations while using sp_rename,  i would like to summarize the scope and features of Sp_Rename system stored procedure.

Scope and Features of Sp_Rename

Sp_Rename is used the change the name of any user object in a SQL Server database or even the database itself. It accepts three parameters for current name, new name and object type. Generally object type parameter (@objtype) can be skipped unless there is a name conflict for concerned object.

Required Permissions

Following are the permissions required for different type of objects

Permissions for using sp_rename in SQL Server T-SQL

Issues while using sp_rename

There are some associated issues while using sp_rename. These should be known and taken care of

Using schema name in new name parameter

You can use schema name along with object name in current name of object but DO NOT use it in new name provided to SP as parameter. It would also take the schema name as object name. Consider following example for this scenario.


-- Use any test DB
Use Test
-- Create table
Create table TableName_Old (id int identity(1,1), FName varchar(50))
-- Verify Table name
select schema_name(schema_id) as SchemaName, name
from sys.objects
where type = 'U' and name like 'TableName_Old'

-- Rename table
exec sp_rename 'dbo.TableName_Old', 'dbo.TableName_New'

And when we tried to verify with table with new name the result is empty.

-- Verify table with new name
select schema_name(schema_id) as SchemaName, name
from sys.objects
where type = 'U' and name like 'TableName_new'

Let us run a generic search to include more tables.

-- Verify table with generic search
select schema_name(schema_id) as SchemaName, name
from sys.objects
where type = 'U' and name like '%TableName%'

Now we have following result that shows that schema dbo is also prefixed in name of table.
Result of sp_rename T-SQL

So we have to execute another sp_rename command to set the required name of this table.

-- Rename and remove the schema prefix
exec sp_rename 'dbo.[dbo.TableName_New]', 'TableName_New'

This fixed out the issue and we have table name as TableName_New.

SP_rename for name correction T-SQL

Effect on Associated Indexes

Associated index will be automatically renamed whenever a PRIMARY KEY or UNIQUE constraint is renamed.
In same way if a renamed index is associated to a PRIMARY KEY constraint,
the PRIMARY KEY constraint will be automatically renamed.

References to Object

Renaming an object will not update its references to that object. This task should be done manually to avoid code failure after rename.

Code definition

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view.


Sp_rename should be avoided if you have option to drop and recreate an object with a different name. Analysis of code base is imperative to make sure that code will not break after rename is performed.

  • Mar 09 / 2017
  • 0
Database Migrations, dbDigger, T-SQL Scripts

Code snippets for database migration task

Following are some code snippets that i used during a database migration task from one server to another. Backup restore method is used in this migration task

DB list on source server

Get the database list on source server that are required to migrate

select name, user_access, state_desc, create_date
from sys.databases
where name in
( 'comma separated list of specific databases to be migrated')
order by name

Note DB properties

Note the important database properties on source server so that we may verify these on destination server after migration is complete.

select name, is_published, is_subscribed, is_cdc_enabled, is_trustworthy_on, is_read_only

from sys.databases where name in
( 'comma separated list of specific databases to be migrated')
order by name

Size of DBs

Get size of all databases that are to be migrated. It would help to estimate the disk capacity on destination and also the time estimate for backup and restore process.

SELECT DB_NAME(database_id) AS DatabaseName, type_desc,
cast(sum( size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
where DB_NAME(database_id) in
( 'comma separated list of specific databases to be migrated')
GROUP BY DB_NAME(database_id), type_desc
ORDER BY DB_NAME(database_id), type_desc DESC

Get which DBs are in Avaiability Group

AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
where dbcs.database_name in ('Comma seperated list of DBs to be migrated')

Set DBs to READ ONLY and Restricted mode on source server

Before backing up the databases we will do following operations for each DB. As here i have databases in AG so i also included command to set DB out of AG so that i may put it to READ ONLY mode next.


Backup Databases

We will take backup of all databases through following script. Go through the option used for backup and also put a valid path. Backup file name will be generated automatically.

-- Declare cursor for database names
SELECT name FROM sys.databases
WHERE name in
( 'Comma seperated list of databases')

-- Declare variable to hold DBName
declare @Db_name varchar(100)

OPEN BackupCursor
FETCH NEXT FROM BackupCursor INTO @Db_name


-- Backup code
declare @Backup_Path Varchar(500) = 'Full Backup Path here'

DECLARE @fileName VARCHAR(400)  = @Backup_Path +'\'+ replace(@@servername, '\','_' ) + '_' + @Db_name+ '_' + 'FULL'+ '_' + LEFT(CONVERT(VARCHAR,getdate(), 120), 10)
+'.BAK'   -- filename for backup

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'BACKUP DATABASE ['+ @Db_name+'] TO DISK ='+   ''''+@fileName+ ''' WITH COPY_ONLY, CHECKSUM, INIT,COMPRESSION, STATS = 10;'

EXEC (@sqlCommand)

RESTORE  verifyonly from disk = @fileName
print 'Backup file path is '+@fileName

FETCH NEXT FROM BackupCursor INTO @Db_name
-- Close and deallocate the cursor
CLOSE BackupCursor


Get restore command for each DB one by one

Put DB name in first line of script one by one and get the restore commands for last full backup of that specific database

     declare @db_name varchar(100) = 'DBName'

Declare @tmp table
(StartDT datetime, BKP_Type char(1), RestorePart Varchar(2000), FilePart varchar(max))

If exists (select name from sys.databases where name = @Db_name)

Insert @tmp
SELECT s.[backup_start_date] ,s.[type]
,'Restore database ' + s.[database_name] +'
From Disk = ''' + Replace (replace (B.[physical_device_name], 'SQL Backups 2', 'SQL Backups 2'), 'DROPOFF', 'RECOVERY') + '''
with replace, keep_cdc, recovery, stats =1 ' as RestorePart    ,
'      , Move ''' + f.[logical_name] + ''''
+ ' TO ''' + f.[physical_name] + '''' as FilePart
FROM [msdb].[dbo].[backupset] S
inner join [msdb].[dbo].[backupfile] F
on S.backup_set_id = f.backup_set_id
inner join [msdb].[dbo].[backupmediafamily] B
on s.media_set_id = b.media_set_id
where s.type ='d'
and s.database_name = @Db_name
and s.backup_start_date =
(select max(s.backup_start_date)
from [msdb].[dbo].[backupset] s
where s.type ='d' and s.database_name = @Db_name )
order by s.backup_set_id desc

--select * from @tmp

select distinct '-- Latest Full backup for database ' + @Db_name + ' is from ' + cast(StartDT as varchar(20))
from @tmp

Select '--Run this on DR replica for initial restore:' as [--RestoreStatement]
union all
Select Distinct RestorePart
from @tmp
union all
select FilePart from @tmp

Select 'Database ' + @Db_name + ' does not exist on ' + @@Servername
Set nocount off

Set databases to READ WRITE mode on destination server



Fix and verify the orphan users

-- Fix orphaned users in DB
<pre>EXEC     [dbo].[spFixOrphanUsers]    @DatabaseNM = 'DBNameHere'

-- Verify the orphaned users in DB
USE DBNameHere
sp_change_users_login @Action='Report'


Update the DB properties

Following scripts will generate commands to fix the specific database property.

-- Set DB owner to sa
select suser_sname(owner_sid) , 'ALTER AUTHORIZATION ON DATABASE::'+name+'  TO sa;'
from sys.databases
where suser_sname(owner_sid) &lt;&gt; 'sa'

-- Set compatibility level
from sys.databases
where COMPATIBILITY_LEVEL &lt;&gt; 120

-- Set AutoCreateStats
from sys.databases
where is_auto_create_stats_on = 0

-- Set AutoUpdateStats
from sys.databases
where is_auto_update_stats_on = 0



  • Mar 18 / 2015
  • 0
dbDigger, Monitoring and Analysis, SQL Server Filegroups

Get size of all databases on a server

Following are three scripts to get

  • Get size of all databases on a server
  • Get size of all databases in terms of data and log
  • Get size of all data and log files

Get Size of all databases

SELECT DB_NAME(database_id) AS DatabaseName,
cast(sum( size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
GROUP BY database_id
ORDER BY database_id

Get Size of all database in data and log category

SELECT DB_NAME(database_id) AS DatabaseName, type_desc,
cast(sum( size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
GROUP BY database_id, type_desc
ORDER BY database_id, type_desc DESC 

— Get Size of all data and log files

SELECT DB_NAME(database_id) AS DatabaseName, type_desc, name, physical_name,
cast( (size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
WHERE DB_NAME(database_id) = 'DBToFiletr'
ORDER BY database_id, type_desc DESC, name 
  • Feb 10 / 2015
  • 0
DBA Interview questions, dbDigger, SQL Server Training, SQL Server versions and editions

SQL Server versions, editions, service packs, OS and deployment

Like other Microsoft technologies SQL Server follows the categorization of versions and editions. Updates are applied mainly through service packs and patches that are released by Microsoft for each version. Understanding of these concepts is important for DBAs as they have to make use of this information for deployment, upgrades and proposals. It is better for beginner level DBAs to explore this information prior to work on these during their jobs.

Click here to download presentation on this topic.

  • Nov 12 / 2014
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions

Generate object level permissions in a database

Following script can be used to generate object level permissions in a database. We can filter the results for specific user or type.

 SELECT Us.name AS username,  
     us.type_desc AS UserType,  
     schema_name(obj.schema_id)+'.'+Obj.name AS objectName,  
     dp.permission_name AS permission ,  
     dp.state_desc AS PermissionStatus  
 FROM sys.database_permissions dp  
 JOIN sys.database_principals Us ON dp.grantee_principal_id = Us.principal_id  
 JOIN sys.objects Obj ON dp.major_id = Obj.object_id --where us.type_desc &lt;&gt; 'DATABASE_ROLE'  
 ORDER BY Us.name  
 -- get user permissions on whole DB  
 EXECUTE AS USER = 'userName';  
 SELECT * FROM fn_my_permissions(NULL, 'DATABASE')   
 -- get user permissions on whole DB  
  a.class_desc, permission_name, state_desc, b.name  
 from sys.database_permissions a  
 inner join sys.database_principals b  
  on a.grantee_principal_id = b.principal_id  
  and permission_name not in ('connect','view definition','SELECT')  
  and b.type_desc in ('SQL_USER','WINDOWS_USER')  
  and state_desc = 'GRANT'  
  and class_desc = 'DATABASE'  

Consult us to explore the Databases. Contact us