About Me

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. 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 for 11 years, from January 2009 to 2020.

Monday, February 27, 2012

Scripts for Backup and Restore

Someone posed this question:  How do you generate scripts for backing up all the databases on ServerA and restoring them on ServerB?

BACKING UP
Generating the backup script is easy:

SELECT 'BACKUP DATABASE [' + Name + '] TO DISK = ''D:\Backup\' + name + '.bak'''
FROM sys.databases
ORDER BY name

Or, refer to a post from December 2011 which does this in a slightly fancier fashion via a stored proc that gives options for putting the date or date/time in the backup filename.  The stored proc is called BackupDB.

Note the proc BackupDB has a couple of dependencies, which can be easily installed:
        udfAddBackslashIfNec        
        DoesPathExist
        
RESTORING
Restoring is trickier, but doable.   I recommend taking the time to write the script – it will help you for years to come.

The RESTORE DATABASE script is shown in this stored procedure, RestoreScriptGenerateFromBak.

Sunday, February 26, 2012

RestoreScriptGenerateFromBak

The syntax for restoring a database file is simple if you have the same paths for the file locations between the source and destination servers. 

However if the database file locations in the source and destination servers are not the same, the RESTORE DATABASE syntax will have to include WITH MOVE which requires that you know the logical name of each file.

The logical name can be retrieved with this command.  Note it will also indicate whether the file is a database or log file.

RESTORE FILELISTONLY
FROM Disk = 'E:\Backups\CCS.bak'






Here’s a stored procedure which will generate the RESTORE DATABASE script when you pass in the location of the backup file.

It has one dependency which is easy to install:
        udfGetFilenameOnly

This script is especially handy when you have a large number of data or log files underlying your database.  The script allows you to pass in a path for Data and Log files.  If you need them to be on different paths, simply change them manually after the script is generated.

After running the proc, the script is shown on the Messages pane.  

Use Admin
GO

CREATE PROC dbo.RestoreScriptGenerateFromBak
      @PathFilename varchar(300),
      @NewDBName varchar(100),
      @DataPath varchar(200),
      @LogPath varchar(200)
AS
/*    DATE        AUTHOR            REMARKS
      2/5/12            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 useful when making a dev copy of a multi-file
                  production database.
     
      DEPENDENCIES
            Admin.dbo.udfGetFilenameOnly()
            RESTORE FILELISTONLY
     
      USAGE
            Exec RestoreScriptGenerateFromBak
                  'D:\Backup\ccs.bak',
                  'CCS',
                  'D:\',
                  'C:\Paul\Sql'

            Exec RestoreScriptGenerateFromBak
                  'K:\Backups\Prod01\MyDB_2012-02-03.bak',
                  'MyDB',
                  'M:\Data',
                  'L:\Logs'

      DEBUG
            RESTORE FILELISTONLY
            FROM DISK = 'E:\Sql\Backup\MyDB_2012-02-03.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
     

Tuesday, February 21, 2012

Populate a temp table from a dynamic query

Today somone on SQLServerCentral.com posted a question – how to populate a temp table with a dynamic SQL query.  The response I posted, shown below, is to use a global temp table, characterized by the double hash mark (##MyGlobalTemp).

The table will persist as long as you have a connection (spid) open that is accessing it. This may or may not be a good idea depending on your environment, but it will definitely work.

Regular temp tables are tricky to identify in metadata, since SQL Server will likely append some text to the name, to help keep the name unique.  Global temp tables, however, are easier to identify, so it is easier to check for its existence beforehand and create a "safe" process.


DECLARE @s varchar(1000)

IF object_id('tempdb.dbo.##MyGlobalTemp') Is Not Null
      DROP TABLE ##MyGlobalTemp


SET @s =
'SELECT *
INTO ##MyGlobalTemp
FROM (select name from sys.databases) as T'

Exec (@s)
go

SELECT *
FROM ##MyGlobalTemp