Chagne Database Physical File name

Question

Chagne Database Physical File name

SQlLogic

SSCrazy Eights

Points: 9686
More actions
December 27, 2011 at 10:43 am

#248729

Hi Guys..
I have to change the physical file name of Databse so should i have to detach DB chance the file and and attach DB again ?
Or
I can take offline and change the physical file name and than bring it online?
Its on Test enviornment not production.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can detach, rename and attach or you can use ALTER DATABASE with modify file, then take the DB offline and make the change to the actual file name.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

have a look at Moving Database Files Detach/Attach or ALTER DATABASE?[/url]

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

george sibbald SSC Guru Points: 104210 More actions · Answer 3

backup\restore with move is another possibility but alter database is the best way to do this.

If at all possible backup the database first, just in case.

---------------------------------------------------------------------

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 4

thanks folks for nice reply . I did with Detach and Attach Option and it looks good now.

george sibbald SSC Guru Points: 104210 More actions · Answer 5

logicinside22 (12/27/2011)
thanks folks for nice reply . I did with Detach and Attach Option and it looks good now.

can I then recommend that you check logins still have the correct default database as the dbid may have changed, and the owner of the database is the same as before in case this is important to your application.

---------------------------------------------------------------------

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

george sibbald (12/27/2011)
logicinside22 (12/27/2011)
thanks folks for nice reply . I did with Detach and Attach Option and it looks good now.
can I then recommend that you check logins still have the correct default database as the dbid may have changed, and the owner of the database is the same as before in case this is important to your application.

Also TRUSTWORTHY database property (if applicable).

The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. By default, this setting is OFF, but can be set to ON by using the ALTER DATABASE statement.

For example,

ALTER DATABASE AdventureWorks2008R2 SET TRUSTWORTHY ON;.

Because a database that is attached to an instance of SQL Server cannot be immediately trusted, the database is not allowed to access resources beyond the scope of the database until the database is explicitly marked trustworthy. Also, modules that are designed to access resources outside the database, and assemblies with either the EXTERNAL_ACCESS and UNSAFE permission setting, have additional requirements in order to run successfully.

For More: http://msdn.microsoft.com/en-us/library/ms187861.aspx

toddasd SSCertifiable Points: 6298 More actions · Answer 7

Dev (12/28/2011)
george sibbald (12/27/2011)
logicinside22 (12/27/2011)
thanks folks for nice reply . I did with Detach and Attach Option and it looks good now.
can I then recommend that you check logins still have the correct default database as the dbid may have changed, and the owner of the database is the same as before in case this is important to your application.
Also TRUSTWORTHY database property (if applicable).
The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. By default, this setting is OFF, but can be set to ON by using the ALTER DATABASE statement.
For example,
ALTER DATABASE AdventureWorks2008R2 SET TRUSTWORTHY ON;.
Because a database that is attached to an instance of SQL Server cannot be immediately trusted, the database is not allowed to access resources beyond the scope of the database until the database is explicitly marked trustworthy. Also, modules that are designed to access resources outside the database, and assemblies with either the EXTERNAL_ACCESS and UNSAFE permission setting, have additional requirements in order to run successfully.
For More: http://msdn.microsoft.com/en-us/library/ms187861.aspx

Why copy pasta msdn and link to it? And what does this have to do with the topic?

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 8

toddasd (12/28/2011)
Dev (12/28/2011)
george sibbald (12/27/2011)
logicinside22 (12/27/2011)
thanks folks for nice reply . I did with Detach and Attach Option and it looks good now.
can I then recommend that you check logins still have the correct default database as the dbid may have changed, and the owner of the database is the same as before in case this is important to your application.
Also TRUSTWORTHY database property (if applicable).
The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. By default, this setting is OFF, but can be set to ON by using the ALTER DATABASE statement.
For example,
ALTER DATABASE AdventureWorks2008R2 SET TRUSTWORTHY ON;.
Because a database that is attached to an instance of SQL Server cannot be immediately trusted, the database is not allowed to access resources beyond the scope of the database until the database is explicitly marked trustworthy. Also, modules that are designed to access resources outside the database, and assemblies with either the EXTERNAL_ACCESS and UNSAFE permission setting, have additional requirements in order to run successfully.
For More: http://msdn.microsoft.com/en-us/library/ms187861.aspx
Why copy pasta msdn and link to it?

To make the suggestion brief and provide the a ref in case someone wants more info. #GoodJob !

And what does this have to do with the topic?

If you would have read the thread, you would have noticed OP chose the attach approach to accomplish his mission.