Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sql Server 2000 - Rename Database

Posted on 2006-11-10
11
Medium Priority
?
546 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

886 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