How To Move SQL Server Data and Transaction Log Files
Share
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
Author
Stewart Schatz
More Stories
Windows God Mode… What!?!
Windows God Mode is a hidden feature in the Windows operating system that allows users to access all of the system’s control panel options and settings in a single place.
How To Test A SQL Server Connection
There is an easy way to test your SQL Server connection when running Windows without any special software. I found...
4 Lines To Export Outlook Rules To Excel/CSV Using PowerShell
I used to use a ton of Outlook Rules to organize the thousands of emails that I receive each day....
Get The Size & Record Count Of All Tables In A SQL Server Database
Use the SQL query below to display the size and record count of all tables in an SQL Server database....
How To Get AS400/iSeries Job Queue Information Using SQL
I received a request the other day about the JD Edwards EnterpriseOne job queues and what the maximum number of...
The EnterpriseOne CNC Job Description
The JD Edwards ERP Specialist is responsible for maintaining the JD Edwards EnterpriseOne platform and auxiliary systems from a development, CNC, system administration, and functional consultant perspective to create and maintain business solutions.
Average Rating