How to move Indexes (Cluster and Non Cluster) from Primary File group to New File group?

As a DBA, many times there are requirements to segregate data in a Database into multiple file groups. There can be many reasons like below:

• Performance issues (Disk I\O)


• Disk space issues


• Data archiving etc

There are multiple solutions to resolve such issues:

• Move the big tables to other File groups in which frequency of transactions are higher


• Segregate the table’s data and it’s Indices (specially Cluster Indices) into different File groups and place the File groups to different drives


• Move the database having high volume of transactions to other disk drives etc

Here I will discuss about a step by step approach of moving the Indexes (Cluster and Non Cluster) from Primary File group to New File group. I did it once and it worked well on SQL Server 2008 R2.

What all do you need? 

• Database: AdventureWorks


• SQL Server : SQL Server 2008 R2 (I tested on Development Edition)

Steps:

1. If you are planning to perform these steps on any other database, I would suggest, take a Full backup of the database after bringing down all the Applications down so that there is no connectivity on the database.

2. Add File group to the database (Open SSMS)

USE [master]
GO
ALTER DATABASE [AdventureWorks] ADD FILEGROUP [FG_INDEX]
GO

3) Add File to the newly added File group

USE [master]
GO
ALTER DATABASE [AdventureWorks] ADD FILEGROUP [FG_INDEX]
GO
ALTER DATABASE [AdventureWorks] 
ADD FILE ( NAME = N"AdventureWorks2008_Index", 
FILENAME = N"H:\MSSQL\DATA\AdventureWorks2008_Index.ndf" , 
SIZE = 5120MB , 
FILEGROWTH = 0) 
TO FILEGROUP [FG_INDEX]
GO

3. Generate Create Index script using script. Run this script on AdventureWorks database.

4. Save the results in an excel sheet for future comparison and copy the Index_Create_Statement column data in a notepad and replace the Filegroup name from Primary to FG_INDEX

Now it must look something like below:

Before:

 Create Unique Clustered Index [PK_ProductInventory_ProductID_LocationID] On [Production].[ProductInventory] ([ProductID] Asc,[LocationID] Asc) With (Drop_Existing = ON, SORT_IN_TEMPDB = ON, Fillfactor = 90, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = NONE) On [PRIMARY];

After:

 Create Unique Clustered Index [PK_ProductInventory_ProductID_LocationID] On [Production].[ProductInventory] ([ProductID] Asc,[LocationID] Asc) With (Drop_Existing = ON, SORT_IN_TEMPDB = ON, Fillfactor = 90, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = NONE) On [FG_NDEX];

You may also change other Index creation parameters as well if it’s required.

5.Now execute the altered scripts from notepad on the database and verify the successful execution.
Note: Please ignore the errors related to XML indices and run the next SQL statements.

6.Verify the Indices File group by running the step 3 script once again & compare.

You are all set. All the Indices in the database are moved to new File group.

How to move Indexes (Cluster and Non Cluster) from Primary File group to New File group?

SQL DBA Cheatcode Part 3 : Few Important Queries for any DBA

In continuation of our DBA Cheatcode series :-

 

I started this series on Queries\T-SQL commands that we use in our day to day operations or those queries which should be handy for any SQL DBA(Junior or senior) out there. In this series, I’ll try to share as many queries as I have or I use. There are 1000s features in SQL Server and each feature has its own set of infinite queries so any blog cannot cover all queries and all features but I will try to cover the queries that is needed in our day to day activities as a DBA. Here is the third installment of this series.(Database Backup\Restore Special)

Comments are most welcome by readers which can also enhance my database of queries.

**DISCLAIMER : I have personally used all these queries but these may or may not work in your environment depending on your server setting, server edition\version etc. Please test these queries in lower environment first before executing these in production directly**

Note :- I would like to thank countless sites and articles present there on google which helped me save all these queries to my personal QUERY DATABASE. This series is for knowledge sharing pupose only and you can share these queries again to your colleagues and friends.

 

QUERY 1 :- SCRIPT TO SHOW ALL RUNNING BACKUPS\RESTORES AND THEIR ESTIMATED COMPLETION TIME :-

This one script is very useful in our day to day operations as DBA in which we need to provide ETAs to customer for database refresh or backup related tasks. Below query provides detailed information about it :-

