Moving data or log files to another location without detaching database.

There are various ways you can move the datafiles or logfiles to new location, However, I am going to share the method I used because I don't need to detach the database and reattach the database using this method. This method saves lot of maintenance time, in case if your database is replicated or it is required to be present 24/7. However, there will be performance fallback while performing any of these methods.


The only limitation using this method of moving files is that the primary data file and primary log file can not be moved while online, however the primary files can be marked as Empty File and sql server will transfer all data to new file and sql server will not put data in files marked as empty files anymore.

It has to be done in the following sequence:

1. Add new files to database to new location.
2. Transfer data from old file to new files and marking file as not to be used for storing any data.
3. Remove old files (except primary data or primary log file). Though primary data or log file will not be used for storing data in future and all existing data will be moved to new file in same filegroup.

Here are the details you can follow against each step I mentioned above:-

1. Add new files to database to new location.

Assuming that you have database named TESTING and you have a file group named SECONDARY. Just make sure that you add new file to one of the existing FILEGROUPS (In my case it is SECONDARY), It is required because the existing data will be moved to existing FILEGROUPS when you mark the database as EMPTYFILE. Run the following script, edit the parameters as per your requirements.

ALTER DATABASE testing
ADD FILE ---- for adding log file just change this line to ADD LOG FILE
(NAME = 'data_5'
, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\data_5_Data.NDF'
, SIZE =5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB)
TO FILEGROUP secondary

2. Move data from existing file to another file and mark file as EMPTYFILE so that you could remove the file later.

DBCC SHRINKFILE(data_3, EMPTYFILE)
GO

Once you mark the file as EMPTYFILE, you may delete the file from list, or you can keep the file if the file is Primary file, however, the file now is empty (no pages) and sql will not store any data in this file.

3. This is the final step. Remove the file you just marked as EMPTYFILE

ALTER DATABASE TESTING
REMOVE FILE data_3
GO

Thanks
Swarndeep