Solved

Sql Server 2000 - Rename Database

Posted on 2006-11-10
11
512 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

828 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