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.
- Use the following commands to get the names and location of the data and transaction log files
USE AdventureWorks GO sp_helpfile GO
-
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
- Move the files to their new location
-
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
Average Rating