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
go

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

ObjectName:
Name of Object such as Stored Procedure, View etc.
Type: 
Type of SQL Server Object ( P for Stored Procedure, V for View etc.)
ObjectTypeDescription:
Full description of Object such as Stored Procedure,View etc.
CrossQueryObject: 
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
ObjectDefinition:
You will have object definition so you can do further analysis

USE [TestDB] --> Change the database name as per your Requirements
GO
DECLARE @TempTable TABLE
  (
     ObjectName       VARCHAR(500),
     CrossQueryObject VARCHAR(500),
     CrossQueryType   VARCHAR(500),
     FirstValue       VARCHAR(100),
     OBJECT_ID        INT
  )DECLARE @SP_Object_ID AS VARCHAR(50)DECLARE Object_Cursor CURSOR FOR
  SELECT OBJECT_ID
  FROM   sys.sql_modules sm
OPEN Object_Cursor
FETCH Next FROM Object_Cursor INTO @SP_Object_ID
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SP_Definition_XML XML-->Convert the definition into XML
      SELECT @SP_Definition_XML = CAST ('<M>'
      + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
      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
      
;
      
WITH CTE_Table
           
AS (SELECT t.value('.''VARCHAR(500)'AS Col
               
FROM   @SP_Definition_XML.nodes('/M'AS x(t)),
           
CTE_NumOfPeriods
           
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 '%=%'),
           
CTE_Table1
           
AS (SELECT ObjectName,
                      
CrossQueryObject,
                      
RTRIM(LTRIM(REPLACE(REPLACE(SUBSTRING(CrossQueryObject1CHARINDEX('.'CrossQueryObject1) - 1), ']'''), '['''))) FirstPart
               
FROM   CTE_NumOfPeriods)-->Insert into Temp table  
      
INSERT INTO @TempTable
      
SELECT *,
             
@SP_Object_ID
      
FROM   (SELECT ObjectName,
                     
CrossQueryObject,
                     
CASE
                       
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,
                     
FirstPart
              
FROM   CTE_Table1
              
WHERE  FirstPart NOT IN (SELECT name
                                         
FROM   sys.schemas) )) DR
      
WHERE  DR.QueryType <> 'Not Valid'

      
PRINT OBJECT_NAME(@SP_Object_ID)
            + 
' analysis is Completed'

      
FETCH Next FROM Object_Cursor INTO @SP_Object_ID
  
END

CLOSE 
Object_Cursor
DEALLOCATE Object_Cursor
SELECT T.ObjectName,
       
o.TYPE,
       
o.type_desc   AS ObjectTypeDescription,
       
T.CrossQueryObject,
       
T.CrossQueryType,
       
sm.definition AS ObjectDefinition
FROM   @TempTable T
       
INNER JOIN sys.objects o
               
ON T.OBJECT_ID o.OBJECT_ID
       
INNER JOIN sys.sql_modules sm
               
ON T.OBJECT_ID sm.OBJECT_ID



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.

USE MASTER
GO
SELECT spid,
       blocked                   AS BlockingSPID,
       (SELECT CRI.TEXT
        FROM   sysprocesses st
               CROSS apply sys.Dm_exec_sql_text(sql_handle) CRI
        WHERE  spid = s.blocked) AS BlockingQuery,
       PROGRAM_NAME,
       nt_userName,
       loginame,
       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

USE MASTER
GO
SELECT spid,
       PROGRAM_NAME,
       nt_userName,
       loginame,
       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.


USE MASTER
GO
DECLARE @LoginName VARCHAR(100)
SET @LoginName='Domain\LoginName' --> Provide the LoginName Windows or SQL Server
DECLARE @DBName VARCHAR(1000)
IF EXISTS (SELECT *
           FROM   syslogins
           WHERE  loginname = @LoginName)
  BEGIN
      PRINT @LoginName + ' login exists'

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

      --OPEN CURSOR
      OPEN CUR

      --Fetch First Row
      FETCH NEXT FROM CUR INTO @DBName

      --Loop
      WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @SQL NVARCHAR(MAX);
            DECLARE @BIGSQL NVARCHAR(MAX);
            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 + '''';

            EXEC(@BIGSQL)

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

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

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

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

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

            --Fetch next DataBase name 
            FETCH NEXT FROM CUR INTO @DBName
        END

      --Close and Deallocate Cursor
      CLOSE CUR

      DEALLOCATE CUR

      PRINT 'Dropping Login ' + @LoginName

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

      EXECUTE(@DropLoginSQL)
  END
ELSE
  BEGIN
      PRINT 'Login Does NOT EXISTS'
  END 

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

Scenario: 

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.

Solution: 

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