Rename a database -- sql server new users

sql server new users : Rename a database

et
5/6/2006 11:06:33 AM

How do I rename a database on sql server 2000? I have a database I brought
up from a backup and has the same name as the current database. I don't
want to detach the current one because I have to get data from both
databases. I tried renaming the file name of the backup copy, and in the
attach database dialog box, renaming the database, but it won't let me,
since the "OriginalFileName" is the same name as the current one. If we
can't rename a database, then why give us the option??? Thanks for your
help.

Andrea Montanari
5/6/2006 7:45:30 PM

hi,
[quoted text, click to view]

you have to specify the MOVE part of the RESTORE syntax..
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6ng9.asp
you do not need to modify he logical names but only the "physical" files( as
long as providing a new database name), so that the "new" database will
point to other physical files...
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

nate.vu NO[at]SPAM gmail.com
5/6/2006 10:05:19 PM

Do you just want to change the name of the database. So, for example, I
have the sample database "Northwind" and I want to rename it
"Northwind2".

If that's what you want then you can use the sp_renamedb stored
procedure. For example:

exec sp_renamedb @dbname = '<Current DB Name>', @newname = '<New DB
Name>'

Hope that helps a bit, but sorry if that's not what you're after. If
I've totally missed the point then give us a buzz and maybe we can
figure it out.

Rhonda Veit
5/11/2006 12:25:44 PM

Create a new database with the name you want this backup one to have. Then
restore the backup file over it. Be sure to go to the second table and
select "Force Restore" over the existing database.

[quoted text, click to view]

Mike Hodgson
5/12/2006 12:00:00 AM

That's not enough - you need to specify the WITH MOVE option in the
RESTORE statement (ie. change the names/locations of the physical files
in the options tab of the restore dialog box). Also, since the RESTORE
statement will automatically create the database if it doesn't exist
(and implicitly drop it if it does), you don't need to create the new DB
first - that will just result in unnecessary I/O.

--
*mike hodgson*
http://sqlnerd.blogspot.com

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn