About Me

My Photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. Presently he is an independent SQL Server consultant with his own firm, CoCreative Solutions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group since January 2009.

Sunday, November 2, 2014

Script for Quickly Moving All Database Files on a SQL Instance

Sometimes there’s a need to move the underlying files for the databases on a SQL instance. 

Scenario
You have all your data files on the “E drive” which is really a RAID.  It has 500 Gbytes available which is plenty.  Months later, the E drive becomes full.  You request more disk.  Some time later your storage engineer gives you a new dedicated  RAID which has been assigned a new drive letter, “S “.  This S volume now has 2 Terabytes of space.

The task at hand is to move some of the databases from the E drive to the S drive.  This is a production server so you want to minimize downtime. 

Strategy
1.      Verify original file locations.
2.       Make a database backup for safety.
3.       Kill any active spids.
4.       Detach files.
5.       Copy files to new location.
6.       Attach files.
7.       Verify database is backed up.
8.       Delete files in original location.

This proc will create T-SQL and some command code for doing all the above.

To create calls for all databases.

SELECT 'Exec MoveDB ''' + name + ''', ''S:\Sql\Data'', ''S:\Sql\Data'', ''S:\Sql\Bak'''
FROM sys.databases           
ORDER BY name          

I put his proc in the Admin database with my other code snippets.

USE Admin

If OBJECT_ID('dbo.MoveDB') Is Not Null
      DROP PROC dbo.MoveDB
go

CREATE PROC [dbo].[MoveDB]
      @DB varchar(100),
      @DestDataPath varchar(300),
      @DestLogPath varchar(300),
      @BackupPath varchar(300) = 'BackupPathHere'
AS
SET NOCOUNT ON
/*    DATE        AUTHOR            REMARKS
      11/1/14           PPaiva            Initial creation.
     
      DESCRIPTION
            Produces scripts for moving a database's files via
                  sp_detach and CREATE DATABASE FOR ATTACH.
            Also produces clean-up scripts for deleting
                  the original files, for use after database
                  has been successfully moved.
                 

      USAGE
            MoveDB 'Admin', 'E:\Sql\Data', 'F:\Sql\Logs'
            MoveDB 'Sopa', 'E:\Sql\Data', 'F:\Sql\Logs'
                 
      DEBUG
            -- Location of database files
            SELECT DB_NAME(database_id), file_id, type_desc, physical_name, database_id
            FROM master.sys.master_files
            ORDER BY DB_NAME(database_id), file_id
           
            -- Generate Exec scripts for all databases
            SELECT 'Exec MoveDB ''' + name + ''', ''S:\Sql\Data'', ''S:\Sql\Data'', ''S:\Sql\Bak'''
            FROM sys.databases           
            ORDER BY name          
     
*/
DECLARE @s varchar(max),
            @sAttach varchar(max) = '',
            @sCmdLine varchar(max) = '',
            @sCmdLineDel varchar(max) = '',
            @sTSql varchar(max) = '',
            @sTSqlDel varchar(max) = '',
            @crlf varchar(2) = Char(13) + Char(10)


SET @s = '-- *******************************************
--  Script for moving database ' + @DB + '
-- *******************************************'
Print @s
Print ''

SET @s = 'SELECT file_id, type_desc, physical_name, *
FROM master.sys.master_files
WHERE DB_NAME(database_id) = ''' + @DB + ''''

Print '-- Verify original file locations'
Print @s
Print ''

