SQL 2000 Change Database Name

1. method 1 (physical method ):

Change old database to new database

Open Enterprise Manager"

Right-click "Old Database" and choose "all tasks" to separate the database.

Find old_data.mdf and

Old_log.ldf

Replace old_data with the name you want, such as new. MDF and new. LDF.

Go to the Enterprise Manager and find database -- Right-click -- all tasks -- add database --. In the pop-up dialog box, find the file new. MDF you just renamed.

In this case, you can see "original database file name" and "current file location". There will be a big red X before "current file location, then manually change the file name in the path of "current file location" to new. MDF, new. LDF, red X disappears, and the name of the database you want to use is written in "Add as:", "specify database owner:", and write the owner of the database. If this is wrong, it will be difficult to change in the future (at least most people think it is difficult to change ).

OK.

Previously I wrote this point, as if the customer did not say anything, and it should be possible, but today I found this problem:

Although the current database file name shows the desired "new", but the logic file name has not been modified yet, it is still the original "old_date ", open Enterprise Manager -- right-click on the data database and choose Properties -- the file name in front of the current database location path under the data file is still the original one, the file name under the transaction log file is also the previous file name, that is, it has not been completely successful, so we have to follow the method below --"

Tool -- SQL query analyzer -- and then enter:

Alter database new

Modify file (name = 'old _ data', newname = 'new _ data ')

Alter database new

Modify file (name = 'old _ log', newname = 'new _ log ')

-- Press F5 to run.

OK. Generally, you must set the space size for the user.

2. method 2 (Stored Procedure ):

Sp_renamedb

Change the database name.

Syntax

Sp_renamedb [@ dbname =] 'old _ name ',

[@ Newname =] 'new _ name'

Parameters

[@ Dbname =] 'old _ name'

Is the current name of the database. Old_name is of the sysname type and has no default value.

[@ Newname =] 'new _ name'

Is the new name of the database. New_name must follow the identifier rules. New_name is of the sysname type and has no default value.

Return code value

0 (successful) or non-zero number (failed)

Permission

Only members of SysAdmin and dbcreator fixed server roles can execute sp_renamedb.

Example

In the following example, change the database accounting to financial.

Exec sp_renamedb 'accounting', 'financial'