Follow sqlserver-dba.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

SQLServer-DBA.com Links

Dba_db2_button

dba-ninja.com

SQL Server DBA Jobs

SQL Server - Move MDF/LDF files to a designated path

19 September,2008 by Tom Collins

Even though I'd prefer to Backup a database and then move , often a third party supplier
will need it as a MDF or LDF , which they then will have  a script to reattach .
The main steps are :

Detach DB
Move to designated path
ReAttach DB
2 input parameters: Name of DB on SAN, targeted path

The main issue to consider is permissions on network to actually transport the files .

The 'xp_cmdshell' is used (which I don't often recommend , but it does serve its purpose on this script

--------------------CODE START----------------------------------
     DECLARE  @DB_NAME NVARCHAR(128);
     DECLARE @FILE_PATH NVARCHAR(256);
     SET   @DB_NAME = 'MY_DB'
     SET   @FILE_PATH = 'MY_FILE_PATH'
     
      --variables
       DECLARE @SQL_A VARCHAR(300); 
       DECLARE @SQL_B VARCHAR(300); 
       DECLARE @SQL_C VARCHAR(300); 
       DECLARE @SQL_D VARCHAR(300); 
       DECLARE @SQL_E VARCHAR(2000); 
       DECLARE @DATA_FILE VARCHAR(600); 
       DECLARE @LOG_FILE VARCHAR(600); 
       DECLARE @MDF_FILE NVARCHAR(256);
       DECLARE @LDF_FILE NVARCHAR(256);
       DECLARE @FILENAME_TBL TABLE(fileType INT, FilePath NVARCHAR(256));
       --establish physical path and filename of MDF and LDF files
       SET @SQL_A = 'SELECT 1,filename from '+@DB_NAME+'.sys.sysfiles WHERE fileid = 1';
       SET @SQL_B = 'SELECT 2,filename from '+@DB_NAME+'.sys.sysfiles WHERE fileid = 2';
       
       INSERT INTO @FILENAME_TBL EXEC (@SQL_A);
    INSERT INTO @FILENAME_TBL EXEC (@SQL_B);
       SET @MDF_FILE = (SELECT FilePath FROM  @FILENAME_TBL WHERE fileType=1);
       SET @LDF_FILE = (SELECT FilePath FROM  @FILENAME_TBL WHERE fileType=2);
     
  
       ---- DETACH DB
       PRINT 'Detach DB : START';
        --set the DB to SINGLE USER
    SET @SQL_C = 'ALTER DATABASE ' + @DB_NAME + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;';
    EXEC (@SQL_C);
      
       EXEC sp_detach_db @DB_NAME,'true'
       PRINT 'Detach DB : FINISH';
       

        --MOVE MDF file
        PRINT 'MOVE MDF & LDF : START';
        DECLARE @dataFile1Cmd VARCHAR(200),
     @dataFile2Cmd VARCHAR(200)
        
        SET  @dataFile1Cmd = 'copy "'+@MDF_FILE + '" "' +  @FILE_PATH + '"';
        
  SET  @dataFile2Cmd = 'copy "'+@LDF_FILE + '" "' +  @FILE_PATH + '"';
        EXEC master..xp_cmdshell @dataFile1Cmd
        EXEC master..xp_cmdshell @dataFile2Cmd
        PRINT 'MOVE MDF & LDF : FINISH';
     
      --REATTACH DB 
        PRINT 'Attach DB : START';
        SET @SQL_E = 'EXEC sp_attach_db @dbname = N'''+@DB_NAME +''',@filename1 = N'''+@MDF_FILE+''',@filename2 = N'''+@LDF_FILE+'''';
        PRINT @SQL_E;
        EXEC (@SQL_E);
        SET @SQL_D = 'ALTER DATABASE ['+@DB_NAME+'] SET MULTI_USER WITH ROLLBACK IMMEDIATE';
        EXEC (@SQL_D);
        PRINT 'Attach DB : FINISH';
--------------------CODE END----------------------------------

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - Move MDF/LDF files to a designated path


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer