Move SQL Server 2008 database files to a new folder location

Question

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

User · Answer

You can use Detach Attach Option in SQL Server Management Studio   Check this  Move a Database Using Detach and Attach

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server-2008