2

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.

Ryan
  • 123
  • 3

3 Answers3

1

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

Khaled
  • 36,533
  • 8
  • 72
  • 99
1

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.

DaniSQL
  • 1,107
  • 7
  • 12
0

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.

Peter Schofield
  • 1,639
  • 9
  • 11