Thursday, March 29, 2012

database name changing

I would like to change database name but also to change database data file n
ame and log file name.
Any suggestions?
Changing only name is not a problem.
Thanks
PrzemoHi
Take a look at ALTER DATABASE command on BOL
Why do you want to change a phsycal file name?
create database test
go
sp_detach_db test (Change now the name of files)
go
sp_attach_db test,
@.filename1 = N'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\test1.mdf',
@.filename2 = N'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\test_log1.ldf'
"Przemo" <anonymous@.discussions.microsoft.com> wrote in message
news:95FB2D1B-C881-4382-ADDF-D927F99D7FC0@.microsoft.com...
> I would like to change database name but also to change database data file
name and log file name.
> Any suggestions?
> Changing only name is not a problem.
> Thanks
> Przemo|||Hi,
Database Name change
--
1. Remove all the users connected to the database
alter database <dbname> set single_user with rollback immediate
2. Run the below command to rename the database
alter database <current_db_name> modify name=<new_db_name>
You can replace the step-2 with sp_renamedb system stored Procedure.
Data and Log file name change
--
1. Detach the database using SP_DETACH_DB
2. Rename the physical files
3. Attach back the files using SP_ATTACH_DB with new physical file names.
Thanks
Hari
MCDBA
"Przemo" <anonymous@.discussions.microsoft.com> wrote in message
news:95FB2D1B-C881-4382-ADDF-D927F99D7FC0@.microsoft.com...
> I would like to change database name but also to change database data file
name and log file name.
> Any suggestions?
> Changing only name is not a problem.
> Thanks
> Przemo|||Przemo
It is pretty simple
1. Using the GUI you can detach the DB. (Make sure you have a backup first
)
2. Then go to the file location and rename the files
3. Then Using the GUI again you can attach the DB. It will automattically
show the old file names and path. Change the path names to match the new na
ming and rename the DB and you are done.
Jeff Duncan
MCDBA, MCSE+I

No comments:

Post a Comment