Post reply

Moving available space from one drive to another on same server

  • March 9, 2016 at 7:59 am

    #311973

    HI

    I want to Move an available space of about 500GB from one drive to another on same server .

    My database is on drive F (Volume: sqlData), but it is only 37GB and it is running out of space. I have a 600 GB available size as drive K (Volume: sqlData1) .

    What is the easiest way to move about 500 GB from K to F?

    Could you tell me the SQL commands and the steps

    thanks

  • March 9, 2016 at 9:13 am

    #1864031

    Is your database 37GB in size or the drive its currently hosted on only 37GB?

    You can take your database offline, move the physical files and bring the database back online using these steps:

    https://msdn.microsoft.com/en-us/library/ms345483.aspx

    MCITP SQL 2005, MCSA SQL 2012

  • March 9, 2016 at 10:42 am

    #1864066

    My drive is about 500GB but the available size is 37gb

  • March 9, 2016 at 11:42 am

    #1864081

    is it possible to use the DETACH

  • March 9, 2016 at 12:12 pm

    #1864085

    Do you want to move space on a volume, or do you want to move the database from one drive to another?

    If you want to move space on a volume, there are a lot more questions that need to be asked. This is likely left to an administrator

    If you want to move the database, you can detach the files, move them to the new drive, and re-attach them.

    To detach:

    USE [master]

    GO

    ALTER DATABASE [Your Database Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    USE [master]

    GO

    EXEC master.dbo.sp_detach_db @dbname = N'Your Database Name'

    GO

    To attach(You will need to modify this for your environment):

    USE [master]

    GO

    CREATE DATABASE [Your Database Name] ON

    ( FILENAME = N'C:\SQLData\Your Database Name_data.mdf' ),

    ( FILENAME = N'C:\SQLLogs\Your Database Name_log.ldf' )

    FOR ATTACH

    GO

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • March 9, 2016 at 4:47 pm

    #1864147

    Move User Databases

    SET OFFLINE with rollback immediate

    Physically copy files

    ALTER DATABASE [DB] MODIFY FILE ( NAME = X, FILENAME = Y)

    SET ONLINE

    Verify

    Physically delete original file

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply