DBA - How To Migrate Database From One SQL Server Instance To Another SQL Server Instance

Suppose your company has one SQL Server that has hundreds of databases which are accessed by different application. Over the time the numbers of Databases are increased on this server and hurting performance. Your company has made decision to purchase a new SQL Server. They want you to move (migrate) some of the databases from current SQL Server to new build SQL Server.

Let’s follow step by step approach. In our Example we will be migrating TEST_PUB Database from MSSQL2008 Instance to MSSQL Instance as shown below
Fig 1: Migrate SQL Server Database

Step 1:
Detach database from current SQL Server Instance.  Before Detaching database run below query to get location for mdf and log file.

USE TEST_PUB --> Change with your Database Name

select * From sys.database_files

Now we know the location of mdf and ldf files of database. Let's detach the database. Right Click on SQL Database--> Tasks-->Detach

Fig 2: Detach SQL Server Database

Fig 3: Kill Connections and Detach SQL Database

Step 2: 
Copy mdf and ldf files to the folder/s which you are using for new SQL Server instance. For this post, I have mdf and ldf files for MSSQL2008 Instance in below location

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008\MSSQL\DATA

My folder location for MSSQL SQL instance is below. This is the instance where we are going to attach our database.

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL\MSSQL\DATA

Please note that I am using the same folder for mdf and ldf files but that is not best practice. You will be using different Drives for mdf and ldf.
Fig 4: Copy mdf and ldf files to required folders

Step 3:
Once the mdf and ldf files are copied to the required folder. Let's go to SQL Server Instance where we want to attach the database.

Right Click on the Databases Tab and then Click Attach Tab as shown below
Fig 5: Attach SQL Server Database

Fig 6: Attach SQL Server Database Attach Wizard

Navigate to the location where you have copied mdf file for your Database and Choose mdf file as shown below.
Fig 7: Attach SQL Server DB by using Attach Wizard

Fig 8: Attach SQL Server Database step by Step by using Attach Wizard

Once you hit Ok, it is going to take few seconds to attach database to SQL Server Instance. TEST_PUB is successfully attached to MSSQL SQL Server Instance.

Fig 9: Database Attached Successfully

DBA - How To Find Objects ( Stored Procedures, Views etc) In SQL Server Which Are Using Cross Database or Cross Server Queries

We often come across this problem where we have to get more information about SQL Server objects such as Stored Procedure, Views etc if they are using Cross Server (Linked Server) or Cross Database queries. This can be very common scenario when we are planning to migrate some of the databases from one SQL Server to other SQL Server. In those cases we want to make sure we are taking care of Cross Database and Cross Server queries which are used in different objects as part of SQL Server Database Migration.

The below script is going to return you following information

Name of Object such as Stored Procedure, View etc.
Type of SQL Server Object ( P for Stored Procedure, V for View etc.)
Full description of Object such as Stored Procedure,View etc.
Will return you fully qualified name
CrossQueryType :
This will return you three values depending upon the analysis.
i) Cross Database
ii) Cross Server
iii) Using Fully Qualified Name for Current Database
You will have object definition so you can do further analysis

USE [TestDB] --> Change the database name as per your Requirements
     ObjectName       VARCHAR(500),
     CrossQueryObject VARCHAR(500),
     CrossQueryType   VARCHAR(500),
     FirstValue       VARCHAR(100),
     OBJECT_ID        INT
  FROM   sys.sql_modules sm