SELECT percent_complete,
            CAST((estimated_completion_time/3600000) as varchar) + " hour(s), "
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + "min, "
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + " sec" as est_time_to_go,command,
            s.text,
            start_time,           
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + " hour(s), "
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + "min, "
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + " sec" as running_time,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ("RESTORE DATABASE", "BACKUP DATABASE", "RESTORE LOG", "BACKUP LOG")

 

 

QUERY 2 :- RESTORE HISTORY FOR A PARTICULAR INSTANCE :-

This script provides complete restore history of all databases present in the instance with restore time, backup used, Backup time and other details :-

SELECT [rs].[destination_database_name],
 [rs].[restore_date],
 [bs].[backup_start_date],
 [bs].[backup_finish_date],
 [bs].[database_name] as [source_database_name],
 [bmf].[physical_device_name] as [backup_file_used_for_restore]
 FROM msdb..restorehistory rs
 INNER JOIN msdb..backupset bs
 ON [rs].[backup_set_id] = [bs].[backup_set_id]
 INNER JOIN msdb..backupmediafamily bmf
 ON [bs].[media_set_id] = [bmf].[media_set_id]
 ORDER BY [rs].[restore_date] DESC

QUERY 3 :- SCRIPT TO SCRIPTOUT ALL ROLES AND PERMISSIONS OF A DATABASE :-

 

Its a very important and useful script for any database restore related task. In most of the scenarios, you need to perform database refresh operation between different environments which have different user permissions all to gather. So in order to get the user permissions as they were prior to restore, you need to generate permission script before database refresh and need to apply it after the database refresh. Below script does wonder here and script out all roles, users and their permissions :-

 