Print '-- Make a backup for safety'
SET @s = 'Exec Admin.dbo.BackupDB ''' + @DB + ''', ''' + @BackupPath + ''', ''DT'', 1'
Print @s
Print ''


-- Kill any active connections
Print '-- Kill any active spids'
SET @s = 'ALTER DATABASE [' + @DB + ']
      SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [' + @DB + ']
      SET ONLINE'
Print @s   
Print ''


Print '-- Detach'
SET @s = 'Exec master.dbo.sp_detach_db @dbname = [' + @DB + ']'
Print @s
Print ''

-- Copy files
SELECT file_id, type_desc, physical_name
INTO #Files
FROM master.sys.master_files
WHERE DB_NAME(database_id) = @DB

DECLARE @MaxFileID int,
            @FileID int = 0,
            @ExistingPath varchar(300),
            @Usage varchar(20),
            @FileNameOnly varchar(100),
            @NumFiles int,
            @i int = 0

SELECT  @MaxFileID = MAX(file_id),
            @NumFiles = COUNT(*)
FROM #Files

SET @sAttach = 'CREATE DATABASE [' + @DB + '] ON '


WHILE @FileID < @MaxFileID
      BEGIN
            SET @i += 1
           
            -- Get next file
            SELECT @FileID = Min(FILE_ID)
            FROM #Files
            WHERE FILE_ID > @FileID
           
            -- Get other attributes
            SELECT  @ExistingPath = physical_name,
                        @Usage = type_desc,
                        @FileNameOnly = dbo.udfGetFilenameAndExtOnly(physical_name)
            FROM #Files
            WHERE FILE_ID = @FileID
           
           
     
            SET @sCmdLine += 'Copy "' + @ExistingPath + '" "'
            SET @sTSql += 'Exec xp_cmdshell ''Copy "' + @ExistingPath + '" "'

            SET @sCmdLineDel += 'Del "' + @ExistingPath  + '"'         
            SET @sTSqlDel += 'Exec xp_cmdshell ''Del "' + @ExistingPath + '"'''          
           
            IF @Usage = 'Rows'
                  BEGIN
                        SET @sCmdLine += @DestDataPath + '"'                       
                        SET @sTSql += @DestDataPath + '"'''                  
                  END
            ELSE
                  BEGIN
                        SET @sCmdLine += @DestLogPath + '"'
                        SET @sTSql += @DestLogPath + '"'''
                  END
     
            SET @sCmdLine += @crlf
            SET @sCmdLineDel += @crlf    
            SET @sTSql += @crlf          
            SET @sTSqlDel += @crlf


            -- Attach
            IF @i > 1
                  SET @sAttach += ', '


            IF @Usage = 'Rows'
                  BEGIN                  
                 
                        SET @sAttach = @sAttach + '
      (FILENAME = ''' +  dbo.udfAddBackslashIfNec(@DestDataPath) +  @FileNameOnly + ''')'
           
                  END
                 
            ELSE
                  SET @sAttach = @sAttach + '
      (FILENAME = ''' +  dbo.udfAddBackslashIfNec(@DestLogPath) +  @FileNameOnly + ''')'
                 
     
     
      END

      SET @sAttach += '
      FOR ATTACH'


Print '-- Command line copy'
Print @sCmdLine

Print ''
Print '-- T-SQL code'
Print @sTSql

Print ''
Print '-- Attach'
Print @sAttach


Print ''
Print '-- Command line DELETE original files'
Print '-- **** Only run this after database has been attached!!  ****'
Print @sCmdLineDel

Print ''
Print '-- T-SQL DELETE original files'
Print '-- **** Only run this after database has been attached!!  ****'
Print @sTSqlDel
Print '-- DONE'
Print ''
Print ''


Wednesday, October 29, 2014

Example: Views can contain old column names

-- Demonstration that a view retains the old column names
-- after renaming a column, or adding a column
CREATE TABLE  Test1 (
      ID int,
      Name varchar(20)
)

INSERT INTO Test1 values (1, 'Hello')
INSERT INTO Test1 values (2, 'Good Bye')

SELECT *
FROM Test1

go
CREATE VIEW vTest1
AS
      SELECT *
      FROM Test1

go
SELECT *
FROM vTest1

EXEC sp_rename 'Test1.Name', 'NNName', 'COLUMN'

SELECT *
FROM Test1

SELECT *
FROM vTest1

alter table Test1 add MyNewCol varchar(20)

SELECT *
FROM Test1

SELECT *
FROM vTest1

DROP VIEW vTest1
DROP TABLE Test1



Tuesday, September 9, 2014

udfGetColumnList( )

USE Admin

IF OBJECT_ID('dbo.udfGetColumnList') Is Not Null
      DROP FUNCTION dbo.udfGetColumnList
go

CREATE FUNCTION dbo.udfGetColumnList(
      @ObjName sysname,
      @UseBrackets bit
      )
