Creating Dynamic T-SQL to Move a SQL Server Database

问题

您有一项任务要从许多单独的T-SQL语句中进行脚本编写。例如，您需要重命名许多SQL Server数据库文件。您需要自动创建T-SQL语句，而不是手动编写解决方案的脚本。你该怎么做？查看此技巧以了解更多信息。

解

您可以使用诸如动态SQL之类的技术来创建大量语句，并将已知表中的参数作为输入传递。这样可以节省大量的键入时间，并显着减少脚本编写中的错误。对于本技巧，我将演示一个示例，在该示例中，可以使用基于集合的（SELECT）条件逻辑智能地生成代码，从而派上用场。

最近，我面临着从其当前形式“ drive：\ folder \ subfolder \ dbname_n.xdf” 重命名70个数据库文件的要求，其中“ n”的范围为1-70，“ xdf”在mdf / ndf / ldf之间变化，共有8个文件组，并且路径根据数据库文件所在的文件组而有所不同。很快，我意识到这是一项艰巨的手动脚本编写任务。用手，我将必须：

Query sys.database_files (name, physical_name) to find the current physical names
Take the database offline
For each file, issue an ALTER DATABASE MODIFY FILE ( FILE = 'name', FILENAME = 'physical_name'); statement
Manually rename each file (i.e. by GUI, or using 'rename x y' syntax in a Command Window)
Bring the database online
Verify the files are in the correct locations

This amounts to at least 140 lines of similar code and much eyeballing of 'before/after' file paths to ensure these are correct. This has a couple of disadvantages - through typos or human error, I could inadvertently point a file record to a physical location that doesn't exist (have you ever done this in production? I have. Scary moment). I could miss one or more files. And, of course, this takes time, even with the magic of copy and paste.

There are a couple of tricks one can use. One of my colleagues uses Excel to auto-fill columns full of SQL commands, but this still takes work when coding individual parameters. The technique below, which I'll examine part-by-part, will show one method of doing it in T-SQL. Here's the build script - a simple CREATE DATABASE statement with the primary filegroup and three tertiary filegroups, and a single log file. For clarity, I've omitted additional options like SIZE, MAXSIZE etc., and the defaults will be used. Replace 'C:\del' with your working directory. You'll need to create the subfolders 'TestDB', 'TestDB\Data' and 'TestDB\Logs':

CREATE DATABASE TestDB 
ON PRIMARY 
 ( NAME = 'TestDB_Primary', FILENAME = 'c:\del\TestDB\Data\Datafile_1.mdf' ),
FILEGROUP FG_1 
 ( NAME = 'TestDB_FG_1_1', FILENAME = 'c:\del\TestDB\Data\Datafile_2.mdf' ), 
 ( NAME = 'TestDB_FG_1_2', FILENAME = 'c:\del\TestDB\Data\Datafile_3.ndf' ),
FILEGROUP FG_2 
 ( NAME = 'TestDB_FG_2_1', FILENAME = 'c:\del\TestDB\Data\Datafile_4.mdf' ), 
 ( NAME = 'TestDB_FG_2_2', FILENAME = 'c:\del\TestDB\Data\Datafile_5.ndf' ),
FILEGROUP FG_3 
 ( NAME = 'TestDB_FG_3_1', FILENAME = 'c:\del\TestDB\Data\Datafile_6.mdf' ), 
 ( NAME = 'TestDB_FG_3_2', FILENAME = 'c:\del\TestDB\Data\Datafile_7.ndf' )
LOG ON 
 ( NAME = 'TestDB_Log',  FILENAME = 'c:\del\TestDB\Logs\TransactionLog.ldf' )
GO

Now, the logical file names are straightforward - each file is named appropriately, and one can look in the folder and see the physical files. However, the physical files are named arbitrarily and this can make it difficult to reconcile each file to the appropriate file group. It's relatively easy to use T-SQL to do this:

USE TestDB 
SELECT  df.name [logical_name], df.physical_name, fg.[name] [filegroup_name]
FROM  sys.database_files df 
INNER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id
ORDER BY fg.data_space_id ASC, df.file_id ASC

However it's neater and consistent with best practices to have meaningful filenames. Here's the crux of the code which will produce the code to rename the physical files to their logical names:

