?
Solved

Sql Server 2000 - Rename Database

Posted on 2006-11-10
11
Medium Priority
?
549 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 200 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 143

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 143

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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

579 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