Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Move data to separate partition

Posted on 2013-05-31
10
Medium Priority
?
341 Views
Last Modified: 2013-06-03
I have an SQL server where the data and log files reside on the same partition.  I want to move them to a separate partition.  How do I do this?

I have SQL 2008
0
Comment
Question by:al4629740
  • 7
  • 3
10 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 39211736
There are 2 methods.  Both involve downtime.

1) For each database, "Detach" the database, move the log file to the new partition, then "Re-attach" the database.  When you do, you'll need to specify the new location of the log file.

2) Update the location of the database files and set the log file location to the location where the log file will moved to.  You can do this for all of your databases.  Then shutdown your MS SQL server process, move all of the log files, and bring it back up again.  If you did it correctly, all your databases will be back up and running.


I usually recommend method 1.  Your DBs are only down one at a time and you know right away if you have a problem with a specific database.  You *must* use method 2 for system databases (msdb, model, distribution, and tempdb).  To move the master files requires yet another different method.

I'll post some screenshots and SQL code in a second.
0
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 800 total points
ID: 39211770
Here are some screenshots:

Detach the database (right-click on database): MS SQL Detach Database
Then move the log file.

Then re-attach (right-click on server): MS SQL Attach Database
Make sure to specify the new location of your log file: MS SQL Attach Database - specify alternate location of LOG file
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39211783
And here is the TSQL code to the detach/attach (I prefer to use this instead of the GUI, since I script out all my moves beforehand):

-- Detach the old database
USE [master]
GO
-- If needed: this will kick off any users
-- ALTER DATABASE [nemtest1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master.dbo.sp_detach_db @dbname = N'nemtest1'
GO


-- ----------------
-- Move file....
-- ----------------

-- Re-attach the database at the new location
USE [master]
GO
CREATE DATABASE [nemtest1] ON
( FILENAME = N'C:\Project\MSSQL2012\Data\nemtest.mdf' ),
( FILENAME = N'C:\New\Location\of\log\file\nemtest_log.ldf' )
FOR ATTACH
GO

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 23

Expert Comment

by:nemws1
ID: 39211791
Here's the SQL for method 2.  Looks much simpler, but remember, you have to shut down your SQL server, move the files, then bring it back up.  This could be a significant amount of downtime.  Also, you have to look up the names of your log files (not necessarily databasename_log, as is the case even in my example).

1) Run this for all the database log files you want to move:
ALTER DATABASE [nemtest1]
MODIFY FILE ( NAME = nemtest_log
        , FILENAME = 'C:\New\Location\of\log\file\nemtest_log.ldf'
);
GO

Open in new window


2) Shut DOWN your MS SQL server process
3) Move all your log files (may take a long time)
4) Bring up your MS SQL server
5) Hope you did everything correctly.

To find/check your DATA and LOG file locations, I use this SQL:

USE master
GO
SELECT db.database_id AS id
        , db.name AS 'dbname'
        , type_desc
        , mf.name AS 'filename'
        , physical_name
        , mf.state_desc AS 'fstate'
        , mf.is_read_only AS 'fro'
        , db.state_desc AS 'dbstate'
        , db.is_read_only AS 'dbro'
FROM sys.master_files AS mf
        JOIN sys.databases AS db
                ON mf.database_id = db.database_id
ORDER BY db.name, type_desc DESC
;
GO

Open in new window

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1200 total points
ID: 39211832
You should not use either of those methods, especially detach!


Instead, simply:
    1) notify SQL of the new file location
    2) verify that SQL "knows" the new location
    3) take the db offline
    4) move/copy the files
    5) bring the db back online.


--1)
ALTER DATABASE [your_db_name]
    MODIFY FILE (
        NAME = nemtest_log,
        FILENAME = 'n:\new\full\path\to\file\your_db_name_log.ldf'
    );

--2)
EXEC [your_db_name].dbo.sp_helpfile
-- VERIFY THAT THE FILE LOCATION MATCHES WHAT YOU WANT
--if it doesn't, re-do step 1

--3)
ALTER DATABASE [your_db_name]
    SET OFFLINE

--4) copy/move the files; you can use any method to move the files: Windows, command line, xp_cmdshell, etc..
EXEC master.dbo.xp_cmdshell 'copy "o:\existing\full\path\to\file\your_db_name_log.ldf" "n:\new\full\path\to\file\your_db_name_log.ldf"'

--5)
ALTER DATABASE [your_db_name]
    SET ONLINE

Verify that the new db is working, then delete the original files.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39211843
You don't like the DETACH method?  Works perfectly for me.  Can you explain *why* you don't like it?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39211899
Sure:

1) detach changes the security settings on the file -- often *only* the *specific* person that detached it can reattach it.  If that person has something else come up and can't finish the attach, it can be a royal pita to get the file reattached (w/o doing serious fiddling with file securities, which is not my specialty)

2) if you detach a damaged db, it will almost never reattach, and you've a major problem ... so now you've got to run a DBCC CHECK before the detach, and that can take a long time on a large db



Can you explain *why* you suggest stopping SQL just to move a log file for a selected db(s) to another drive?
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39211922
I know #1 happens (I consider it a bug) and again, didn't think that would be relevant here - assuming the same user would be doing the entire transfer process.

Curious though...  What if I'm connecting using an SQL Server account and not an AD account?  What if my AD account has access to the SQL Server, but has no permissions to the underlying Windows Server?


#2 very true, but I thought it irrelevant in this context.  If you've got a bad database, you've got other problems. ;-)


As for stopping SQL, you are 100% correct.  There is no need to do it.  I didn't realize taking a database individually offline would do the same thing.  I had assumed, incorrectly, that the database file locations were only read at SQL Server startup (or during an attach/detach).  Of course, you would want to stop your database if you're moving any of the system databases (model, msdb, tempdb, and/or distribution - and of course, master, which has other caveats as well).  When I've had to do migrations in the past, this was the case (ie system files had to be moved as well).
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39212010
Quite true: you can't take system dbs offline, so the method wouldn't apply there.

#1 is not a bug -- it's (officially) a "security enhancement".  I've even seen some DBAs that agree with this.  But it can be a royal pain.  You can detach using SSMS on your local machine, remote into the main server box, and ACK, you can't attach the db *you* detached because of security issues ... GRRRR, frustrating!

#2 detach seems to have a problem with ANY error, including on nonclus indexes.  Offline / Online seems more resilient to such issues.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39212069
Awesome. Thank you much for the info!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question