How To Move SQL Server Data and Transaction Log Files

Read Time:55 Sec

I’ve been migrating data from an IBM AS/400 to a SQL Server a lot lately. Within the next year or so we should have completed migrating 3 more instances. Then, we will have all 7 instances on SQL Server.

In preparing for the final data migration of one of the instances, I had to move the data and transaction log files. I found a great article for this on MSSQLTips.

Below are the basic steps.

  1. Use the following commands to get the names and location of the data and transaction log files
    USE AdventureWorks
    GO
    
    sp_helpfile
    GO
    
  2. Set the database to single user mode and detach it
    Use MASTER
    GO
    
    -- Set database to single user mode
    ALTER DATABASE adventureWorks
    SET SINGLE_USER
    GO
    
    -- Detach the database
    sp_detach_db 'AdventureWorks'
    GO
    
  3. Move the files to their new location
  4. Re-attach the database with the files at their new location
    USE master
    GO
    
    -- Now Attach the database
    sp_attach_DB 'AdventureWorks',
    'D:\Program Files\Microsoft SQL Server\MSSQL\Data\AdventureWorks_Data.mdf',
    'E:\Move LogFile here through T-SQL\AdventureWorks_Log.ldf'
    GO
    

Author

Stewart Schatz

Career: Principal CNC Consultant for Syntax Systems Limited specializing Oracle JD Edwards EnterpriseOne and the technology that supports it. Side Hustle: Owner/Operator of E1Tips.com Location: Lancaster, PA USA  What I like to do: Invest in Family, Explore Technology, Lead Teams, Share Knowledge/Experience, Hunt, Hike, etc.
Happy
Happy
0
Sad
Sad
0
Excited
Excited
0
Sleepy
Sleepy
0
Angry
Angry
0
Surprise
Surprise
0

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Previous post Recover Lost Disk Space
Next post EnterpriseOne Business Services (BSSV) Error: Security Token Failed To Validate