Move SQL Server 2008 database files to a new folder location

Logical Name

• my_Data
• my_Log

Path:

• C:\Program Files\Microsoft SQL S开发者_如何学JAVAerver\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.

---

You forgot to mention the name of your database (is it "my"?).

ALTER DATABASE my SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE my SET OFFLINE;

ALTER DATABASE my MODIFY FILE 
(
   Name = my_Data,
   Filename = 'D:\DATA\my.MDF'
);

ALTER DATABASE my MODIFY FILE 
(
   Name = my_Log, 
   Filename = 'D:\DATA\my_1.LDF'
);

Now here you must manually move the files from their current location to D:\Data\ (and remember to rename them manually if you changed them in the MODIFY FILE command) ... then you can bring the database back online:

ALTER DATABASE my SET ONLINE;

ALTER DATABASE my SET MULTI_USER;

This assumes that the SQL Server service account has sufficient privileges on the D:\Data\ folder. If not you will receive errors at the SET ONLINE command.

---

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

Check this: Move a Database Using Detach and Attach

---

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.

---

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