RETURNS varchar(max)
AS
/*    DATE        AUTHOR            REMARKS
      9/9/14            PPaiva                  Initial creation.

      DESCRIPTION
            Returns a comma-separated list of columns based
                  on @ObjName from vwDictCol.

      DEPENDENCY
            vwDictCol

      USAGE
            SELECT dbo.udfGetColumnList('Employee', 1)
            SELECT * FROM vwDictCol where ObjName = 'Employee'

            SELECT dbo.udfGetColumnList(TableName, 1) ColList,
                        *
            FROM vwRowCount


      DEBUG
            SELECT *
            FROM vwDictCol
           

*/
BEGIN
      DECLARE @t TABLE( ColID int,
                                    ColName sysname
                                    )

      DECLARE @MaxColID int,
                  @ColID int = 0,
                  @ColName sysname,
                  @Out varchar(max) = ''
     
     
      INSERT INTO @t
            SELECT ColID, ColName
            FROM vwDictCol
            WHERE ObjName = @ObjName
            ORDER BY 1


      SELECT  @MaxColID = Max(ColID)                 
      FROM @t

      WHILE @ColID < @MaxColID
            BEGIN
                  -- Get next ColID
                  SELECT @ColID = Min(ColID)
                  FROM @t
                  WHERE ColID > @ColID

                  -- Get ColName
                  SELECT @ColName = ColName
                  FROM @t
                  WHERE ColID = @ColID


                  IF @UseBrackets = 1
                        SET @ColName = '[' + @ColName + ']'

                  SET @Out += @ColName

                  IF @ColID <> @MaxColID
                        SET @Out += ', '
                       
            END


      RETURN @Out

END



Friday, August 22, 2014

udfGetFilenameAndExtOnly

Useful function for returning only the filename with its extension.  That is, the path is not shown.

This is a dependent object for some other objects on this blog.

USE Admin

IF Object_ID('dbo.udfGetFilenameAndExtOnly') Is Not Null
      DROP FUNCTION dbo.udfGetFilenameAndExtOnly
go

CREATE FUNCTION dbo.udfGetFilenameAndExtOnly(
      @In varchar(1000)
      )
RETURNS varchar(300)
AS
/*    DATE              AUTHOR            REMARKS
      8/22/14          PPaiva            Initial creation.


      DESCRIPTION
            Returns filename and extention only.  That is, path is stripped away.

      USAGE
            SELECT dbo.udfGetFilenameAndExtOnly('O:\Program Files\Microsoft SQL Server\MSSQL10.HOLD\MSSQL\DATA\MSDBData.mdf')
            SELECT dbo.udfGetFilenameAndExtOnly('Admin_Log.ldf')

      DEBUG
            -- Last backslash, in reverse
            SELECT CharIndex('\', Reverse('O:\Program Files\DATA\MSDBData.mdf'))
            -- Pos of last backslash
            SELECT Len('O:\Program Files\DATA\MSDBData.mdf') - 13

*/

