/* Here we're going to move some DB files. So we need to create both the alter DB cmds and the file move cmds. First we need to do the alter stmts then we need to do the file moves. Do these steps for each one of the queries below and run it on the result set. Steps: 1. Run query to create the copy move stmts. 2. Take the results and paste them to a 2nd window. 3. Now to get the filename so you can move it to a new dir: Search for: {:a\:\\(.+\\)}{.+\.(m|n|l)df} 1st query: Replace with: F:\SQLData\\\2 2nd query: Replace with: COPY "\1\2" F:\SQLData\\\2 :a -- the drive letter. \: -- escape and get the colon. \\ -- escape and get the backslash. (.+\\) -- group: any 1 or more chars followed by backslash (escaped) {} -- capture that entire group. this is the folder structure minus the filename. :i+ -- any 1 or more chars. This is the filename. \. -- escape the period. (m|n|l)df -- either mdf, ndf, ldf. In some cases these might be case-sensitive... not sure why. */ --1. select 'ALTER DATABASE [' + DB_NAME(database_id) + '] MODIFY FILE (NAME = ' + NAME + ', FILENAME = ''' + PHYSICAL_NAME + ''')' from sys.master_files where DB_NAME(database_id) like 'SP2010%' --2. SELECT physical_name from sys.master_files where DB_NAME(database_id) like 'SP2010%'