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
Set the database to single user mode and detach it
Transact-SQL1234567891011Use MASTERGO-- Set database to single user modeALTER DATABASE adventureWorksSET SINGLE_USERGO-- Detach the databasesp_detach_db 'AdventureWorks'GO
- Move the files to their new location
Re-attach the database with the files at their new location
Transact-SQL12345678USE masterGO-- Now Attach the databasesp_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