Wednesday, 28 June 2017

Rename Database in SQL Server


How to rename the existing database in SQL server [rename database]?

Example: Change database name from [TestDev] to [TestProd]. Please find the following steps.

  1. Open Microsoft SQL Server Management Studio.

  1. To kill all the sessions

USE master;
GO

 DECLARE @SQL varchar(8000) = ''; 
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 DB_NAME(sp.dbid)  = 'TestDev' 

 -- Kill all the opened sessions 
EXEC(@SQL);

  1. Disconnect all the existing connection
ALTER DATABASE TestDev SET SINGLE_USER WITH ROLLBACK IMMEDIATE


  1. To Take Offline the current database
ALTER DATABASE [TestDev] SET OFFLINE WITH ROLLBACK IMMEDIATE

  1. To change physical file name for data file (.mdf).
ALTER DATABASE TestDev MODIFY FILE (NAME = 'TestDev', FILENAME = 'D:\MSSQL\DATA\TestProd.mdf');

  1. To change physical file name for log file (.ldf).
ALTER DATABASE TestDev MODIFY FILE (NAME = 'TestDev_log', FILENAME = 'D:\MSSQL\DATA\TestProd_log.ldf');

  1. To change logical name for data file (.mdf).
ALTER DATABASE TestDev MODIFY FILE (NAME = TestDev, NEWNAME = TestProd);

  1. To change logical name for log file (.ldf).
ALTER DATABASE TestDev MODIFY FILE (NAME = TestDev_log, NEWNAME = TestProd_log);

9.  Go to the location that MDF and LDF files are located in server and rename mdf and ldf files exactly as you specified in step 3 ('D:\MSSQL\DATA\TestProd.mdf') and step 4 ('D:\MSSQL\DATA\TestProd_log.ldf') alter commands. Otherwise use the following shell script for renaming the files

EXEC SP_CONFIGURE 'show advanced options' , 1  RECONFIGURE WITH OVERRIDE;
EXEC SP_CONFIGURE 'xp_cmdshell' , 1  RECONFIGURE WITH OVERRIDE;
EXEC xp_cmdshell 'ren "D:\MSSQL\DATA\TestDev.mdf" "TestProd.mdf"';
EXEC xp_cmdshell 'ren "D:\MSSQL\DATA\TestDev_log.ldf" "TestProd_log.ldf"';
EXEC SP_CONFIGURE 'show advanced options' , 1  RECONFIGURE WITH OVERRIDE;
EXEC SP_CONFIGURE 'xp_cmdshell' , 0  RECONFIGURE WITH OVERRIDE;


  1. To set multi-user connection
ALTER DATABASE TestDev SET MULTI_USER 

  1. To Bring Online the current database.
ALTER DATABASE [TestDev] SET ONLINE

  1. To change database name.
ALTER DATABASE TestDev MODIFY NAME = TestProd;

  1. Right click on the Database Refresh.



The following SQL script rename database from [TestDev] to [TestProd].

--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

Posted by at

No comments:

Post a Comment