BEGIN
      DECLARE @Out varchar(300),
                  @Pos smallint
           
      -- Strip away path if it exists
      -- Get position of last backslash
      SET @Pos = CharIndex('\', Reverse(@In))
      IF @Pos > 1
            BEGIN
                  SET @Pos = Len(@In) - @Pos + 2
                  SET @Out = Substring(@In, @Pos, 500)
            END

      ELSE
            SET @Out = @In


      RETURN @Out

END





Wednesday, April 16, 2014

RestoreScriptGenerateFromBak - Generate restore script from backup file

Here’s a handy-dandy proc for restoring a database backup file.  You simply pass in the path of the backup, your new DB name, and the locations for data and log files. 

This is especially helpful when your database has multiple files (10, 20, 50 files) due to partitioning or separated file groups.  But even if you only have 2 files, it is still a time-saver.


USE Admin
IF OBJECT_ID('dbo.RestoreScriptGenerateFromBak') Is Not Null
      DROP PROC dbo.RestoreScriptGenerateFromBak
GO

CREATE PROC dbo.RestoreScriptGenerateFromBak
      @PathFilename varchar(300),
      @NewDBName varchar(100),
      @DataPath varchar(200),
      @LogPath varchar(200)
AS
/*    DATE        AUTHOR            REMARKS
      4/16/14           PPaiva            Initial creation.
     
      DESCRIPTION
            Creates a restore script for a given backup file
                  with a common specified destination folder for data
                  files, and another for log files.
            This is especially useful when making a copy of a multi-file
                  (10, 50, 100+ files) database, as in the case of
                  partitioning and/or multiple file groups.
            Click on Messages tab to review script.
     
      DEPENDENCIES
            Admin.dbo.udfGetFilenameOnly()
            RESTORE FILELISTONLY
     
      USAGE
            Exec RestoreScriptGenerateFromBak
                  'C:\Sql\Bak\ProdDB_20121116.bak',
                  'MyDevDB',
                  'E:\Sql\Data',
                  'L:\Sql\Logs'

            Exec RestoreScriptGenerateFromBak
                  'C:\Paul\SqlDataFromClient\Sopa\Sopa_Feb27.bak',
                  'SopaFeb27',
                  'E:\Data',
                  'F:\Logs'

      DEBUG
            RESTORE FILELISTONLY
            FROM DISK = 'C:\Paul\SqlDataFromClient\Sopa\Sopa_Feb27.bak'
           
*/
SET NOCOUNT ON

DECLARE @Sql varchar(Max),
            @crlf varchar(2),
            @Path varchar(200)
           
SET @crlf = CHAR(13) + CHAR(10)
SET @DataPath = dbo.udfAddBackslashIfNec(@DataPath)
SET @LogPath = dbo.udfAddBackslashIfNec(@LogPath)


CREATE TABLE #FileList(
      LogicalName nvarchar(128) NOT NULL PRIMARY KEY,
      PhysicalName nvarchar(260),
      Type char(1),
      FileGroupName nvarchar(128),
      Size numeric(20,0),
      MaxSize numeric(20,0),
      FileID bigint,
      CreateLSN numeric(22,0),
      DropLSN numeric(25,0),
      UniqueID uniqueidentifier,
      ReadOnlyLSN numeric(25,0),
      ReadWriteLSN numeric(25,0),
      BackupSizeInBytes bigint,
      SourceBlockSize int,
      FileGroupID int,
      LogGroupGUID uniqueidentifier,
      DifferentialBaseLSN numeric(25,0),
      DifferentialBaseGUID uniqueidentifier,
      IsReadOnly bit,
      IsPresent bit,
      TDEThumbprint varbinary(32)
      )

-- Determine version of SQL
DECLARE @Version varchar(10) 
SELECT @Version = Convert(varchar(10), ServerProperty('ProductVersion'))

IF Left(@Version, 2) = '9.'
      ALTER TABLE #FileList DROP COLUMN TDEThumbprint
     
SET @Sql = 'RESTORE FILELISTONLY FROM DISK = ''' + @PathFilename + ''''
     
INSERT INTO #FileList
      Exec (@Sql)

SELECT *
FROM #FileList
ORDER BY FileID

DECLARE @LogicalName nvarchar(128),
            @Filename varchar(300),
            @FileID int,
            @MaxFileID int,
            @Type char(1),
            @NumIterations int

SELECT  @MaxFileID = MAX(FileID),
            @FileID = 0,
            @NumIterations = 0,
            @Sql = 'RESTORE DATABASE [' + @NewDBName + ']
FROM DISK = ''' + @PathFilename + '''
WITH '
FROM #FileList

WHILE @FileID < @MaxFileID
      BEGIN
            SET @NumIterations = @NumIterations + 1
     
            -- Get next file
            SELECT @FileID = MIN(FileID)
            FROM #FileList
            WHERE FileID > @FileID
     
            -- Get other attribs for this FileID
            SELECT  @LogicalName = LogicalName,
                        @Filename = dbo.udfGetFilenameOnly(PhysicalName),
                        @Type = Type
            FROM #FileList
            WHERE FileID = @FileID
     
            IF @NumIterations = 1
                  SET @Sql = @Sql
            ELSE
                  SET @Sql = @Sql + '     '
     
            --SELECT @Type Type, @DataPath DataPath, @LogPath LogPath
                 
            IF @Type = 'D'
                  SET @Path = @DataPath
            ELSE
                  BEGIN
                        SET @Path = @LogPath
           
                        -- Force suffix to be .ldf if a log file
                        SET @Filename = REPLACE(@Filename, '.ndf', '.ldf')
                        SET @Filename = REPLACE(@Filename, '.mdf', '.ldf')
                  END
           
            SET @Sql = @Sql + 'MOVE ''' + @LogicalName + ''' TO ''' +  @Path + @NewDBName + '_' + @Filename + ''',' + @crlf
           
     
      END

-- Remove last comma
SET @Sql = LEFT(@Sql, len(@Sql) - 3)

Print @Sql