[sql] Move SQL Server 2008 database files to a new folder location

Logical Name

• my_Data
• my_Log

Path:

• C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
• C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

FileName:

• my.MDF
• my_1.LDF

What would be the sql script to move these files to a new location: D:\DATA

Database is live so I would need to close existing connections.

To add the privileges needed to the files add and grant right to the following local user: SQLServerMSSQLUser$COMPUTERNAME$INSTANCENAME, where COMPUTERNAME and INSTANCENAME has to be replaced with name of computer and MSSQL instance respectively.

You can use Detach/Attach Option in SQL Server Management Studio.

Check this: Move a Database Using Detach and Attach

This is a complete procedure to transfer database and logins from an istance to a new one, scripting logins and relocating datafile and log files on the destination. Everything using metascripts.

http://zaboilab.com/sql-server-toolbox/massive-database-migration-between-sql-server-instances-the-complete-procedure

Sorry for the off-site procedure but scripts are very long. You have to:
- Script logins with original SID and HASHED password
- Create script to backup database using metascripts
- Create script to restore database passing relocate parameters using again metascripts
- Run the generated scripts on source and destination instance.
See details and download scripts following the link above.

Some notes to complement the ALTER DATABASE process:

1) You can obtain a full list of databases with logical names and full paths of MDF and LDF files:

   USE master SELECT name, physical_name FROM sys.master_files

2) You can move manually the files with CMD move command:

Move "Source" "Destination"

Example:

md "D:\MSSQLData"
Move "C:\test\SYSADMIT-DB.mdf" "D:\MSSQLData\SYSADMIT-DB_Data.mdf"
Move "C:\test\SYSADMIT-DB_log.ldf" "D:\MSSQLData\SYSADMIT-DB_log.ldf"

3) You should change the default database path for new databases creation. The default path is obtained from the Windows registry.

You can also change with T-SQL, for example, to set default destination to: D:\MSSQLData

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\MSSQLData'

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\MSSQLData'

GO

Extracted from: http://www.sysadmit.com/2016/08/mover-base-de-datos-sql-server-a-otro-disco.html