print @@servername
print db_name()
--Script to Reverse Engineer SQL Server Object Role Permissions
--Written By Bradley Morris
--In Query Analyzer be sure to go to
--Query -> Current Connection Options -> Advanced (Tab)
--and set Maximum characters per column
--to a high number, such as 10000, so
--that all the code will be displayed.
begin
declare @dRoleName [sysname]
DECLARE _outer
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
select "RoleName" = name 
from sysusers where (issqlrole = 1 or isapprole = 1)
AND [name] NOT IN
(
"public",
"INFORMATION_SCHEMA",
"db_owner",
"db_accessadmin",
"db_securityadmin",
"db_ddladmin",
"db_backupoperator",
"db_datareader",
"db_datawriter",
"db_denydatareader",
"db_denydatawriter"
)
OPEN _outer
FETCH
NEXT
FROM _outer
INTO
@dRoleName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DatabaseRoleName [sysname]
--SET @DatabaseRoleName = "Database Role Name"
SET @DatabaseRoleName = @dRoleName
SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseRoleID [smallint],
@IsApplicationRole [bit],
@ObjectID [int],
@ObjectName [sysname]
SELECT
@DatabaseRoleID = [uid],
@IsApplicationRole = CAST([isapprole] AS bit)
FROM [dbo].[sysusers]
WHERE
[name] = @DatabaseRoleName
AND
(
[issqlrole] = 1
OR [isapprole] = 1
)
AND [name] NOT IN
(
"public",
"INFORMATION_SCHEMA",
"db_owner",
"db_accessadmin",
"db_securityadmin",
"db_ddladmin",
"db_backupoperator",
"db_datareader",
"db_datawriter",
"db_denydatareader",
"db_denydatawriter"
)
IF @DatabaseRoleID IS NULL
BEGIN
IF @DatabaseRoleName IN 
(
"public",
"INFORMATION_SCHEMA",
"db_owner",
"db_accessadmin",
"db_securityadmin",
"db_ddladmin",
"db_backupoperator",
"db_datareader",
"db_datawriter",
"db_denydatareader",
"db_denydatawriter"
)
SET @errStatement = "Role " + @DatabaseRoleName + " is a fixed database role and cannot be scripted."
ELSE
SET @errStatement = "Role " + @DatabaseRoleName + " does not exist in " + DB_NAME() + "." + CHAR(13) +
"Please provide the name of a current role in " + DB_NAME() + " you wish to script."
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = "--Security creation script for role " + @DatabaseRoleName + CHAR(13) +
"--Created At: " + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ":", "") + CHAR(13) +
"--Created By: " + SUSER_NAME() + CHAR(13) +
"--Add Role To Database" + CHAR(13)
IF @IsApplicationRole = 1
SET @msgStatement = @msgStatement + "EXEC sp_addapprole" + CHAR(13) +
CHAR(9) + "@rolename = """ + @DatabaseRoleName + """" + CHAR(13) +
CHAR(9) + "@password = ""Please provide the password here""" + CHAR(13)
ELSE
BEGIN
set @msgStatement = ""
SET @msgStatement = @msgStatement + "EXEC sp_addrole " + "@rolename =""" + @DatabaseRoleName + """
go" 
END
SET @msgStatement = @msgStatement  
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
"[" + USER_NAME([sysobjects].[uid]) + "].[" + [sysobjects].[name] + "]"
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseRoleID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ""
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + "SELECT,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + "INSERT,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + "UPDATE,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + "DELETE,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + "EXECUTE,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + "REFERENCES,"
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ","
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = "GRANT " + @msgStatement + " ON " + @ObjectName + " TO " + @DatabaseRoleName + "
go"
PRINT @msgStatement
END
SET @msgStatement = ""
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + "SELECT,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + "INSERT,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + "UPDATE,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + "DELETE,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + "EXECUTE,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + "REFERENCES,"
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ","
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = "DENY" + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + "ON " + @ObjectName + CHAR(13) +
CHAR(9) + "TO " + @DatabaseRoleName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
--PRINT "GO"
END
FETCH
NEXT
FROM _outer
INTO
@dRoleName
end
end
--------------For users
begin
DECLARE @DatabaseUserName [sysname]
declare @UName sysname
SET NOCOUNT ON
DECLARE
--@errStatement [varchar](8000),
--@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000)
--@ObjectID [int],
--@ObjectName [varchar](261)
DECLARE _dbusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[sysusers].name
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
OPEN _dbusers
FETCH NEXT FROM _dbusers INTO @UName
WHILE @@FETCH_STATUS = 0
begin
--cursor ends for all users
set @DatabaseUserName=@UName
SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = "User " + @DatabaseUserName + " does not exist in " + DB_NAME() + CHAR(13) +
"Please provide the name of a current user in " + DB_NAME() + " you wish to script."
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement =""
SET @msgStatement = 
--"--Add User To Database" + CHAR(13) +
"EXEC [sp_grantdbaccess]" + " @loginame =""" + @ServerUserName + """," + " @name_in_db =""" + @DatabaseUserName + """
GO" 
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = "EXEC [sp_addrolemember] " + "@rolename = """ + @RoleName + """," + " @membername = """ + @DatabaseUserName + """
go"
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
SET @msgStatement = "" + CHAR(13) 
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
"[" + USER_NAME([sysobjects].[uid]) + "].[" + [sysobjects].[name] + "]"
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ""
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + "SELECT,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + "INSERT,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + "UPDATE,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + "DELETE,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + "EXECUTE,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + "REFERENCES,"
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ","
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = "GRANT" + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + "ON " + @ObjectName + CHAR(13) +
CHAR(9) + "TO " + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ""
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + "SELECT,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + "INSERT,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + "UPDATE,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + "DELETE,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + "EXECUTE,"
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + "REFERENCES,"
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ","
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = "DENY" + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + "ON " + @ObjectName + CHAR(13) +
CHAR(9) + "TO " + @DatabaseUserName
PRINT @msgStatement
END
FETCH NEXT FROM _sysobjects INTO @ObjectID,@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
end
close _sysusers
deallocate _sysusers
FETCH NEXT FROM _dbusers INTO @UName
END
close _dbusers
deallocate _dbusers
--end
--END
end
--sp_helptext sp_helprolemember
---------------------------------Generating script to add role members --------------------------------
set nocount on
if object_id("tempdb..#t") is not null
drop table #t
select DbRole = g.name, MemberName = u.name, MemberSID = u.sid  into #t
from sysusers u, sysusers g, sysmembers m  
where g.uid = m.groupuid  
and g.issqlrole = 1  
and u.uid = m.memberuid  
and 1=2
insert into #t exec sp_helprolemember
--select * from #t
--sp_addrolemember
declare @dbrole varchar(800)
declare @membername varchar(800)
DECLARE _addrole CURSOR FOR 
SELECT DbRole, MemberName
FROM #t
OPEN _addrole 
FETCH NEXT FROM _addrole 
INTO @dbrole, @membername
WHILE @@FETCH_STATUS = 0
BEGIN
--print @dbrole + @membername
print "sp_addrolemember @rolename =""" +  @dbrole + """,  @membername = """ + @membername + """
go"
FETCH NEXT FROM _addrole 
INTO @dbrole, @membername
end
close _addrole
deallocate _addrole
print db_name()

 

QUERY 4 :- Restore SQL Database from MDF file ( without LDF file)

In the below script I have created the database, create a table in that, dropped its log file and created the database with the .mdf file.

 

-- created database with .mdf and .ldf file
CREATE DATABASE [TEST]
ON  PRIMARY
( NAME = N"TEST", FILENAME = N"C:\TEST_Data.mdf")
 LOG ON
( NAME = N"TEST_log", FILENAME = N"C:\TEST_log.ldf")
GO
-- inserting data into database
use TEST
go
CREATE TABLE customer
(    customer_id int not null,
     customer_name    varchar(50)    not null,
     address    varchar(50),   
     city    varchar(50),   
     state    varchar(25),   
     zip_code    varchar(10),   
)   
-- inserting records
insert into customer values(1,"John","9290-9300 Transit Road","Amherst","NY","14051")
insert into customer values(2,"Sam","4030 Maple Ave.","Amherst","NY","14051")
insert into customer values(3,"Jason","4888 State Route 30","Amherst","NY","14051")
insert into customer values(4,"Joe","1651 Clark Street","Amherst","NY","14051")
go
-- Selecting Data and verifying Data is inserted
select * from TEST..customer
-- deleting the log file
-- detaching the database file
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N"TEST"
GO
-- now next step is delete the file manually or you can do it from command prompt
EXEC xp_cmdshell "del C:\Test_log.ldf"
EXEC xp_cmdshell "dir C:\Test_log.ldf"
-- script to attach the database
USE [master]
GO
CREATE DATABASE TEST ON
( FILENAME = N"C:\TEST_Data.mdf" )
FOR ATTACH
GO

 

SCRIPT 5 :- RANDOM USEFUL SCRIPTS FOR DATABASE BACKUP & RESTORE

 

Some Randome scripts for Backup\restore :-

–- Script to Get the backup file properties

 RESTORE FILELISTONLY FROM DISK = ‘D:\TEST.bak’


–- Script to delete the backup history of the specific databsae

 EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’TEST1′
 GO

--Script to get basic details of a backup file

RESTORE HEADERONLY FROM DISK = "E:\Restore\backroom.bak"

--Script to check consistency of a backup file

RESTORE VERIFYONLY FROM DISK ="E:\Restore\backroom.bak"

--Script to check backup media information for the backup device.

RESTORE LABELONLY FROM DISK = "E:\Restore\Sample.bak" 

-- Script to fix all Orphan users after restore :-

DECLARE @username varchar(25)
  DECLARE fixusers CURSOR
  FOR
  SELECT UserName = name FROM sysusers
    WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
      and suser_sname(sid) is null
        ORDER BY name
  OPEN fixusers
  FETCH NEXT FROM fixusers
  INTO @username
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC sp_change_users_login "update_one", @username, @username
    FETCH NEXT FROM fixusers
    INTO @username
  END CLOSE fixusers
  DEALLOCATE fixusers

-- Script to Drop all users of source backup after restore

declare @user_name varchar(100), @exec_sql varchar(2000)
declare user_cursor cursor for
select name from sysusers 
where issqlrole = 0
and hasdbaccess = 1
and name <> "dbo"

open user_cursor
FETCH NEXT FROM user_cursor  into @user_name 
   WHILE @@FETCH_STATUS  = 0
   BEGIN
           set @exec_sql = "exec sp_revokedbaccess " +  "["+  @user_name   +"]"
             
      --print @exec_sql
       execute (@exec_sql)
 FETCH NEXT FROM user_cursor  into @user_name    
    END

   close user_cursor
   deallocate user_cursor

Happy Learning.

SQL DBA Cheatcode Part 3 : Few Important Queries for any DBA

SQL DBA Cheatcode Part 2 : Few Important Queries for any DBA

Hi Friends,

From last few days, I was thinking about writing a series on Queries\T-SQL commands that we use in our day to day operations or those queries which should be handy for any SQL DBA(Junior or senior) out there. In this series, I’ll try to share as many queries as I have or I use. There are 1000s features in SQL Server and each feature has its own set of infinite queries so any blog cannot cover all queries and all features but I will try to cover the queries that is needed in our day to day activities as a DBA. Here is the second installment of this series. (Replication Special)

Comments are most welcome by readers which can also enhance my database of queries. So here goes first part of this series.

**DISCLAIMER : I have personally used all these queries but these may or may not work in your environment depending on your server setting, server edition\version etc. Please test these queries in lower environment first before executing these in production directly**

Note :- I would like to thank countless sites and articles present there on google which helped me save all these queries to my personal QUERY DATABASE. This series is for knowledge sharing pupose only and you can share these queries again to your colleagues and friends.

 

Query 1 :- Important Scripts to find out Replication details.

Script to run on Distribution database

This script returns completed setup replication information. Unless an orphan article exists, this will return a complete set of replication information. I also added the distribution agent job name to show how easy it is to pull in other configuration information.

 

USE Distribution

GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

— Get the publication name based on article

SELECT DISTINCT

srv.srvname publication_server

, a.publisher_db

, p.publication publication_name

, a.article

, a.destination_object

, ss.srvname subscription_server

, s.subscriber_db

, da.name AS distribution_agent_job_name

FROM MSArticles a

JOIN MSpublications p ON a.publication_id = p.publication_id

JOIN MSsubscriptions s ON p.publication_id = s.publication_id

JOIN master..sysservers ss ON s.subscriber_id = ss.srvid

JOIN master..sysservers srv ON srv.srvid = p.publisher_id

JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id

AND da.subscriber_id = s.subscriber_id

ORDER BY 1,2,3

 

Script to run on Publisher database

This script returns what publications has been setup. This will go through all the published databases and return information if the database has replication enabled. Sometimes, I just want to see the publication name and subscriber server names (no articles) to see what servers are being used with replication other times I want all of the information, so I added a variable called @Detail and if you set @Detail = ‘Y’ it will return data with the article list. Any other value will only return the publisherDB, publisherName and SubscriberServerName.

— Run from Publisher Database

— Get information for all databases

DECLARE @Detail CHAR(1)

SET @Detail = ‘Y’

CREATE TABLE #tmp_replcationInfo (

PublisherDB VARCHAR(128),

PublisherName VARCHAR(128),

TableName VARCHAR(128),

SubscriberServerName VARCHAR(128),

)

EXEC sp_msforeachdb

‘use ?;

IF DATABASEPROPERTYEX ( db_name() , ”IsPublished” ) = 1

insert into #tmp_replcationInfo

select

db_name() PublisherDB

, sp.name as PublisherName

, sa.name as TableName

, UPPER(srv.srvname) as SubscriberServerName

from dbo.syspublications sp

join dbo.sysarticles sa on sp.pubid = sa.pubid

join dbo.syssubscriptions s on sa.artid = s.artid

join master.dbo.sysservers srv on s.srvid = srv.srvid

‘

IF @Detail = ‘Y’

SELECT * FROM #tmp_replcationInfo

ELSE

SELECT DISTINCT

PublisherDB

,PublisherName

,SubscriberServerName

FROM #tmp_replcationInfo

DROP TABLE #tmp_replcationInfo

Script to run on Subscriber database

This script returns what article(s) is/are being replicated to the subscriber database. I also use this to find orphaned subscribers. This is rather simple since there is not much information to pull.

— Run from Subscriber Database

SELECT distinct publisher, publisher_db, publication

FROM dbo.MSreplication_subscriptions

ORDER BY 1,2,3

The following scripts will provide the name of the database on which merge replication is setup

select namefromsys.databaseswhereis_merge_published= 1

 

Query 2 :- Drop article from Existing Publication (Transactional Replication)

EXEC sp_dropsubscription

@publication = ‘Publication name’,

@article = N’Table Name’,

@subscriber = ‘Subscriber Server Name";

GO

EXEC sp_droparticle

@publication = ‘Publication name’,

@article = N’Table Name’,

@force_invalidate_snapshot = 0

 

QUERY 3 :- Adding new article to Existing Publication (Transactional Replication)

First of all run the Exec sp_helppublication in publication database and checked the following fields

 

1. Immediate_sync
   
   2. Allow_anonymous

Both the fields were set to ON as they showed a value 1 which is enabled. If the Immediate_sync is enabled, every time you add a new article it will cause the entire snapshot to be applied and not the one for the particular article alone. Usually, the Immediate_sync publication property is set to true if we allowed anonymous subscriptions while creating the publication through the Create Publication wizard. To prevent the complete snapshot, run the script below.

Step 1:- Disable the two fields in publication database

 

EXEC sp_changepublication

@publication = ‘Publication name’,

@property = N’allow_anonymous’,

@value = ‘false’

GO

EXEC sp_changepublication

@publication = Publication name’,

@property = N’immediate_sync’,

@value = ‘false’

GO

 

Step 2:- Add new article in publication database

exec sp_addarticle

@publication = N’Publication name’,

@article = N’Table Name’,

@source_object = N’Table Name’,

@destination_table = N’Table Name’

GO

exec sp_addsubscription

@publication = N’Publication name’,

@subscriber = N’Subscriber Server Name’,

@destination_db = N’Destination Database Name’,

@subscription_type = N’Subcription Type’ [Pull\Push]

Step 3:- Now start the snapshot agent in publisher, it worked perfectly. You can see that only the particular table added was replicated. So from now on to apply the snapshots of the entire articles you need to reinitialize the subscriptions since the Immediate_sync is set to off.

 

QUERY 4 :-List all tables that are currently published for replication MS-SQL

It’s possible to query the distribution database to see what articles (tables) are published and within which Publication they are in.

SELECT

P.[publication]   AS [Publication Name]

,A.[publisher_db]  AS [Database Name]

,A.[article]       AS [Article Name]

,A.[source_owner]  AS [Schema]

,A.[source_object] AS [Table]

FROM

[distribution].[dbo].[MSarticles] AS A

INNER JOIN [distribution].[dbo].[MSpublications] AS P

ON (A.[publication_id] = P.[publication_id])

ORDER BY

P.[publication], A.[article];

 

Query 5 :-

 

This is how transfer primary keys in replication:

To replicate primary/foreign keys, we need to enable an option for the

article.

1. In the SQL Enterprise Manager, drill down to Databases -> your

publishing database -> Publications.

2. Right click the transactional publication under the Publications node,

click Properties.

3. Click the Articles tab, click the ¡¬ button near an article that you

want to replicate the primary/foreign key.

4. Click the snapshot tab, check the “Include declared referential

integrity” option.

5. Click the snapshot tab, check the “delete all data in the existing table” option.

This requires us to have created the tables manule at first or provid the snapshot agent with an script to create those before applying the snapshot. I prefeer to create the table manualy before i set up the transaction.

 

I shall be back with more queries soon. Please comment your views. Thanks for reading!!

SQL DBA Cheatcode Part 2 : Few Important Queries for any DBA

What are naming standards/conventions for SQL Server? (1/5)

I think it will be good idea to setup some naming standards throughout your database servers’ farm. Most of us already have these standards in place. And if there is nothing, I understand that it’s not going to be an easy task to implement standards, especially within an existing environment. BUT, it’s never too late. Let’s have something now.

 

Server name – <Three or Four Characters for Datacenter location> + <Two, Three or may be Four characters for Server type> + <three or four digit number>

Location example: USA, IND, DEL, LON, ZUR, NYK etc

Server Type example: SQLS, SSIS, SSRS, SSAS, ETL

So, server name should look something like – DELSSRS001 (or DEL-SSRS-001), by looking at the name only, anyone can now understand that this server is for SSRS & located at Delhi (DEL) datacenter. Additionally, you can define standards for named SQL instances.

Database Name – Naming database is a crucial task, it’s like finding a good name for a new born baby. This can be good idea to associate database name with the applications as family name. Like, <AppName> + <Underscore> + <Three Characters for Environment> + <version number>

Example: MyApp_Prd1, MyApp_Dev1, MyApp_UAT2

Once database name is set, it comes to Database Files & File groups. Database files should follow the same logic as database name. So, it will become something like

<databasename> + <Underscore> + <dat> + <count number>.mdf

<databasename> + <Underscore> + <log> + <count number>.ldf

<databasename> + <Underscore> + <dat> + <count number>.ndf

Example: MyApp_Prd1_dat1.mdf, MyApp_Prd1_dat2.ndf, MyApp_Prd1_log1.ldf

File Groups : <fg> + <Underscore> + <purpose1> + <purpose2> + <count number>

Example: fg_data1, fg_myindex_1

Stay tuned for upcoming posts on this series.

Related articles across the web

• SQL Unplugged Episode 1 | SQL Unplugged


• Using Docker to Build Linked Container Course VMs


• SQL Query for User Accounts with No Paypal


• 5 Things You Didn’t Know About SQL Agent

What are naming standards/conventions for SQL Server? (1/5)

Common Human Errors as DBA (Part 3)

 

In continuation of the series of “Common Human Errors as DBA” – here I am posting Third Part of it. You may check others here. Part 1 & Part 2

Poor naming standards & Lack of documentation: This is a common mistake (not human error) that frequently happens. From the very beginning, a DBA must setup naming standards and must communicate ‘standards’ to all stakeholders. Setting up an environment which has all naming standards in place is very easy to manage and helps others to understand quickly. Missing control over naming standard can be considered as Human Error though. So, to prevent,

• You may create a scheduled report to show the list of objects which are not meeting the standards.


• You must communicate your standards; especially to Developers


• You must not allow exceptions when it comes to standards

Added indexes to every column in a table for best performance: This can be consider as human error that without evaluating existing indexes on other columns, during a course of time, a DBA created indexes on all (or most of the) columns. And top of it, DBA forgot that indexes need maintenance too. DBA needs to be careful about index selection and their maintenance. I could not figure out any prevention method for this. I can say, “Be careful as always”. However, if you have some suggestions, please feel free to add comments below in the post.

Gave database user DBO or SA rights: Sometimes, in non-production environment, while troubleshooting issues, DBA may need to elevate access for a database user. Once DBA finds the root cause, DBA must revoke elevated access as quickly as possible. I have seen some cases where DBA felt excited after fixing those issues and forgot to revoke access. This eventually caused issues at the end of the day. To prevent this,

• A weekly Audit report can be created and delivered to DBAs for review.


• An alerting system can be created if there is any change with permissions.


• Changes should be tracked in Incident or Change documentation.

 

Hopefully this series helped you. Stay tuned.

Common Human Errors as DBA (Part 3)

SQL DBA Cheatcode Part 1 : Few Important Queries for any DBA

Hi Friends,

From last few days, I was thinking about writing a series on Queries\T-SQL commands that we use in our day to day operations or those queries which should be handy for any SQL DBA(Junior or senior) out there. In this series, I’ll try to share as many queries as I have or I use. There are 1000s features in SQL Server and each feature has its own set of infinite queries so any blog cannot cover all queries and all features but I will try to cover the queries that is needed in our day to day activities as a DBA.

Comments are most welcome by readers which can also enhance my database of queries. So here goes first part of this series.

**DISCLAIMER : I have personally used all these queries but these may or may not work in your environment depending on your server setting, server edition\version etc. Please test these queries in lower environment first before executing these in production directly**

Note :- I would like to thank countless sites and articles present there on google which helped me save all these queries to my personal QUERY DATABASE. This series is for knowledge sharing pupose only and you can share these queries again to your colleagues and friends.

QUERY 1 :-
Free & Used space of all files in a Database : Many times we need to check which all datafiles are using how much space. Instead of using GUI, you can use below query directly to get all the details :-

select

name

, filename

, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB

, convert(decimal(12,2),round(fileproperty(a.name,’SpaceUsed’)/128.000,2)) as SpaceUsedMB

, convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) as FreeSpaceMB

from dbo.sysfiles a

QUERY 2 :-
All Database files in a Instance with location : This query lists all the database files present in a Instance. Output of this file contains Logical filename and its physical path in the server.

SELECT name, physical_name AS current_file_location

FROM sys.master_files

QUERY 3 :-

Query to find all the processes running on a particular DB : In many cases, you may not want to see for running processes on all the databases from sp_who2 and want to look directly for a particular database :-

SELECT [Database]=DB_NAME(dbid), spid, last_batch, status, hostname, loginame

FROM sys.sysprocesses

WHERE dbid = DB_ID(‘TESTDB’); —-change the databse name here

QUERY 4 :-

All Databases last access time from last reboot :- You may need to find which database is not being used by users in long time. This query may come handy but it has limitations that it contains data from last service restart only as data from DMV dm_db_index_usage_stats gets flushed after every service recycle :-

SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate

FROM

(SELECT DB_NAME(database_id) DatabaseName ,

last_user_seek ,

last_user_scan ,

last_user_lookup ,

last_user_update FROM sys.dm_db_index_usage_stats) AS PivotTable UNPIVOT

(LastAccessDate FOR last_user_access IN

(last_user_seek ,

last_user_scan ,

last_user_lookup ,

last_user_update) )

AS UnpivotTable GROUP BY DatabaseName

HAVING DatabaseName NOT IN (‘master’, ‘tempdb’, ‘model’, ‘msdb’)

ORDER BY 2

QUERY 5 :-

Script to find fragmentation level for every table of a database :- The below script provides fragmentation percentage for every index of every table in a database :-

select TableName=object_name(dm.object_id)

,IndexName=i.name

,IndexType=dm.index_type_desc

,[%Fragmented]=avg_fragmentation_in_percent

from sys.dm_db_index_physical_stats(db_id(),null,null,null,’sampled’) dm

join sys.indexes i on dm.object_id=i.object_id and dm.index_id=i.index_id

order by avg_fragmentation_in_percent desc

I shall be back with more queries soon. Please comment your views. Thanks for reading!!

SQL DBA Cheatcode Part 1 : Few Important Queries for any DBA

How to get access on SQL Server if you don"t have "sa" password or sysadmin access?

PROBLEM

DBAs need to have sysadmin access on SQL Server to support Databases effectively. There may come situations where DBAs neither have sysadmin access nor [sa] password. In that case, DBA needs to perform below activity to get the access.

RESOLUTION

PREREQUISITES:

1) Administrative privileges needed on Host Server.

2) Downtime of approximately 10 minutes needed because SQL Server would be started in ‘Single User’ mode.

PROCEDURE:

1. RDP to the server with the account which has ‘Admin’ rights on the Hosting Windows box. Using a service account is good idea.

2. Open ‘SQL Server Configuration Manager’ :

Start>All Programs>Microsoft SQL Server 2008 (or 2005)>Configuration Tools>SQL Server Configuration

Manager

3. Start SQL Services in ‘Single User Mode:

• Right Click on SQL Services and click ‘properties’


• Go ‘Advanced’ tab then open ‘Start up Parameters’


• Write ‘-m’ at the end, then click ‘OK’.

This would open a dialog box informing that SQL Services need to be restarted for this. Click ‘OK’.

• Stop dependent services like SQL Agent. Restart SQL Services.

4. Connect to SQL instance using SQLCMD

• Right Click on Command prompt, select ‘Run as Administrator’ (in Windows 2003 and lower, run as administrator is not available and not required)


• Now connect to SQL instance using SQLCMD, write below command:


• SQLCMD –E –S <instance name>

5. Now create a windows login (i.e. group account used by DB team), use below command:

CREATE LOGIN [Login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

GO

6. Now grant ‘sysadmin’ access to this newly created login, use below command:

EXEC master..sp_addsrvrolemember @loginame = "login_name", @rolename = "sysadmin"

GO

7. Restart SQL Services in multi user mode. For doing that, remove ‘-m’ from Startup parameters and restart SQL

Services.

Now DBA gets sysadmin access at SQL Server with the above created Login Name.

Related articles across the web

• How to Link an SQL Server To Another Sql ServerGuys I need your help on this,


• Doing it Wrong: Database Mirroring


• SQL SERVER – Customization of New Query in SQL Server Management Studio


• I wish I wouldn’t have clicked that button…

How to get access on SQL Server if you don"t have "sa" password or sysadmin access?