Solved

Sql Server 2000 - Rename Database

Posted on 2006-11-10
11
501 Views
Last Modified: 2012-05-05
Experts,
Is there a way to rename an existing SQL Server 2000 database?
0
Comment
Question by:feesu
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 50 total points
ID: 17920181
Hi feesu,

exec EXEC sp_renamedb 'old', 'newName'


Cheers!
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17920190
You can also backup and restore as different database name.
0
 

Author Comment

by:feesu
ID: 17920215
Hi aneeshattingal,
I got the following:
Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17920248
run sp_who2 and kill all the process id's on that database , then rename the db
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17920287
like aneeshattingal indicated, there must be on connections to the database.
this includes yourself with your query analyser (USE MASTER for example) and/or your enterprise manager (close the databases folder to be sure), and other applications like visual studio & co.
with sp_who2 or in the enterprise activity monitor you can see which processes are still connected to the database, and you can use KILL comment in query analyser or enterprise context menu in the activity monitor.
0
 

Author Comment

by:feesu
ID: 17920308
Thanks everyone!
0
 

Author Comment

by:feesu
ID: 17920426
Hi again, Why doesn't it rename the physical files as well? And my i rename them manually?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17920456
>Why doesn't it rename the physical files as well?
as renamedb only renames the db, not the files. this is due to the fact that you can give any name you want to the files (well, at least any name that is legal as OS file name, of course).

to rename the files, you have 2 methods:
* go through the BACKUP database + RESTORE, during the restore you can specify new file names and locations for each logical file
* use the detach + attach method, in between you can move/rename the files.
0
 

Author Comment

by:feesu
ID: 17920506
Thanks alot angel!
0
 

Author Comment

by:feesu
ID: 17924289
Hi angel, It gives an error if you rename after deataching and try to attach the new file name.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17924317
here is an example from books online

E. Make a copy of a database using BACKUP and RESTORE
This example uses both the BACKUP and RESTORE statements to make a copy of the Northwind database. The MOVE statement causes the data and log file to be restored to the specified locations. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored. The new copy of the database is named TestDB. For more information, see RESTORE FILELISTONLY.

BACKUP DATABASE Northwind
   TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
   FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
   FROM DISK = 'c:\Northwind.bak'
   WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
   MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question