--The following SQL script rename
database from [TestDev] to [TestProd].
DECLARE @CurrentDatabaseName NVARCHAR(100) = 'TestDev' --<<-- current database name --
DECLARE @NewDatabaseName NVARCHAR(100) = 'TestProd' --<<-- new database name --
DECLARE @DatabaseId INT = 0
DECLARE @PhysicalDatabaseMDFName NVARCHAR(500), @PhysicalDatabaseLDFName NVARCHAR(500)
DECLARE @PhysicalDatabaseNewMDFName NVARCHAR(500), @PhysicalDatabaseNewLDFName NVARCHAR(500)
DECLARE @LogicalDatabaseMDFName NVARCHAR(500),
@LogicalDatabaseLDFName NVARCHAR(500)
DECLARE @CharIdx INT = 0, @Idx INT = 0, @Length INT = 0
DECLARE @MDF NVARCHAR(100) = '' , @NewMDF NVARCHAR(100) = ''
DECLARE @LDF NVARCHAR(100) = '' , @NewLDF NVARCHAR(100) = ''
DECLARE @MDFName NVARCHAR(100) = '' , @LDFName NVARCHAR(100) = ''
DECLARE @SQL NVARCHAR(2000) = ''
DECLARE @Return INT = 0
DECLARE @ProductVersion NVARCHAR(128)
DECLARE @ProductMajorVersion INT
= 0
SELECT @ProductVersion = CONVERT(NVARCHAR(128),SERVERPROPERTY('ProductVersion'))
SET @ProductMajorVersion = CONVERT(INT, ISNULL(LEFT(@ProductVersion,2),'0'))
SET @CurrentDatabaseName = RTRIM(LTRIM(@CurrentDatabaseName))
SET @NewDatabaseName = RTRIM(LTRIM(@NewDatabaseName))
-- getting the Database Id
SELECT @DatabaseId =
database_id FROM sys.databases WITH (NOLOCK) WHERE name =
@CurrentDatabaseName
IF (@DatabaseId IS NULL OR @DatabaseId = 0)
BEGIN
PRINT 'Error! Current
Database not found';
RETURN
END
BEGIN TRY
IF (@DatabaseId >0)
BEGIN
-- To enable xp_cmdshell:
SET @SQL = 'SP_CONFIGURE ''' + 'show advanced options' +
''' , 1
RECONFIGURE WITH OVERRIDE;'
PRINT @SQL
EXEC(@SQL)
SET @SQL = 'SP_CONFIGURE ''' + 'xp_cmdshell' + ''' , 1 RECONFIGURE
WITH OVERRIDE;'
PRINT @SQL
EXEC(@SQL)
-- To kill all the opened sessions
SELECT @SQL = @SQL + 'KILL ' + CONVERT(varchar(5), es.session_id) + ';'
FROM sys.dm_exec_sessions es
INNER JOIN sys.sysprocesses sp on
es.session_id =
sp.spid
WHERE sp.dbid = @DatabaseId
PRINT @SQL
EXEC(@SQL);
-- getting the physical mdf name
SELECT Top 1
@PhysicalDatabaseMDFName = physical_name, -- physical data file
@MDFName = name -- logical name for
data file
FROM sys.master_files WITH (NOLOCK)
WHERE database_id =
@DatabaseId AND file_id
= 1 AND
type_desc = 'ROWS'
SET @CharIdx = CHARINDEX('.mdf',
@PhysicalDatabaseMDFName)
IF(@CharIdx >0)
BEGIN
SET @Length = LEN(@PhysicalDatabaseMDFName)
SET @Idx = @Length - CHARINDEX('\',REVERSE(@PhysicalDatabaseMDFName))
IF (@Idx >0)
BEGIN
SET @MDF = SUBSTRING(@PhysicalDatabaseMDFName, @Idx+2, @Length - @Idx +1)
SET @NewMDF =
@NewDatabaseName + '.mdf'
SET @PhysicalDatabaseNewMDFName = LEFT(@PhysicalDatabaseMDFName,@Idx+1) + @NewMDF
IF EXISTS (SELECT 1 FROM sys.master_files WITH (NOLOCK)
WHERE physical_name = RTRIM(LTRIM(@PhysicalDatabaseNewMDFName))
AND database_id <>
@DatabaseId)
BEGIN
SET @Return =
1
PRINT 'Error!
Database cannot be renamed, because data file '+
@PhysicalDatabaseNewMDFName + ' being refered for other database';
END
END
END
IF (@Return = 0)
BEGIN
-- getting the physical ldf name
SELECT Top 1
@PhysicalDatabaseLDFName = physical_name, -- physical log file
@LDFName = name -- logical name for
log file
FROM sys.master_files WITH (NOLOCK)
WHERE database_id =
@DatabaseId AND file_id
= 2 AND
type_desc = 'LOG'
SET @Idx = 0
SET @CharIdx = 0
SET @CharIdx = CHARINDEX('.ldf',
@PhysicalDatabaseLDFName)
IF(@CharIdx >0)
BEGIN
SET @Length = LEN(@PhysicalDatabaseLDFName)
SET @Idx = @Length - CHARINDEX('\',REVERSE(@PhysicalDatabaseLDFName))
IF (@Idx >0)
BEGIN
SET @LDF =
SUBSTRING(@PhysicalDatabaseLDFName, @Idx+2, @Length - @Idx +1)
SET @NewLDF =
@NewDatabaseName + '_log.ldf'
SET @PhysicalDatabaseNewLDFName = LEFT(@PhysicalDatabaseLDFName,@Idx+1) + @NewLDF
IF EXISTS (SELECT 1 FROM sys.master_files WITH (NOLOCK)
WHERE physical_name = RTRIM(LTRIM(@PhysicalDatabaseNewLDFName))
AND database_id <>
@DatabaseId)
BEGIN
SET @Return =
1
PRINT 'Error!
Database cannot be renamed, because log file '+
@PhysicalDatabaseNewLDFName + ' being refered for other database';
END
END
END
END
IF (@Return = 0)
BEGIN
-- Changing Physical file names -----------------------
SET @SQL = 'ALTER DATABASE ' +
@CurrentDatabaseName + '
MODIFY FILE (NAME = '''+ @MDFName +''', FILENAME = '''+ @PhysicalDatabaseNewMDFName +''');'
PRINT @SQL
EXEC(@SQL)
SET @SQL = 'ALTER DATABASE ' +
@CurrentDatabaseName + '
MODIFY FILE (NAME = '''+ @LDFName +''', FILENAME = '''+ @PhysicalDatabaseNewLDFName +''');'
PRINT @SQL
EXEC(@SQL)
-- Changing logical names -----------------------------
SET @SQL = 'ALTER DATABASE ' +
@CurrentDatabaseName + '
MODIFY FILE (NAME = '+ @MDFName + ', NEWNAME = ' + @NewDatabaseName +');'
PRINT @SQL
EXEC(@SQL)
SET @SQL = 'ALTER DATABASE ' +
@CurrentDatabaseName + '
MODIFY FILE (NAME = '+ @LDFName + ', NEWNAME = ' + @NewDatabaseName +'_log);'
PRINT @SQL
EXEC(@SQL)
-- To disconnect all the existing connection
SET @SQL = 'ALTER DATABASE ' +
@CurrentDatabaseName + '
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
PRINT @SQL
EXEC(@SQL)
-- To Take Offline the current database
SET @SQL = 'ALTER DATABASE ' + @CurrentDatabaseName +
' SET OFFLINE WITH ROLLBACK IMMEDIATE;'
PRINT @SQL
EXEC(@SQL)
-- Rename physical files
SET @SQL= 'REN "' +
@PhysicalDatabaseMDFName + '" "'
+
@NewMDF + '";'
PRINT
@SQL
EXEC xp_cmdshell @SQL
-- Rename physical files
SET @SQL= 'REN "' +
@PhysicalDatabaseLDFName + '" "'
+
@NewLDF + '";'
PRINT @SQL
EXEC xp_cmdshell @SQL
-- To disable xp_cmdshell:
SET @SQL = 'SP_CONFIGURE ''' + 'show advanced options' +
''' , 1
RECONFIGURE WITH OVERRIDE;'
PRINT @SQL
EXEC(@SQL)
SET @SQL = 'SP_CONFIGURE ''' + 'xp_cmdshell' + ''' , 0 RECONFIGURE
WITH OVERRIDE;'
PRINT @SQL
EXEC(@SQL)
-- To set multi-user access
SET @SQL = 'ALTER DATABASE ' +
@CurrentDatabaseName + '
SET MULTI_USER;'
PRINT @SQL
EXEC(@SQL)
-- To bring the databse online
SET @SQL = 'ALTER DATABASE ' +
@CurrentDatabaseName + '
SET ONLINE;'
PRINT @SQL
EXEC(@SQL)
-- Changing database name -----------------------------
SET @SQL = 'ALTER DATABASE ' +
@CurrentDatabaseName + '
MODIFY NAME = ' + @NewDatabaseName + ';'
PRINT @SQL
EXEC(@SQL)
END
END
END TRY
BEGIN CATCH
-- To set multi-user access
SET @SQL = 'ALTER DATABASE ' +
@CurrentDatabaseName + '
SET MULTI_USER;'
PRINT @SQL
EXEC(@SQL)
-- To bring the databse online
SET @SQL = 'ALTER DATABASE ' +
@CurrentDatabaseName + '
SET ONLINE;'
PRINT @SQL
EXEC(@SQL)
PRINT 'Error! Database cannot
be renamed, because due to file names are not found';
END CATCH
