subota, 1. lipnja 2013.

Moving SQL server database files

Lately my VPS server C disk space seems to be filling-up rapidly. So I decided to get some extra space (its risky to append C-disk space, so I agreed to create additional partition) - let's call it E. Since my database .mdf files seem to take too much space, let's try to move them to this new partition.

First, I tried to detach the database, copy it to new location, and try to reattach it. Unfortunately - it doesn't seem to work. Exception was:

Unable to open physical file - Operating system error 5: 5(error not found) Microsoft SQL Server: Error 5120

After few minutes of google-ing, problem appeared to be due to security restrictions. Easiest way (allthough not the most secure) is to share a folder that my .mdf copy is located - unfortunately, it didn't work either - again the same exception.

After a bit more google-ing, found some solutions saying to allow sql express service full control over that specific file. That sounded like a splendid idea, but as a no-system-knowledge programmer, I stumbled upon this - until I checked service properties:


Basically, only thing one needs to do is set file permissions through this super-user-friendly screen:


After this, hitting "ENTER" does the trick:


And whoalla - it worked!


Nema komentara:

Objavi komentar