Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to move few DB files to another location on same server due to low disk space. Replication is present with that DB and this scenario is on subscriber server. Is there any way to do this without doing reconfiguration?

share|improve this question
add comment

migrated from stackoverflow.com Nov 23 '11 at 6:20

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 2 down vote accepted

Here's how I'd do it:

  1. Schedule some time where the database can be down for the amount of time to move the files
  2. Execute a series of 'alter database [foo] modify file (name = 'foo_1', filename = 'new location here') statements
  3. alter database [foo] set offline (you'll have to kill any active spids in the db or wait for them to finish their business)
  4. move your files
  5. alter database [foo] set online

If you want to be really clean about this, you can stop the distribution agent while the database is offline and restart it when it's back online. But that's not necessary. Just so you know, I've also done this with a distribution database and would imagine that it would work fine with a publisher, too.

share|improve this answer
    
alter Database [testDB] set Offline; alter database [testDB] modify file (name = 'testDB', filename = 'D:\SQL Server Databases\Data') alter Database [testDB] Set Online; Now getting error: Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "D:\SQLServerDatabase\Data". Operating system error 5: "5(Access is denied.)". Msg 945, Level 14, State 2, Line 1 Database 'testDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. –  Kamaljeet Kumar Nov 24 '11 at 3:26
    
Tried with Everyone (Full Access). Network Service/Service/System (Full Access). No luck :( –  Kamaljeet Kumar Nov 24 '11 at 3:41
    
The account that's running your SQL Service needs to have read/write permissions on the files. Incidentally, is the name of your data file really "data"? You need the full path to the file, not to the folder in which the files resides. I'd have expected something like "D:\SQL Server Databases\Data\testDB.mdf". That could be your problem. –  Ben Thul Nov 24 '11 at 15:42
    
Thanks Ben. It has been resolved now :) –  Kamaljeet Kumar Nov 25 '11 at 3:36
    
No problem. For the benefit of people who come across this in the future, would you mind telling what went wrong and what you did to fix it? Thanks! –  Ben Thul Nov 25 '11 at 4:40
show 1 more comment

You should be able to do this by altering the clustered index and pointing it to be created on SECONDARY filegroup instead of the PRIMARY. The clustered index is what specified where the physical data is stored. Create a new filegroup in your database called SECONDARY and point it to your other location. Use CREATE INDEX ....... WITH DROP_EXISTING on your clustered index specify the new filegroup in the ON clause.

Download some SQL to move lots of tables to another filegroup here: Adam Haines

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.