SELECT 'ALTER DATABASE ' + CAST(DB_NAME() AS VARCHAR(50)) + ' MODIFY FILE ( NAME = ' +
  QUOTENAME(df.[name],'''') + ', FILENAME = ''' + 
  SUBSTRING(df.[physical_name], 1, 
  LEN(df.[physical_name]) - CHARINDEX('\',REVERSE(df.[physical_name])) + 1 ) +
  df.[name] + CASE 
      WHEN df.type_desc = 'ROWS' AND df.file_id = 1 THEN '.mdf'' )'
      WHEN df.type_desc = 'LOG' THEN '.ldf'' )'
      WHEN df.type_desc = 'ROWS' AND df.file_id != 1 THEN '.ndf'' )'
     END 
FROM sys.database_files df

Note this code doesn't take into account FILESTREAM and FULLTEXT (pre-2012) types in the type_desc column of sys.database_files. I'm not using these features in the demo, but feel free to amend the CASE block accordingly. I'm also assuming your file_id for the main DB file in the PRIMARY filegroup is 1 - check and amend if using this code for your databases.

When I run the above against my newly-created TestDB, I get:

Thus I have renamed my physical files to match my logical names. I will step through the snippet above line-by-line to explain what's going on:

SELECT 'ALTER DATABASE ' + CAST(DB_NAME() AS VARCHAR(50)) + ' MODIFY FILE ( NAME = ' +

This line gets the current database context (DBNAME()) and casts it into a string concatenation to build the initial part of the string. Given that sys.database_files in the FROM clause is database context-dependent (i.e. different results are returned depending on which context you are in), DB_NAME(), which is also context-dependent, is safe to use.

QUOTENAME(df.[name],'''') + ', FILENAME = ''' +

Two methods of inserting quotes into quoted strings are demonstrated here - QUOTENAME, which enables one to specify two parameters - the expression to quote, and the character to use. It is also possible to specify a quote using a 'quoted quote', i.e. ''', as above.

SUBSTRING(df.[physical_name],1,LEN(df.[physical_name])-CHARINDEX('\',REVERSE(df.[physical_name]))+1)

This is rather complicated. Starting from the innermost expressions, the physical name of the file is reversed and the CHARINDEX string function is used to identify the first occurrence of '\' from the left. This returns a string position for the LAST occurrence of '\' in the physical name (once reverted to normal). This is necessary since CHARINDEX does not enable directional specification (although something might be possible by switching collation?).

Now we have the position of the last '\', we can surmise everything before that (from position 1 to this position) is the drive and path, and everything afterwards is the file name. So now we need to get the substring of the physical path from position 1 to this position to derive the drive and path, with all subfolders. The filename is going to be replaced so is of no use to us and can be discarded. The + 1 at the end simply reads an additional character into the SUBSTRING, the final '\', so we can append the df.[name] value directly without hardcoding another '\'.

If this seems confusing, read through the line, and dry-run through the functions with a pen and paper starting with REVERSE, then CHARINDEX, then LEN and finally SUBSTRING, and you will see how this works regardless of number of subfolders, drive letter or path variations. Note the CHARINDEX return value and SUBSTRING position parameter are out by +/- 1 since the former is 1-indexed, and the latter 0-indexed, which is why the final '\' doesn't appear in the output despite it's position being explicitly specified.

df.[name] + CASE 
    WHEN df.type_desc = 'ROWS' AND df.file_id = 1 THEN '.mdf'' )'
    WHEN df.type_desc = 'LOG' THEN '.ldf'' )' 
    WHEN df.type_desc = 'ROWS' AND df.file_id != 1 THEN '.ndf'' )' 
   END

This block appends the logical file name to the string we are creating (after the aforementioned drive:path), then makes a decision on the file extension based on the attributes of the file in the row. If the file_id is 1, and the type_desc in sys.database_files is 'ROWS', it's likely (note: not guaranteed!) that this is the primary datafile, so it merits extension '.mdf'. Likewise if the type_desc describes the file as 'LOG', it's going to be a transaction log file and gets extension '.ldf'. Finally, if the file_id is NOT equal to 1 and of type_desc 'ROWS', it's named as a '.ndf'. BE CAREFUL - as stated before this example doesn't take into account full-text indexes or filestream attributes. You may wish to amend this conditional logic to deal with this.

FROM sys.database_files df

This is the source of the datafile information, and is a context-dependent view. If you wish to work from another DB context (e.g. master), you can specify the database name prior to 'sys' but you must hard-code the database name in line 1 rather than using DB_NAME().

Next, we need to create the rename script. This is simpler:

SELECT 'rename ' + df.[physical_name] + ' ' + df.[name] + 
  CASE 
   WHEN df.type_desc = 'ROWS' AND df.file_id = 1 THEN '.mdf'
   WHEN df.type_desc = 'LOG' THEN '.ldf'
   WHEN df.type_desc = 'ROWS' AND df.file_id != 1 THEN '.ndf'
  END 
FROM sys.database_files df

It builds the rename string dynamically, and uses identical CASE logic to our first code block.

Finally, we need to set the database offline; execute the first code block and copy/paste the output to a new window; execute the output from our first code block; execute the second code block and copy/paste the output to a new batch file; execute the output from our second code block in a command window; and bring the database back online, like so: