I am having a SQL Server 2008 installation with almost 15 databases running on it. Now due to scarcity of space I would like to move the data path to another drive. What is the best practice for this. Please explain in details if including any SQL commands as I'm relatively new to SQL Server administration.
Note - I have already changed the path in SQL server properties from SQL Management Studio 2008, to the new path. But I would also like the existing databases to reside in the new path.
First, detach database:
Next step - copy files .mdf and .ldf of this database files to new location
And then attaching the database:
If you don't want to attach and detach all databases one-by-one, you can generate SQL script to attach and detach all databases you need (execept system, of course), using curosr that searches in sys.databases dynamic management view. But don't forget to copy the database files.