Home > Administration, SQL Server, T-SQL > Generate ALTER Statements for Database Moves

Generate ALTER Statements for Database Moves

Last week, I posted Identifying Corrupt Rows USING DBCC CHECKDB and DBCC PAGE. I wrote the scripts in that post to identify corrupt rows in a client’s database. As we continued to investigate, things got strange. Repeated runs of DBCC CHECKDB were generating different results. Each one showed serious corruption, but on different pages with each run. In an effort to remove the storage system from the list of potential causes, I needed to move to data and log files for the database to a a different storage array. This post from TechNet gave me the steps I needed to run.

In this case, only one database had to be moved. To move more than one database, I wrote the following script to generate the statements specified in the TechNet article. In addition to the ALTER DATABASE statements, the script checks to see if the database is part of an availability group. If it is, a statement is generated to remove it. As adding a database to an availability group is far more complicated than removing it, I will leave that to the user.

As usual, I hope you find this useful. And remember, executing code you find on the internet against your production instance always has the potential to become a resume updating event. Use caution and test.

/*---------------------------------------------------------------
Created By - Frank Gill
Created On - 2014-10-13
 
Script to generate ALTER DATABASE statement to move a database to
a different storage location
---------------------------------------------------------------*/

USE master;

SET NOCOUNT ON;

/* Drop temp table if it exists */

IF OBJECT_ID('tempdb..#dbfiles') IS NOT NULL
BEGIN

	DROP TABLE #dbfiles;

END

/* Declare local variables and create temp table */
DECLARE @dbname SYSNAME;
DECLARE @agname SYSNAME;
DECLARE @sqlstr NVARCHAR(4000);
DECLARE @physicalname NVARCHAR(260);
DECLARE @logicalname SYSNAME;
DECLARE @filetype TINYINT;
DECLARE @datapath NVARCHAR(1000);
DECLARE @logpath NVARCHAR(1000);
DECLARE @agdb BIT = 0;
DECLARE @loopcount INT = 1;
DECLARE @looplimit INT;

CREATE TABLE #dbfiles
(logicalname SYSNAME
,physicalname NVARCHAR(260)
,filetype TINYINT)

/* Initialize variables */
SET @dbname = 'Your_Database';
SET @datapath = 'Your_Data_Path';
SET @logpath = 'Your_Log_Path';

/* If the database is part of an availability group, generate a statement to remove it prior to taking the database offline */
IF(SELECT replica_ID FROM sys.databases WHERE name = @dbname) IS NOT NULL
BEGIN

	PRINT '/* This database is part of an availability group and must be removed prior to taking the database offline
   You will need to add the database back to the availability group after the database files are moved */'

	SELECT @agname = g.name FROM sys.databases d
	INNER JOIN sys.availability_replicas r
	ON d.replica_id = r.replica_id
	INNER JOIN sys.availability_groups g
	ON g.group_id = r.group_id
	WHERE d.name = @dbname;

	SET @sqlstr = 'ALTER AVAILABILITY GROUP [' + @agname + ']
REMOVE DATABASE [' + @dbname + '];';

	PRINT @sqlstr;
	
	SET @agdb = 1;

END

/* Generate a statement to set the database offline */
SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
SET OFFLINE WITH ROLLBACK IMMEDIATE';

PRINT @sqlstr;

/* Get a list of database files for your database and insert them into the temp table for use in the loop */
SET @sqlstr = 'INSERT INTO #dbfiles
SELECT name, physical_name, type FROM ' + @dbname + '.sys.database_files;'

EXEC sp_executesql @sqlstr;

/* Set the loop limit to the number of files returned from the insert statement */
SET @looplimit = @@ROWCOUNT;

WHILE @loopcount <= @looplimit
BEGIN

	/* Get the first set of physical and logical file name and generate an ALTER DATABASE statement using the file type to determine the new physical location */

	SELECT TOP 1 @logicalname = logicalname, @physicalname =  REVERSE(SUBSTRING(REVERSE(physicalname),1,(CHARINDEX('\',REVERSE(physicalname))-1))), @filetype = filetype FROM #dbfiles;

	IF @filetype = 0
	BEGIN

 SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
MODIFY FILE 
(NAME = ' + @logicalname + ' 
,FILENAME = ''' + @datapath + @physicalname + '''' + ');';
 
 END
 ELSE IF @filetype = 1
 BEGIN

 SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
MODIFY FILE 
(NAME = ' + @logicalname + ' 
,FILENAME = ''' + @logpath + @physicalname + '''' + ');';
 END

	PRINT @sqlstr;

	SET @loopcount += 1;

	DELETE TOP (1) FROM #dbfiles 

END

/* Generate a statement to bring the database back online */
SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
SET ONLINE';

PRINT @sqlstr;


IF @agdb = 1
BEGIN

	PRINT '/* Reminder!  ' + @dbname + 'is part of an availability group 
You will need to add the database back to the availability group if necessary */'

END

  1. October 14, 2014 at 11:24

    I altered the script to add the closing quote and parenthesis at the end of the MODIFY FILE statements. It works better now.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: