Rename a database in SQL Server 2000

The database changes before a project, but previously written programs support old databases. New databases have some problems and need to be modified, but the databases are repeated, so we need to rename the database on the development machine.

 

Here, record the steps (old name: mobilemsg, new name: mobilemsg_old ):

 

1. Turn off the Enterprise Manager and open the query analyzer;

 

2. Modify the Database Name:

 

Exec sp_dboption 'lelemsg ', 'single user', 'true'
Go
Exec sp_renamedb 'lelemsg ', 'lelemsg _ old'
Go
Exec sp_dboption 'lelemsg _ old', 'single user', 'false'
Go

 

3. Modify the logical name of the database:

 

Alter database mobilemsg_old Modify file (name = 'lelemsg _ data', newname = 'lelemsg _ old_data ')
Alter database mobilemsg_old Modify file (name = 'lelemsg _ log', newname = 'lelemsg _ old_log ')
Go

 

4. Disable the SQL Server service and modify the physical Name:

 

C:/program files/Microsoft SQL Server/MSSQL/data/mobilemsg_old_data.mdf

C:/program files/Microsoft SQL Server/MSSQL/data/mobilemsg_old_log.mdf

 

5. Rename the physical database file:

 

Exec xp_cmdshell 'rename C:/program files/Microsoft SQL Server/MSSQL/data/mobilemsg_data.mdf C:/program files/Microsoft SQL Server/MSSQL/data/mobilemsg_old_data.mdf'
Exec xp_cmdshell 'rename C:/program files/Microsoft SQL Server/MSSQL/data/mobilemsg_log.ldf C:/program files/Microsoft SQL Server/MSSQL/data/mobilemsg_old_log.ldf'
Go