OPEN Object_Cursor
FETCH Next FROM Object_Cursor INTO @SP_Object_ID
      DECLARE @SP_Definition_XML XML-->Convert the definition into XML
      SELECT @SP_Definition_XML = CAST ('<M>'
      REPLACE(definition, '&', ''),
       CHAR(13), ' '),
       CHAR(10), ' '),
       '<', ''), '>', ''), '', ''), '''', ''), ',', ' '),
        ' ', '</M><M>')          + '</M>' AS XML)   
FROM   sys.sql_modules
WHERE  OBJECT_ID @SP_Object_ID-->Converting XML into Table
AS (SELECT t.value('.''VARCHAR(500)'AS Col
FROM   @SP_Definition_XML.nodes('/M'AS x(t)),
AS (SELECT OBJECT_NAME(@SP_Object_ID)            AS ObjectName,
LEN(Col) - LEN(REPLACE(Col'.''')) NumOfPeriods,
Col                                   AS CrossQueryObject
FROM   CTE_Table
WHERE  LEN(Col) - LEN(REPLACE(Col'.''')) >= 2
AND Col NOT LIKE '%=%'),
AS (SELECT ObjectName,
RTRIM(LTRIM(REPLACE(REPLACE(SUBSTRING(CrossQueryObject1CHARINDEX('.'CrossQueryObject1) - 1), ']'''), '['''))) FirstPart
FROM   CTE_NumOfPeriods)-->Insert into Temp table  
FROM   (SELECT ObjectName,
WHEN FirstPart IN (SELECT name
FROM   sys.serversTHEN 'Cross Server'
WHEN FirstPart IN (SELECT name
FROM   sys.databases
WHERE  name <> DB_NAME()) THEN 'Cross Database'
WHEN FirstPart DB_NAME() THEN 'Using Fully Qualified Name for Current Database'
ELSE 'Not Valid'
END QueryType,
FROM   CTE_Table1
WHERE  FirstPart NOT IN (SELECT name
FROM   sys.schemas) )) DR
WHERE  DR.QueryType <> 'Not Valid'

' analysis is Completed'

FETCH Next FROM Object_Cursor INTO @SP_Object_ID

DEALLOCATE Object_Cursor
SELECT T.ObjectName,
o.type_desc   AS ObjectTypeDescription,
sm.definition AS ObjectDefinition
FROM   @TempTable T
INNER JOIN sys.objects o
INNER JOIN sys.sql_modules sm

Snapshot for Output

DBA - How To Find Blocked Processes In SQL Server

Sometime we run a query and expect that to complete quickly but it kept running. Finally we ask DBA what is blocking this query? Blocking happens when a process hold a lock and other process has to wait till the first process completes.

To understand this, First we are going to start a process that is going to hold update lock on a table and then in second process we will try to update the same column with different values.

Fig 1: Starting First Process

We have executed query with Begin Tran , this query is updating CountryName in dbo.Countries table. We have not Commit this transaction.

Open a new query window and run query as shown in Fig 2.
Fig 2: Initiating second update query on dbo.Countries table

You have noticed that the status of second query is Executing Query...  and it is not completing at all. 

Let's check what is blocking our second query.

1-- Use sp_who or sp_who2
We can use sp_who or sp_who2 to see which processes are blocked by other processes. sp_who2 provide little more information such as Program name which initiated query etc.

Fig 3: Find Blocked Processes in SQL Server by using sp_who or sp_who2

2--Use Sysprocesses

The below query can be used to get all blocked processes with blocking SPIDs.

SELECT spid,
       blocked                   AS BlockingSPID,
        FROM   sysprocesses st
               CROSS apply sys.Dm_exec_sql_text(sql_handle) CRI
        WHERE  spid = s.blocked) AS BlockingQuery,
       DB_NAME(s.dbid)           AS DatabaseName,
       CR.TEXT                   AS Query
FROM   sysprocesses s
       CROSS apply sys.Dm_exec_sql_text(sql_handle) CR
WHERE  blocked <> 0

Fig 4: Using sysprocesses to get blocked and blocking SPIDs in SQL Server

Now we know which process is blocking our process, we can further look into the details of it. If process is stuck and we want to kill it .

We can use Kill SPID. 

Note:Take a detail look before you kill any process.

DBA - How To Find Open Transactions In SQL Server

As SQL Server DBA or Developer we often face issues such as blocking etc. where we have to find open transactions.

In our step by step approach , we are going to open two transactions as shown below. The both transactions are update transactions which are updating records in different tables in different Databases.
Fig 1: Updating dbo.Country Table in TestDB

Fig 2: Updating dbo.Customer Table in Test1 Database

Let's run both of the queries. Now both transactions are open as we did not commit them. We can use DBCC OPENTRAN to find out open transactions.

Fig 3: Using DBCC OPENTRAN to find out open transactions in SQL Server

