2

We have a SQL Server with about 50 databases. Of those databases, about 25 of them need to have their .mdf and .ldf files transferred from drive D: to drive E:. It is critical that the database names all remain the same.

What is the best, safest way to do this in an automated fashion?

My initial idea was to use a sort of pseudo ForEach Loop, supply a list of database names, and have it loop through queries to backup and restore or detach and re-attach the databases, but I have not been able to properly insert the database name variables into SQL queries within a loop.

4
  • 1
    What issues are you currently facing? There are likely hundreds of ways to do this. Jun 12, 2019 at 13:08
  • My issue is that the methods I know of all involve either manually detaching and re-attaching every database via the GUI or individual SQL queries with database names manually typed/pasted into them, or manually backing up and restoring every database via the GUI or individual SQL queries with database names manually typed/pasted into them. I am looking for a method that will allow me to at least detach/backup all of the desired databases in one go, then re-attach/restore all of the desired databases in one go. Jun 12, 2019 at 13:17
  • 1
    So do you want a script that generates an ALTER DATABASE ... MODIFY FILE script for each and every db file that's currently on the D: drive? You don't need to attach/detach at all - if you issue your ALTER statement, shut the instance down, move the files then bring the instance back up it should have the same effect. Jun 12, 2019 at 13:18
  • Yes, that is one of the methods I was considering. My issue is that I am having trouble getting the ALTER DATABASE queries to accept variable for things like the database name parameter, or the filename parameter. Such as (Swapped $ for the 'at' symbol, as StackExchange thinks I'm mentioning someone): USE [master]; GO ALTER DATABASE $DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE $DBname MODIFY FILE (name = N'$DBname_log', [filename] = N'E:\SQL\Log\$DBname_log.ldf'); ALTER DATABASE dbname SET OFFLINE; Jun 12, 2019 at 13:22

2 Answers 2

4

Something as simple as the below can construct your script

SELECT
'ALTER DATABASE ' + QUOTENAME(d.name) + ' MODIFY FILE (name=N'''+[f].[name]+ ''', filename = N''' + REPLACE(f.[physical_name],'G:','E:') + ''')'

FROM sys.databases d
    INNER JOIN sys.[master_files] f ON d.[database_id] = f.[database_id]
WHERE d.[database_id] > 4

You can then take the output and save it, or you could potentially wrap it further to turn it into dynamic SQL to actually run it

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'';

SELECT @SQL +=
'ALTER DATABASE ' + QUOTENAME(d.name) + ' MODIFY FILE (name=N'''+[f].[name]+ ''', filename = N''' + REPLACE(f.[physical_name],'G:','E:') + ''')'

FROM sys.databases d
    INNER JOIN sys.[master_files] f ON d.[database_id] = f.[database_id]
WHERE d.[database_id] > 4

PRINT @SQL
--EXEC (@SQL)

Note the use of REPLACE to deal with the change of file location

For additional parameters and commands the above script can be edited easily to include things such as setting the database to single_user or offline.

Following this the SQL Server should be brought offline, the files moved in the file system, and the server brought back online.

3
  • I think this is close.I get every combination of Database Name, Files, name, and File path instead of just a single query per database. Instead of only getting consistent queries like: ALTER DATABASE [APPSERVER1_Table1] MODIFY FILE (name=N'APPSERVER1_Table1', filename = N'E:\SQL\Data\APPSERVER1_Table1.mdf') ALTER DATABASE [APPSERVER2_Table2] MODIFY FILE (name=N'APPSERVER2_Table2', filename = N'E:\SQL\Data\APPSERVER2_Table2.mdf') I also get weird combos: ALTER DATABASE [APPSERVER1_Table1] MODIFY FILE (name=N'APPSERVER2_Table2', filename = N'E:\SQL\Data\APPSERVER2_Table2.mdf') Jun 12, 2019 at 19:03
  • 1
    I've added a fix for the mixed results - if you want them as one alter statement I'll write that up for you tomorrow - it's worth saying that there isn't a need to do that in terms of script functionality though. Jun 12, 2019 at 21:03
  • Thanks for your ideas! I sort of took your general concept and ran with it. Jun 13, 2019 at 13:35
0

Following George's advice, I decided to script out the creation of the necessary queiries so that they can be copied and pasted into SQL and more or less run in one shot.

It is probably just my poor skills in T-SQL, but I found PowerShell much better suited to this sort of task.

What I did:

  1. Generated a list of all databases, thier file names, and file paths using T-SQL.
 SELECT NAME,
       physical_Name,
       database_id,
       FILE_ID,
       type_desc,
       state_desc,
       SIZE * 8 / 1024 AS SizeMB
    FROM   sys.master_files
  1. Created a CSV in Excel with columns for Name, Path, and Type. I populated the Name field with the NAME name, the Path CSV feild with the physical_name data, and the Type column with the type_desc data.

  2. Wrote a PowerShell script to ingest the CSV data, then loop through each row generating the needed scripts. I generated scripts to take databases offline, update the mdf file paths, update the ldf file paths, and then to bring the databases back online. Obviously, you'll want to update the file paths below to what your desired new locations for the ldf and mdf files will be. I separated the ldf files from the mdf files, as our ldf files have an appended '_log' portion of the text which I needed to trim out to reference the correct database names in the ALTER DATABASE queries. I then needed to go back to some of the quires for System ldf files, as they are named MODEL, MSDB, and TEMP, but the log files have different names. 3-4 manual edits is not too bad...

    $DBList = Import-CSV -Path 'C:\PATH\DBs.csv'
    $PathOffline = 'C:\PATHS\QLMTOffline.txt'
    $PathOnline = 'C:\PATHS\QLMTOnline.txt'
    $PathAlterDB = 'C:\PATH\SQLMTAlterDB.txt'
    $PathAlterLOG = 'C:\PATH\SQLMTAlterLOG.txt'

    ForEach ($DB in $DBList)
    {
    $DatabaseName = $DB.Name

    $DatabaseTrimmed = $DatabaseName.TrimEnd('_log')

    Add-Content -Path $PathOffline -Value $SetOffline
    Add-Content -Path $PathOffline -Value "GO"

        IF ($DB.Type -like "ROWS")
        {
        $SetOffline = ""
        $SetOffline = "ALTER DATABASE "+$DatabaseTrimmed+" SET OFFLINE WITH ROLLBACK IMMEDIATE"
        Add-Content -Path $PathOffline -Value $SetOffline
        Add-Content -Path $PathOffline -Value "GO"

        $SetOnline = ""
        $SetOnline = "ALTER DATABASE "+$DatabaseTrimmed+" SET ONLINE WITH ROLLBACK IMMEDIATE"
        Add-Content -Path $PathOnline -Value $SetOnline
        Add-Content -Path $PathOnline -Value "GO"


        $MDFPATH = ""
        $MDFPATH = "'"+"F:\PATH\Data\"+"$DatabaseName"+".mdf"+"'"
        $AlterDB = ""
        $AlterDB = "ALTER DATABASE "+"$DatabaseTrimmed"+" MODIFY FILE ( NAME = "+"$DatabaseName"+" , FILENAME = "+"$MDFPATH"+")"  
        Add-Content -Path $PathAlterDB -Value $AlterDB 
        Add-Content -Path $PathAlterDB -Value "GO"
        }
            ElseIF ($DB.Type -like "LOG")
            {
            $LDFPATH = ""
            $LDFPATH = "'"+"F:\PATH\Log\"+"$DatabaseName"+".ldf"+"'"
            $AlterLOG = ""
            $AlterLOG = "ALTER DATABASE "+"$DatabaseTrimmed"+" MODIFY FILE ( NAME = "+"$DatabaseName"+" , FILENAME = "+"$LDFPATH"+")"   
            Add-Content -Path $PathAlterLOG -Value $AlterLOG
            Add-Content -Path $PathAlterLOG -Value "GO" 
            }
    }
  1. Finally, I

I. Ran the modify mdf queries

II.Ran the modify ldf queries,

III. Ran the 'take offline' queries

IV. Physically moved the ldf and mdf files

V. Ran the 'take online' queries

Everything came back online buitifully, and the whole process took about 45 minutes as opposed to the hours of manually backing up and restoring individual databases. I hope this helps some poor soul someday.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.