Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Unanswered: Drive Filling Up / Moving Data Files Help

    I have a database [CarlosDB] that currently has it's .MDF on E:\ and I need to move the x2 .NDF data files off C:\ to E:\data using a single T-SQL statement:

    Code:
    database_id file_id     db_name                                                                                                                          disk_path                                                                                                                                                                                                                                                        status                                                       size        read_only
    ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ----------- ---------
    7           1           CarlosDB                                                                                                                         E:\data\CarlosDB.mdf                                                                                                                                                                                                                                             ONLINE                                                       384         0
    7           2           CarlosDB_log                                                                                                                     L:\logs\CarlosDB_log.ldf                                                                                                                                                                                                                                         ONLINE                                                       128         0
    7           3           CarlosDB_2                                                                                                                       C:\sql\CarlosDB_2.ndf                                                                                                                                                                                                                                            ONLINE                                                       128         0
    7           4           CarlosDB_3                                                                                                                       C:\sql\CarlosDB_3.ndf                                                                                                                                                                                                                                            ONLINE                                                       128         0
    
    (4 row(s) affected)
    Looking at the file configuration above, what would be the most logical way as a DBA / SQL Server 2014 Std to move the NDF files to live w/ the MDF file using:

    Code:
    EXEC master.dbo.xp_cmdshell 'copy c:\sql\CarlosDB_2.ndf e:\data\CarlosDB_2/ndf'...
    but cleanly using a single T-SQL statement?

    Thanks for any help in properly formatting a single T-SQL query to use the xp_cmdshell system stored procedure.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure where the single command requirement is coming from. I usually do this in about 4 commands, if you include the OS command. Run the following:

    Code:
    alter database [CarlosDB] modify file
      (name = 'CarlosDB_2',
       filename = 'e:\data\CarlosDB_2.ndf') -- This does not affect the database, until its next startup
    
    alter database [CarlosDB] set offline with rollback immediate -- This does affect the database.
    
    --copy the datafile to the new location.  This can be done with xp_cmdshell.
    
    alter database [CarlosDB] set online
    There are a number of internal pointers that SQL Server has to update, so just copying the file won't do it.

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    I don't need this in a single command as I don't think that's actually possible or logical but more that I need to declare or build out the x2 ALTER DATABASE statements below so that it's portable to any system. Basically a way to query the master files, locate the files on C:\ and then use the xp_cmdshell command to move them.

    Code:
    USE [CarlosDB]
    GO
    
    ALTER DATABASE [CarlosDB] modify file
      (
      	name = 'CarlosDB_2',
        filename = 'd:\data\CarlosDB_2.ndf'
      ) 
    GO
    
    ALTER DATABASE [CarlosDB] modify file
      (
      	 name = 'CarlosDB_3'
      	 filename = 'd:\data\CarlosDB_3.ndf'
      )
    GO
    
    ALTER DATABASE [CarlosDB] SET offline with ROLLBACK IMMEDIATE
    GO
    
    EXEC master.dbo.xp_cmdshell 'copy c:\sql\CarlosDB_2.ndf d:\data\CarlosDB_2.ndf'
    EXEC master.dbo.xp_cmdshell 'copy c:\sql\CarlosDB_3.ndf d:\data\CarlosDB_3.ndf'
    
    ALTER DATABASE [CarlosDB] SET online 
    GO
    From above, I need a way to build out the section of the x2 ALTER DATABASE [] modify file.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    ALTER DATABASE does not take variables, if I recall, so you would have to wrap everything up in an EXEC statement. Error checking with those could get kind of hard.

  5. #5
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Thank you!

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd encourage you to look at PowerShell. I think that it would be a great fit for this problem!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •