SQL 2005 Database Files

Question

Hi,

We having database called   MoneyTrans Which contains 4 database file as below:

LogicalName Filetype File Group             Path

MoneyTrans     Data          Primary        d:\data

Expenses        Data         expen           e:\

Receivables     Data          receiv           f:\

payments_log  Daata        log               m:\

 

Now my question is i want to change the path of receivables from f:\ to some other drive like L:\.

IS it possible to change path without restoring entire Database?.

We are using SQL Server 2005.

 

Answer 1

Yes, you need to look at ALTER DATABASE MODIFY FILE syntax. This change can be done in realtime but the changes won't be reflected until the SQL Server is re-started.

 

Look for examples here.

http://msdn.microsoft.com/en-us/library/bb522469.aspx

---

http://SankarReddy.com/

Answer 2

THANK YOU VERY MUCH SANKAR.

Answer 3

@Vijay,

There is no need to restart the sql server.

Once alter the database you can put the database offline and move the files physically and put the database back to online.It will refelect.

---

Muthukkumaran Kaliyamoorthy SQLDBA MyBlog-->sqlserverblogforum

Answer 4

Hi ,

 

Successfully moved the Datafiles from one location to another location.

But my doubt is what about Access permissions, Indexes on that DB and so on?

And one more doubt is if i add a network drive like share drive. How SQl will recognize?

Answer 5

User access to SQL objects relying on the user permissions set under SQL security not windows file system. so move the file shouldn't affect the access permission.

if i add a network drive like share drive. How SQl will recognize? it will be no problem, however, Why would you put data into a shared network drive?

Answer 6

Steven is correct. Moving the files does not change object level permission within the database.

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq

Answer 7

Vijay Kumar,

 Have you seen any problem ?

Run this query it will report the users in the current database & Sid too.

EXEC sp_change_users_login 'Report';

Putting the sql files to share location is not best practice.

Do you have any disk space problem.

---

Muthukkumaran Kaliyamoorthy SQL DBA MyBlog-->sqlserverblogforum Please click the Mark as Answer button if a post solves your problem!

Answer 8

Vijay Kumar,
Have you seen any problem ?
Run this query it will report the users in the current database & Sid too.
EXEC sp_change_users_login 'Report';

---

Muthukkumaran Kaliyamoorthy SQL DBA MyBlog-->sqlserverblogforum Please click the Mark as Answer button if a post solves your problem!

This is NOT needed if database files are moved on same server.

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq

Answer 9

@Balmukund

yes agreed.

Now my question is i want to change the path of receivables from f:\ to some other drive like L:\.

I Didn't read this. (:-

---

Muthukkumaran Kaliyamoorthy SQL DBA MyBlog-->sqlserverblogforum Please click the Mark as Answer button if a post solves your problem!

Answer 10

Hi Steven,

Having some problem with one more server.

E:\ Almost full which contain abc.ndf file . there is no way to add additional disk space.

What i am planning is i found one table calls msg table contains 50 GB of data, if i archive old data atleast i can get some free diskspace.

Is it correct way? if it is correct how to archive the old data?

 

Answer 11

S. Vijay Kumar,
1. Use Alter database and move the file location to other drive.
2. Take Database OFFLINE.
3. Cut the file from the current location to new location.
3. Bring database online.

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq

Answer 12

I agree with Balmukund. Will only suggest that DONT cut the file to the new Location, rahter copy and paste it if file size and network speed is not an issue. I have encountered a scenario in the past where the file got corrupted on the destination and since this was a cut paste not copy >> paste, we had to finally restore the database from a previous copy.

once you see after copy and paste that the database could come online, you can delete the non required file.

Amit Pandey

 

Answer 13

Agree with Amit. Cut would be dangerous if something goes wrong.

Thanks Amit for adding your comments.

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/blakhani
Team Blog: http://blogs.msdn.com/sqlserverfaq

Answer 14

hi Balmukund,

Having some problem with one more server.

E:\ Almost full which contain abc.ndf file . there is no way to add additional disk space.

What i am planning is i found one table called msg table contains 50 GB of data, if i archive old data atleast i can get some free diskspace.

Is it correct way? if it is correct how to archive the old data?

 

Answer 15

You don't have any other existing Disk(with free space). If you have move the NDF files to taht disk.

If you have any other choice check the usused space for that file and shrink, But keep in mind the the perfect DBA will not go for shrinking.It will leads fragmentation.

Users need the archive data? (OR)

Its for some other reference.

Droping the table only gives the 50 GB of free space. So let me answer the above question.

 

---

Muthukkumaran Kaliyamoorthy SQL DBA MyBlog-->sqlserverblogforum Please click the Mark as Answer button if a post solves your problem!