Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have an installation of MSSQL where I would like to move the data directory to another drive so that all the existing databases are located there and all new databases are created there, as well as the backups, logs, etc. I know I can detach/attach the existing databases, but what about the rest of the settings (backup, new databases)? Is this possible without an uninstall/reinstall? Thank you.

share|improve this question

3 Answers 3

up vote 1 down vote accepted

Yes it is possible with out uninstall/install. Just move the existing user databases by attach/detach method and you can modify the settings for both default data directory(which newly created databases be stored) and the default backups directory for the future. Here is how.

share|improve this answer

You can have a look at this page. They are talking about moving the databases and the logs.

share|improve this answer

There is another way for datafiles for user databases that requires no outage, just a performance degradation - but it is for experienced DBAs. For larger files, you're also at the mercy of disc IO speed. Always do it in pre-prod first!

Methods:

1 - detach and re-attach - easy, but requires an outage for that database

2 - backup and restore - nearly as easy, requires a shorter outage - but you need to be careful.

3 - if you have one datafile: create a new datafile on the right drive, and then select the original datafile from sys.sysfiles - and run something like DBCC SHRINKFILE(logical file name,emptyfile) - this will empty the data from the file, and put it in the new datafile. Then you delete the original datafile.

share|improve this answer

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.