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
How to Print a Directory Structure Using PowerShell: Step-by-Step Guide
If you need to print out the directory structure and list the files within, this PowerShell script will help you...
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...
Average Rating