How to Rename a Microsoft SQL Server Database using SQLPro

Renaming a Microsoft SQL Server database can be a complex process, but it's essential in certain situations, such as when you need to change the database name or move it to a different server. In this article, we will demonstrate how to rename a SQL Server database using SQLPro.

Step 1: Open the Query Window

To start renaming your database, open the query window in SQLPro and connect to the SQL Server where your database is located. Make sure you have the necessary permissions to perform these actions.

Step 2: Disable xp_CmdShell for Security Reasons

Before we begin, it's essential to disable xp_CmdShell for security reasons. This will prevent unauthorized access to the database files. To do this, execute the following script:

```

SET @xp_cmdshell = 'CREATE DATABASE MyDB ON ( FILENAME = ''C:\Path\To\MyDB.mdf''), ( FILENAME = ''C:\Path\To\MyDB_log.ldf'') FOR ATTACH'

```

Step 3: Rename the Physical and Logical Names

Next, we need to rename the physical and logical names of our database. To do this, execute the following script:

```

RENAME 'C:\Path\To\MyDB.mdf' TO 'MyDB_OLD.mdf'

PRINT xp_CmdShell = 'RENAME ''C:\Path\To\MyDB_log.ldf'' TO 'MyDB_OLD_log.ldf''

RENAME 'C:\Path\To\MyDB.mdf' TO 'MyDB.mdf'

PRINT xp_CmdShell = 'RENAME ''C:\Path\To\MyDB_log.ldf'' TO 'MyDB_log.ldf''

```

Step 4: Attach the New Database Files

After renaming the physical and logical names, we need to attach the new database files. To do this, execute the following script:

```

DECLARE @NewDatabaseName nvarchar(300) = 'NewMyDB', @OldDatabaseName nvarchar(300) = 'MyDB'

IF (EXISTS (SELECT * FROM sys.databases WHERE name = @NewDatabaseName)) OR EXISTS (SELECT * FROM sys.databases WHERE name = @OldDatabaseName)

BEGIN

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE NewMyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE MyDB MODIFY NAME = @OldDatabaseName

ALTER DATABASE NewMyDB MODIFY NAME = @NewDatabaseName

END

ALTER DATABASE @OldDatabaseName MODIFY FILE (NAME=N'MyDB', NEWNAME=N'MyDB_OLD')

ALTER DATABASE @NewDatabaseName MODIFY FILE (NAME=N'NewMyDB', NEWNAME=N'MyDB')

EXEC _detach_db = N'MyDB_Old'

```

Step 5: Enable xp_CmdShell

Finally, we need to enable xp_CmdShell. To do this, execute the following script:

```

DECLARE @PhysicalName nvarchar(300) = (SELECT physical_name FROM sys.master_files WHERE name = 'MyDB')

DECLARE @LogicalName nvarchar(300) = (SELECT physical_name FROM sys.master_files WHERE name = 'MyDB_log')

DECLARE @NewPhysicalName nvarchar(300) = (SELECT physical_name FROM sys.master_files WHERE name = 'NewMyDB')

DECLARE @NewLogicalName nvarchar(300) = (SELECT physical_name FROM sys.master_files WHERE name = 'NewMyDB_log')

xp_CmdShell will be executed under the user which the SQL Server process runs as, and might not have the file system permissions required to rename the database files.

```

Renaming a Microsoft SQL Server database can be a complex process, but with these steps, you should be able to achieve it using SQLPro. Remember to disable xp_CmdShell for security reasons and attach the new database files after renaming the physical and logical names. The original database will be left detached, and the new database will replace it.

Sqlpro for mssql rename database