How to rename MS SQL database filenames without breaking integration with Plesk?

Created:

2016-12-12 03:44:12 UTC

Modified:

2017-08-08 13:34:50 UTC

0

Was this article helpful?


Have more questions?

Submit a request

How to rename MS SQL database filenames without breaking integration with Plesk?

Applicable to:

  • Plesk 12.5 for Windows

Question

Due to the bug #PPPM-4974 described in #213936025 database name and log file are too long on the file system.How to rename the database name and log file on the filesystem?

Answer

  1. Create a backup of databases .

  2. Log in to MS SQL Management Studio, create an SQL query and execute it (renaming the database name from 19915381_test to the required one):

    /*Check database and log names */
    USE master
    GO
    /* Identify Database File Names */
    SELECT
    name AS [Logical Name],
    physical_name AS [DB File Path],
    type_desc AS [File Type],
    state_desc AS [State]
    FROM sys.master_files
    WHERE database_id = DB_ID(N'19915381_test')
    GO
  3. Detach the database in MSSQL:

    /* Detach Current Database */
    USE [master]
    GO
    EXEC master.dbo.sp_detach_db @dbname = N'19915381_test'
    GO
  4. Once the database is detached, rename its files in C:\\Parallels\\Plesk\\Databases\\MSSQL\\MSSQL12.MSSQLSERVER2014\\MSSQL\\DATA . For example:

    C:\\Parallels\\Plesk\\Databases\\MSSQL\\MSSQL12.MSSQLSERVER2014\\MSSQL\\DATA\\19915381_test.mdf
    C:\\Parallels\\Plesk\\Databases\\MSSQL\\MSSQL12.MSSQLSERVER2014\\MSSQL\\DATA\\19915381_test_log.ldf
  5. Attach the database back:

    USE [master]
    GO
    CREATE DATABASE "19915381_test" ON
    ( FILENAME = N'C:\\Parallels\\Plesk\\Databases\\MSSQL\\MSSQL12.MSSQLSERVER2014\\MSSQL\\DATA\\19915381_test.mdf' ),
    ( FILENAME = N'C:\\Parallels\\Plesk\\Databases\\MSSQL\\MSSQL12.MSSQLSERVER2014\\MSSQL\\DATA\\19915381_test_log.ldf' )
    FOR ATTACH
    GO
    ALTER DATABASE "19915381_test" SET MULTI_USER
    GO
Have more questions? Submit a request
Please sign in to leave a comment.