Solved

Sql Server 2000 - Rename Database

Posted on 2006-11-10
11
534 Views
Last Modified: 2012-05-05
Experts,
Is there a way to rename an existing SQL Server 2000 database?
0
Comment
Question by:feesu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

617 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