***Need a script to change a database name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

***Need a script to change a database name

Author

Topic

Paul_Russ
Starting Member

5 Posts

Posted - 2006-12-31 : 15:16:00

Hi,

This is my first posting to this forum. I'm trying to
I'm trying to change the name of my database from ASPNET.mdf to CulturedStar.mdf. I need to change the physical name and logical name.

My instance name is: sapphirelap\sqlexpress
Old Database name: ASPNET.mdf
New Database name: CulturedStar.mdf

I'm working with Microsoft SQL Server Management Studio Express.

Can someone give me a script to accomplish this?

Thank you so much for your time and effort ~ Paul

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2006-12-31 : 15:48:31

change newDbName aand oldDbName in this script to your real names. this will change the logical and physical names of mdf and ldf.


use master
go
EXEC sp_renamedb N'oldDbName', N'newDbName'
GO

ALTER Database newDbName
MODIFY FILE (NAME = 'oldDbName', NEWNAME = 'newDbName')
GO

ALTER Database newDbName
MODIFY FILE (NAME = 'oldDbName_log', NEWNAME = 'newDbName_log')
GO

Exec sp_detach_db newDbName
GO


-- THIS PART WILL FAIL in default setup. Either allow xp_cmd_shell,
--	or rename physical files in win explorer
Exec xp_cmdshell 'rename c:\sqlExpress\MSSQL.1\MSSQL\DATA\oldDbName.mdf newDbName.mdf'
Exec xp_cmdshell 'rename c:\sqlExpress\MSSQL.1\MSSQL\DATA\oldDbName.mdf newDbName.mdf'
GO

sp_attach_db 'newDbName', 'c:\sqlExpress\MSSQL.1\MSSQL\DATA\newDbName.mdf', 'c:\sqlExpress\MSSQL.1\MSSQL\DATA\newDbName_log.ldf'
GO

Paul_Russ
Starting Member

5 Posts

Posted - 2006-12-31 : 16:56:35

use Master
go
EXEC sp_renamedb N'ASPNET', N'CulturedStar1'
GO

ALTER Database CulturedStar1.mdf
MODIFY FILE (NAME = 'ASPNET', NEWNAME = 'CulturedStar1')
GO

ALTER Database CulturedStar1.ldf
MODIFY FILE (NAME = 'ASPNET.ldf', NEWNAME = 'CulturedStar1.ldf')
GO

This is what I tried and it didn't work. I'm a newbie to SQL Server and I'm not sure if I have the right script. Anything you can see that I'm obviously doing wrong? Thanks ~ Paul

nr
SQLTeam MVY

12543 Posts

Posted - 2006-12-31 : 18:59:17

Which bits didn't work?
Your database rename looks ok. May fail if there were any connections though.

ALTER Database CulturedStar1.mdf
MODIFY FILE (NAME = 'ASPNET', NEWNAME = 'CulturedStar1')
s.b.
ALTER Database CulturedStar1
MODIFY FILE (NAME = 'ASPNET', NEWNAME = 'CulturedStar1')

ALTER Database CulturedStar1.ldf
MODIFY FILE (NAME = 'ASPNET.ldf', NEWNAME = 'CulturedStar1.ldf')
s.b.
ALTER Database CulturedStar1
MODIFY FILE (NAME = 'ASPNET.ldf', NEWNAME = 'CulturedStar1.ldf')

that should alter the logical filenames.
For the physical files I would do a backup and restore (in fact I would do that to rename the database too).

Have a look at the commands in bol.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2006-12-31 : 19:05:41

have permissions? i wouldn't recommend backup/restore. for someone new to sql server, too likely to lose recent transactions. just detach db, rename physical file, re-attach

what error messages do u get?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-12-31 : 21:03:01

You will need to stopaccess to the database to do this.
The safest method is to backup the database then restore to the new name - renaming the physical files. After you have verified it is ok you can drop the old database, rename the logical files and then restart the system.

If you are going to detach then you should do a backup first in case the attach fails (and given you have a backup why not restore it - you should to test it depending on how much risk you are happy with).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-01-01 : 04:36:33

"i wouldn't recommend backup/restore. for someone new to sql server"

And I wouldn't recommend a DETACH unless they had a backup ...

Kristen

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-01 : 11:52:19

"And I wouldn't recommend a DETACH unless they had a backup .."

definitely agree.

"The safest method is to backup the database then restore to the new name"

not so. backup/restore not safest. easiest, but not safest

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-01 : 13:36:12

>> not so. backup/restore not safest. easiest, but not safest
It's not easiest.
It's safest because you still have the old database which you can always go back to. You don't drop the old database until the process is complete and the new version is verified.
Difficult to see how anything could be less risky.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-01-01 : 16:54:13

"not so. backup/restore not safest. easiest, but not safest"

So, what do you reckon is the safest then, and why? Discuss!!

Kristen

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-01-01 : 17:41:44

I'd like to know what's going to happen if any of the code uses the 3 part naming convention

--Jeff Moden

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-01 : 18:52:46

Don't think it would affect any of this code.

If you're talking about application code then of course anything that refers to the database by name will have to be changed unless some indirection is incorporated.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources