Darren Myher's Blog

This weekend I was retiring an old server, replacing it with a new server.  The old server had 3 different SQL instances with hundreds of mounted databases.  To move them all to the new server (and get them re-mounted again) I figured there had to be some SQL Scripts already written for the task and after a brief search I found an excellent article with exactly the scripts that I needed here:

https://www.mssqltips.com/sqlservertip/1786/auto-generate-sql-server-database-attach-and-detach-scripts/ 

The important parts from that article were the script generator to detach the databases:

-- Build the sp_detach_db command (ONLINE, non-system databases only):
SELECT DISTINCT 'exec sp_detach_db ''' + DB_NAME(dbid) + ''';'
FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('E','F')
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
GO

And the script generator to subsequently mount the databases (again, 100% of the credit for this script goes to Michelle Gutzait):

— Build the sp_attach_db:

SET NOCOUNT ON
DECLARE     @cmd        VARCHAR(MAX),
            @dbname     VARCHAR(200),
            @prevdbname VARCHAR(200)

SELECT @cmd = ”, @dbname = ‘;’, @prevdbname = ”

CREATE TABLE #Attach
    (Seq        INT IDENTITY(1,1) PRIMARY KEY,
     dbname     SYSNAME NULL,
     fileid     INT NULL,
     filename   VARCHAR(1000) NULL,
     TxtAttach  VARCHAR(MAX) NULL
)

INSERT INTO #Attach
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid, filename, CONVERT(VARCHAR(MAX),”) AS TxtAttach
FROM master.dbo.sysaltfiles
WHERE dbid IN (SELECT dbid FROM master.dbo.sysaltfiles
            WHERE SUBSTRING(filename,1,1) IN (‘E’,’F’))
            AND DATABASEPROPERTYEX( DB_NAME(dbid) , ‘Status’ ) = ‘ONLINE’
            AND DB_NAME(dbid) NOT IN (‘master’,’tempdb’,’msdb’,’model’)
ORDER BY dbname, fileid, filename

UPDATE #Attach
SET @cmd = TxtAttach = 
            CASE WHEN dbname <> @prevdbname
            THEN CONVERT(VARCHAR(200),’exec sp_attach_db @dbname = N”’ + dbname + ””)
            ELSE @cmd
            END +’,@filename’ + CONVERT(VARCHAR(10),fileid) + ‘=N”’ + filename +””,
    @prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END,
    @dbname = dbname
FROM #Attach  WITH (INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)

SELECT TxtAttach
FROM
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach
GROUP BY dbname) AS x

DROP TABLE #Attach
GO

I noticed however, that many of the files I had attached had massive sql .ldf (log) files.  Since none of my databases needed to run with full logging, I could go ahead and shrink the log files without any issue.  Shrinking the log files had the effect of reducing the data to move from server A to B by nearly 1TB.  Here’s the script, courtesy of: http://dba.stackexchange.com/questions/358/how-do-i-shrink-all-files-quickly-for-all-databases

SELECT
      ‘USE [‘ + d.name + N’]’ + CHAR(13) + CHAR(10)
    + ‘DBCC SHRINKFILE (N”’ + mf.name + N”’ , 0, TRUNCATEONLY)’
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM
         sys.master_files mf
    JOIN sys.databases d
        ON mf.database_id = d.database_id
WHERE d.database_id > 4;

This generates the shrink commands using the syntax:

USE [MountedDatabaseName]
DBCC SHRINKFILE (N’Demo Data SQL Master_dat’ , 0, TRUNCATEONLY)

The thing that’s a bit strange to me at least about the SHRKINFILE command is that it uses the internal database name instead of the mounted name.  So having a script generator like this one to create the script is invaluable!

The last thing that most of these articles do not mention is that if other users have detached databases in the folder structure that you’re trying to move, then their user accounts (or the SQL Service account if they were using SQL Auth) will then have exclusive security permissions to the detached .mdf/.ndf/.ldf files.

That means you will hit file system permission issues, so the way that you solve that is using an admin account, run the following commands from a command prompt:

First, change to the root folder where your databases are mounted on the old server, then:

1. Use the TakeOwn command to take ownershp of all of the files from the current folder (dot) and sub-folders /R:

TakeOwn /F . /R

2. Use the cacls command to grant permission to the physical files:

cacls * /G Everyone:F /T

Note: in my case  just granted “everyone” full permission to the SQL Database files, because when I move these files to the destination server the destination server’s folder permissions are going to impose new restrictions.