We know that we have two open transactions but DBCC OPENTRAN is only showing us one transaction. Well, DBCC OPENTRAN only shows open transactions for specific database for which it is executed. Here it is showing us only open transactions in TEST1 Database. 

Now we know the SPID ( server process ID) , we want to know which query is associated with this process id. We can use DBCC INPUTBUFFER(SPID) to get the query information as shown below

Fig 4: Using DBCC INPUTBUFFER to get Query from SPID (Process ID) in SQL Server

DBCC OPENTRAN AND DBCC INPUTBUFFER are very helpful but If we have more than one transactions open and we want to get all information in once, which query we can use to get all open transactions with database name,who executed these queries,sql query and program name etc.

This query can be used to get all above information

SELECT spid,
       DB_NAME(s.dbid) AS DatabaseName,
       CR.TEXT AS Query
FROM   sysprocesses s
       CROSS apply sys.Dm_exec_sql_text(sql_handle) CR
WHERE  open_tran = 1

By using Sysprocesses we don't have to query one database at a time to find out open transactions, it will return us all open transactions on SQL Server.
Fig 5: Using Sysprocesses to get all Open Transactions in SQL server with Query information

DBA - How To Drop Login and User Name From All Databases In SQL Server

The below code can be used to drop Login from SQL Server and  user name associated with this Login in different databases.

SET @LoginName='Domain\LoginName' --> Provide the LoginName Windows or SQL Server
           FROM   syslogins
           WHERE  loginname = @LoginName)
      PRINT @LoginName + ' login exists'

        --Get the list of databases
        SELECT name
        FROM   sys.databases
        WHERE  state = 0
        AND is_read_only=0

      OPEN CUR

      --Fetch First Row

            DECLARE @DropUSerSQL NVARCHAR(500)
            DECLARE @DropLoginSQL NVARCHAR(500)
            DECLARE @USER NVARCHAR(100)

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

            --> Get User Name for Login
            SET @SQL='
     (SELECT name into ##T FROM sys.database_principals where suser_sname(sid)='''''
                     + @LoginName + '''''' + ')'
            SET @BigSQL = 'USE [' + @dbName
                          + ']; EXEC sp_executesql N''' + @sql + '''';


            SET @USER=(SELECT Name
                       FROM   ##T)

            --> If User is owner of Database, Change the owner to sa
            IF ( @USER = 'dbo' )
                  PRINT 'Changed DATABASE owner FROM ' + @LoginName
                        + ' TO sa'

                  EXECUTE('USE [' + @dbName + '];EXEC sp_changedbowner ''sa''')
            --If not owner of DB , proceed to Drop User
                  PRINT 'Droping  USER ' + @USER + ' IN Database:'
                        + @DBName

                  SET @DropUSerSQL= 'DROP USER [' + @USER + ']'

                  EXECUTE('USE [' + @dbName + ']; EXEC sp_executesql N''' + @DropUSerSQL + '''')

            --Fetch next DataBase name 

      --Close and Deallocate Cursor


      PRINT 'Dropping Login ' + @LoginName

      SET @DropLoginSQL= 'DROP Login [' + @LoginName + ']'

      PRINT 'Login Does NOT EXISTS'

SSRS - How To Create SSRS Report with DateTime By Using Standard Subscription In SQL Server Reporting Services


I got this requirement where I have to generate SSRS Report with DateTime. I could have set Data Driven Subscription but that can be lengthy process. We can generate Report with Datetime by using Standard Subscription in SSRS. Follow the below steps.


Step 1: 
Create a SSRS report if you don't have one;) I have created Customer Report as shown below

Fig 1: Customer Report to Set up SSRS Subscription

Step 2: 
Go the the report on which you want to create subscription as shown and Choose Subscribe
Fig 2: Configure SSRS Standard Subscription

Step 3: 
Provide the required information as shown below, 2nd step is important as we have @Timestamp at the end of report name.This is going to add DateTime at the end of our report name.

 Fig 3: Generate SQL Server Report with Date Time 

Step 4: 
Let the report run on schedule and check the folder to confirm if report is generated with date time. As we can see that the report is generated with Datetime successfully.
Fig 4: Standard Subscription Windows File Share