Tag Archives: databases

How to move and or rename Database files in SQL Server

An example use case for the process below could be you need to move database files to a new drive. Another example might be your organisation intends to run a legacy database along side a new updated database with both sharing the same database name in the same instance with the files located in the same directory with the same names. Obviously this cannot be done and requires the database names to differ and the files to be renamed or not exist in the same directory.

For example AdventureWorks might become AdventureWorks_Legacy while a new and improved AdventureWorks database retains the original database name. The associated database file names would also need to be changed/moved to reflect this.

Someone might also want to do something like this for test purposes but obviously having test resources in a live environment would not be recommended if avoidable.

The first step to moving and renaming the files is to copy and modify the script below. Note the script below assumes you want to move and change the names of the files. To avoid any database conflicts you only need to do one or the other.

/* 
Find & Replace DbName with the name of the Database you are working with
*/
USE [DbName];

/*
Changing Physical names and paths
Replace 'C:\...\NewDbName.mdf' with full path of new Db file to be used
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = ' DbName '
	,FILENAME = 'C:\...\NewDbName.mdf'
	);

/*
Replace 'C:\...\NewDbName_log.ldf' with full path of new Db log file to be used
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = ' DbName _log'
	,FILENAME = 'C:\...\NewDbName_log.ldf'
	);

/*
Changing logical names
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = DbName
	,NEWNAME = NewDbName
	);

ALTER DATABASE DbName MODIFY FILE (
	NAME = DbName_log
	,NEWNAME = NewDbName_log
	);
Once the script has been set up as desired follow the steps below:
  1. Open Microsoft SQL Server Management Studio (SSMS).
  2. Connect to the server that houses the Db you are working with.
  3. Run the modified script
  4. Right click on the Db in SSMS and select Tasks > Take Offline
  5. If you are moving the database files log into the server that houses the database files and copy and move the MDF and LDF files to the location you specified in first two alter commands. If the script specifies new names rename the copied files to match the names given in the script exactly.
  6. Go back to SSMS and right click on the Db and select Tasks > Bring Online.
  7. If you have moved the files once the database is back online and confirmed working as expected the unused original files can be deleted.
  8. Now you can rename the Db to the new name if you wish using SSMS.

How to search for SQL Server objects that exist anywhere across an instance using T-SQL

You’re probably never going to be familiar with every database object, i.e. Table, View, Stored Procedure and Function, that exists in a large production database, even if you were the one who designed it. So everyone who maintains an environment be it a call center back end or sales system back end or CRM back end etc. etc. needs to be able to locate objects quickly.

Redgate offer a fantastic free tool to do this within SQL Server Management Studio through a GUI called SQL Search.

As great as this is though sometimes you might want to search through object definitions programmatically.

To clarify I’m defining object definition as being column names of a table or view or the command that makes up a stored procedure or function.

Below is the Store Procedure I’ve written to do this called SearchObjectDefinition. To work this Stored Procedure also requires the User Defined Function (UDF) called Split which I used in the tutorial “How to pass a multi-value parameter to a stored procedure from a SSRS Report“.

Below are a few use cases for SearchObjectDefinition:

--List All Instance Tables, Stored Procedures, Views and Functions
EXEC dbo.SearchObjectDefinition

--List All Stored Procedures, and Functions in the Databases 
--TestDatabaseOne and TestDatabaseTwo
EXEC dbo.SearchObjectDefinition @ObjectType = 'Sp, Fn'
	,@DatabaseName = 'TestDatabaseOne, TestDatabaseTwo'

--List All Instance Tables, Stored Procedures, Views and Functions 
--where Object Definition contains the word Insert
EXEC dbo.SearchObjectDefinition @strFind = 'insert'

--List All Instance Tables where Object Name is Customers and 
--Column name contains the word Phone
EXEC dbo.SearchObjectDefinition @ObjectType = 'tb'
	,@ObjectName = 'Customers'
	,@strFind = 'Phone'

As always be sure to deploy the following Function and Store Procedure in a utility database not the master database as this is bad practice.

Split Function:

--USE [DatabaseName];
--GO

IF OBJECT_ID('[Split]') IS NULL
	EXEC ('CREATE FUNCTION dbo.[Split](@i INT) RETURNS @RtnValue TABLE (j INT) AS BEGIN INSERT INTO @RtnValue (j) SELECT 1 RETURN END');
GO

ALTER FUNCTION [dbo].[Split] (
	@List NVARCHAR(2000)
	,@SplitOn NVARCHAR(5)
	)
RETURNS @RtnValue TABLE (
	Id INT identity(1, 1)
	,Value NVARCHAR(100)
	)
AS
BEGIN
	WHILE (Charindex(@SplitOn, @List) > 0)
	BEGIN
		INSERT INTO @RtnValue (value)
		SELECT Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List) - 1)))

		SET @List = Substring(@List, Charindex(@SplitOn, @List) + len(@SplitOn), len(@List))
	END

	INSERT INTO @RtnValue (Value)
	SELECT Value = ltrim(rtrim(@List))

	RETURN
END

SearchObjectDefinition Stored Procedure:

--USE [DatabaseName];
--GO

IF OBJECT_ID('[SearchObjectDefinition]') IS NULL
	EXEC ('CREATE PROCEDURE dbo.[SearchObjectDefinition] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[SearchObjectDefinition] (
	@ObjectType AS VARCHAR(20) = NULL
	,@ObjectName AS SYSNAME = NULL
	,@DatabaseName AS SYSNAME = NULL
	,@strFind AS VARCHAR(MAX) = NULL
	)
AS
BEGIN
	SET NOCOUNT ON;
	SET @strFind = ISNULL(@strFind, '')
	SET @ObjectName = ISNULL(@ObjectName, '')

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

	DECLARE @DatabaseTable TABLE (DbName SYSNAME)
	DECLARE @DbName AS SYSNAME
	DECLARE @Sql AS VARCHAR(MAX)

	CREATE TABLE #Result (
		DbName SYSNAME NULL
		,ObjectType VARCHAR(2)
		,ObjectName SYSNAME
		,ObjectDefinition VARCHAR(MAX)
		)

	IF @DatabaseName IS NOT NULL
	BEGIN
		INSERT INTO @DatabaseTable (DbName)
		SELECT Value
		FROM dbo.Split(@DatabaseName, ',')
	END

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @DatabaseTable (DbName)
		SELECT NAME
		FROM master.dbo.sysdatabases
		WHERE NAME NOT IN (
				'tempdb'
				,'master'
				,'msdb'
				,'model'
				)
		ORDER BY NAME ASC
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL PROCEDURES  
	IF @ObjectType LIKE '%Sp%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
			USE ' + QUOTENAME(@DbName) + ';
			
			INSERT INTO #Result (
				DbName
				,ObjectType
				,ObjectName
				,ObjectDefinition
				)
			SELECT ''' + @DbName + ''' AS DbName
				,''Sp'' AS ObjectType
				,OBJECT_NAME(OBJECT_ID) AS ObjectName
				,OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
			FROM ' + QUOTENAME(@DbName) + '.sys.procedures
			WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'' + ''' + @strFind + ''' + ''%''
			AND Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL VIEWS   
	IF @ObjectType LIKE '%Vw%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
		
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Vw'' AS ObjectType
			,OBJECT_NAME(OBJECT_ID) AS ObjectName
			,OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.sys.VIEWS
		WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'' + ''' + @strFind + ''' + ''%''
		AND Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL FUNCTION 
	IF @ObjectType LIKE '%Fn%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
		
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Fn'' AS ObjectType
			,ROUTINE_NAME AS ObjectName
			,ROUTINE_DEFINITION AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.INFORMATION_SCHEMA.ROUTINES
		WHERE ROUTINE_DEFINITION LIKE ''%'' + ''' + @strFind + ''' + ''%''
			AND ROUTINE_NAME LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			AND ROUTINE_TYPE = ''FUNCTION''
		ORDER BY ROUTINE_NAME
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL TABLES OF DATABASE.  
	IF @ObjectType LIKE '%Tb%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
			
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Tb'' AS ObjectType
			,t.NAME AS ObjectName
			,c.NAME AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.sys.tables AS t
		INNER JOIN ' + QUOTENAME(@DbName) + '.sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
		WHERE c.NAME LIKE ''%'' + ''' + @strFind + ''' + ''%''
		AND t.Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
		ORDER BY [ObjectDefinition] ASC
			'

			EXEC (@Sql)
		END
	END

	SELECT DbName
		,ObjectType
		,ObjectName
		,ObjectDefinition
	FROM #Result
	ORDER BY DbName ASC
		,ObjectType ASC
		,ObjectName ASC

	DROP TABLE #Result
END