Three methods of moving File path move datafile in Sqlserver

Three methods of moving Files in sqlserver

1. Set the database separation, copy the files to the new directory, and then attach

2. Set the database offline, and then copy the files to the new directory

Then ALTER DATABASE XX MODIFY FILE (NAME='logicalname',FILENAME=' new directory\ YY.dbf')

Go online again

3. ALTER DATABASE XX MODIFY FILE (new NAME='logicalname',FILENAME=' directory\ YY.dbf')

Close the instance, copy the file to the new directory, and then start the instance

The statement that queries NAME and FILENAME, assuming the database name is XX

SELECT name,physical_name FROM master.sys.master_files WHERE database_id = DB_ID (NissXX')

If the file type is filestream, the above FILENAME is the directory (the following D:\ FS1.FILESTREAM\ Netfile_FS1 is the directory)

ALTER DATABASE Netfile1 MODIFY FILE (NAME = 'Netfile_FS1', FILENAME =' D:\ FS1.FILESTREAM\ Netfile_FS1')

Matters needing attention

1. The master copy of always on does not apply to the above methods 1 and 2, but only to the above methods 3.

2. The auxiliary copy of always on does not apply to methods 1, 2 and 3 above, and special treatment needs to be done on the basis of method 3.

3. Neither the master library nor the slave library of mirror is applicable to the above methods 1 and 2, but only to the above methods 3

4. The main library of logshipping is applicable to the above methods 1, 2, 3

5. The slave library of logshipping does not apply to the above methods 1 and 2, but only to the above methods 3.

When setting up the database offline, execute the following first to see if there is a transaction. If there is a transaction, either kill it or wait for the transaction to finish.

DBCC OPENTRAN (dbname)

Or

Select * from sys.sysprocesses where dbid=db_id ('dbname')