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.

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

     

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

FilesShow

Handy procedure for finding which folders or files on a given disk (or path) are consuming the most space. 

This works by shelling out to DOS and then running DIR /s which means that it requires xp_cmdshell to be enabled.  It may not be preferred in a production environment, but you can always enable it, run this utility, then disable it again.

After running this by passing in a drive letter (or path), switch to the Messages pane and copy/paste the resultant script into a new window.  You now have every file on that disk (or path) in a table that can be queried.  Some sample queries are provided, such as GB/Path, identifying files in the Recycle Bin, and sorting by largest files.

If you want to do analysis on a disk or volume that doesn’t have SQL Server, that’s ok – just run this and pass in the path.  It will work as long as SQL Server has access to that path.  For example,

Exec Admin.dbo.FilesShow '\\10.0.0.17\g$'

Exec Admin.dbo.FilesShow '\\MachineName\MyFileshare'

A global temp table whose name is comprised of the user’s login is used, allowing for querying after the proc finishes executing.  Since the user’s name is part of the tablename, multiple persons can use this simultaneously without interfering with each other.

IF object_id('dbo.FilesShow') Is Not Null

      DROP PROC dbo.FilesShow

GO

CREATE PROC dbo.FilesShow

      @DriveLetterOrPath varchar(200),

      @AppendFiles bit = 0,

      @SuppressOutput bit = 0,

      @QueryHelp1 varchar(Max)= '' OUTPUT,

      @QueryHelp2 varchar(Max)= '' OUTPUT,

      @Debug bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      1/15/12           PPaiva            Initial creation.

                                                     

      DESCRIPTION

            Populates a global temp table with file info for all files

                  on a given drive letter or path.

            A global temp table is used so the results can be further

                  refined.

            After running proc, switch to Messages pane to get SQL code

                  for further analysis.              

            To query for files on all drives, see proc FilesShowAll.

      USAGE

            -- Admin.dbo.FilesShow c

            Admin.dbo.FilesShow r, 0, 0, '', '', 1

            Admin.dbo.FilesShow e  

            Admin.dbo.FilesShow g, 1

            Admin.dbo.FilesShow j

            Admin.dbo.FilesShow k

            Admin.dbo.FilesShow l

            Admin.dbo.FilesShow m

            Admin.dbo.FilesShow n

            Admin.dbo.FilesShow o

            Admin.dbo.FilesShow r

            Admin.dbo.FilesShow s

            Admin.dbo.FilesShow t

            Admin.dbo.FilesShow u

            Admin.dbo.FilesShow v

            Admin.dbo.FilesShow w

           

    Admin.dbo.FilesShow '\\10.0.0.17\g$'

    Admin.dbo.FilesShow '\\MachineName\MyFileshare'

            To for files on more than one drive letter, use @AppendFiles.

                        For example, to include files on drives r, s, and t, run:

                              Exec Admin.dbo.FilesShow r

                              Exec Admin.dbo.FilesShow s, 1

                              Exec Admin.dbo.FilesShow t, 1

      DEBUG

            xp_fixeddrives

            xp_cmdshell 'dir j:\ /s /-C /Ogn'

*/

SET NOCOUNT ON

DECLARE @Cmd varchar(500),

            @Drive char(1)

IF Len(@DriveLetterOrPath) = 1

      SET @Cmd = 'dir /s /-C /Ogn ' + @DriveLetterOrPath + ':\ '

ELSE

      SET @Cmd = 'dir /s /-C /Ogn ' + @DriveLetterOrPath

SET @Drive = Left(@DriveLetterOrPath, 1)

IF UPPER(@Drive) >= 'A' AND @Drive <= 'Z'

      SELECT ''

ELSE

      SET @Drive = '-'

     

-- Validate drive letter

CREATE TABLE #Drive (

      Drive varchar(1),

      MBFree char(50)

      )

INSERT INTO #Drive

      Exec master..xp_fixeddrives

SELECT *

FROM #Drive

IF Len(@DriveLetterOrPath) = 1

      BEGIN

            IF Not Exists(    SELECT *

                                    FROM #Drive

                                    WHERE Drive = @DriveLetterOrPath

                              )

                  BEGIN

                        SELECT ' No ' + Upper(@DriveLetterOrPath) + ': drive found. Acceptable Drives are:' UNION

                        SELECT Drive

                        FROM #Drive

                        RETURN

                  END

                 

      END

-- Generate global table name

DECLARE @GlobalTableName varchar(200) = '##FilesShow_' + Substring(system_user, CHARINDEX( '\', SYSTEM_USER) + 1, 100),

            @s varchar(Max)

IF @Debug = 1

      Print 'Global table name = ' + @GlobalTableName

IF @AppendFiles = 0

      -- Do not append, therefore need to drop the table

      BEGIN

            SET @s = 'IF Object_ID(''tempdb.dbo.' + @GlobalTableName + ''') Is Not Null

                  DROP TABLE ' + @GlobalTableName

            IF @Debug = 1

                  Print @s

            Exec(@s)

      END

ELSE

      -- Append - no need to drop, but need to guard against re-running for the same drive

      BEGIN

            SET @s = 'IF Object_ID(''tempdb.dbo.' + @GlobalTableName + ''') Is Not Null

                  DELETE FROM ' + @GlobalTableName + ' WHERE Drive = ''' + @Drive + ''''

            IF @Debug = 1

                  Print @s

            Exec(@s)

     

      END

-- Always create the table if it does not exist

SET @s = '

IF Object_ID(''tempdb.dbo.' + @GlobalTableName + ''') Is Null

      CREATE TABLE ' + @GlobalTableName + '(

            ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY,

            Drive char(1),

            FreeGBytesFMT char(15),

            SizeBytesFMT char(15),

            SizeGBytes decimal(10, 1),

            Name varchar(1000),

            Date datetime,

            PathName varchar(2000),

            Path varchar(1000),

            FreeMBytes int,

            SizeBytes bigint,

            DeleteScript varchar(2000),  

            IsRecycleBin bit DEFAULT 0,

            Raw varchar(2000)

            )'

IF @Debug = 1

      Print @s

Exec(@s)

SET @s = '

INSERT INTO ' + @GlobalTableName + ' (Raw)

      Exec xp_cmdshell ''' + @Cmd + '''

-- For debugging

--SELECT  --charindex(''dir(s)'', Raw) ''Pos "dir(s)"'',

--          --charindex(''bytes free'', Raw) ''Pos "bytes free"'',

--          Replace(substring(Raw, charindex(''dir(s)'', Raw) + 7, charindex(''bytes free'', Raw) - 7 - charindex(''dir(s)'', Raw)), '','', '''')

--FROM ##FilesShow_PaulLocal

--WHERE Raw Like ''%bytes free%''

UPDATE ' + @GlobalTableName + '

SET FreeMBytes = Convert(bigint,

            Replace(substring(Raw, charindex(''dir(s)'', Raw) + 7, charindex(''bytes free'', Raw) - 7 - charindex(''dir(s)'', Raw)), '','', '''')

            ) /1000000

FROM ##FilesShow_PaulLocal

WHERE Raw Like ''%bytes free%''

UPDATE ' + @GlobalTableName + '

SET FreeMBytes = (SELECT Max(FreeMBytes) FROM ' + @GlobalTableName + ')

DELETE

FROM ' + @GlobalTableName + '

WHERE Left(Raw, 2) = '' ''

   OR Left(Raw, 4) = '' Vol''

   OR Raw Is Null

   OR Raw Like ''%<%''

UPDATE ' + @GlobalTableName + '

SET Date = Convert(datetime, Left(Raw, 20)),

      SizeBytes = Convert(bigint, Substring(Raw, 21, 18)),

      SizeBytesFMT = Admin.dbo.udfPadLeft(Convert(varchar(30), Admin.dbo.udfFormatInteger(Convert(bigint, Substring(Raw, 21, 18)))), 15, '' ''),

      SizeGBytes = Convert(bigint, Substring(Raw, 21, 18)) / 1000000000.0,

      Name = Substring(Raw, 40, 1000)

WHERE Left(Raw, 13) <> '' Directory of''

  AND Left(Raw, 1) Like ''[0-9]''

UPDATE ' + @GlobalTableName + '

SET Path = Substring(Raw, 15, 1000)

WHERE Left(Raw, 13) = '' Directory of''

'

IF @Debug = 1

      Print @s

Exec(@s)

SET @s = 'DECLARE @ID int,

            @MaxID int,

            @PrevPath varchar(1000),

            @Path varchar(1000)

SELECT  @MaxID = Max(ID),

            @ID = 0

FROM ' + @GlobalTableName + '

WHILE @ID < @MaxID

      BEGIN

            -- Get Previous Path

            SELECT @PrevPath = Path

            FROM ' + @GlobalTableName + '

            WHERE ID = @ID

            -- Get next row

            SELECT @ID = Min(ID)

            FROM ' + @GlobalTableName + '

            WHERE ID > @ID

            -- Get corresponding Path

            SELECT @Path = Path

            FROM ' + @GlobalTableName + '

            WHERE ID = @ID

            IF @Path Is Null AND @PrevPath Is Not Null

            UPDATE ' + @GlobalTableName + '

            SET Path = @PrevPath

            WHERE ID = @ID

      END

'

IF @Debug = 1

      Print @s

Exec(@s)

SET @s = '

DELETE

FROM ' + @GlobalTableName + '

WHERE Date Is Null

UPDATE ' + @GlobalTableName + '

SET PathName = dbo.udfAddBackslashIfNec(Path) + Name

UPDATE ' + @GlobalTableName + '

SET DeleteScript = ''Exec xp_cmdshell ''''del "'' + PathName + ''"''''''

UPDATE ' + @GlobalTableName + '

SET IsRecycleBin = 1

WHERE PathName Like ''' + @DriveLetterOrPath + ':\$Recycle.Bin\%''

   OR PathName Like ''' + @DriveLetterOrPath + ':\Recycle%''

  

UPDATE ' + @GlobalTableName + '

SET Drive = ''' + @Drive + '''

  

-- Total bytes free

UPDATE ' + @GlobalTableName + '

SET FreeGBytesFMT = Admin.dbo.udfFormatInteger((FreeMBytes/1000.))'

IF @Debug = 1

      Print @s

IF @Debug = 1

      SELECT  @GlobalTableName '@GlobalTablename',

                  @DriveLetterOrPath '@DriveLetterOfPath',

                  @Drive '@Drive'

     

Exec(@s)

-- Optional indexes

--SET @s = 'CREATE INDEX idx_Name_' + @GlobalTableName + ' ON ' + @GlobalTableName + '(Name)

--CREATE INDEX idx_SizeBytes_' + @GlobalTableName + ' ON ' + @GlobalTableName + '(SizeBytes)

--CREATE INDEX idx_PathName_' + @GlobalTableName + ' ON ' + @GlobalTableName + '(PathName)

--'

--IF @Debug = 1

--    Print @s

--Exec(@s)

SET @QueryHelp1 = 'SELECT  Drive,

            Count(*) NumFiles,

            Sum(SizeGBytes) UsedGBytes,

            Min(FreeMBytes/1000) FreeGBytes

FROM ' + @GlobalTableName + '

GROUP BY Drive

ORDER BY Drive

SELECT *

FROM ' + @GlobalTableName + '

'

IF @SuppressOutput = 0

      BEGIN

            IF @Debug = 1

                  Print @QueryHelp1

            Exec(@QueryHelp1)

END

SET @QueryHelp2 = '

-- Show all files, sort by largest

SELECT *

FROM ' + @GlobalTableName + '

WHERE Name Like ''%%''

ORDER BY SizeBytes desc

-- Count of all files by Drive

SELECT  Drive,

            Count(*) NumFiles,

            Sum(SizeGBytes) UsedGBytes,

            Min(FreeMBytes/1000) FreeGBytes

FROM ' + @GlobalTableName + '

GROUP BY Drive

ORDER BY Drive

-- Count of all files, all drives

SELECT Count(*) NumFilesAllDrives, Sum(SizeGBytes) TotalGBytesAddDrives

FROM ' + @GlobalTableName + '

-- GB/Path

SELECT Path, Convert(Decimal(5, 1), Sum(SizeBytes/1000000000.0)) SizeGB

FROM ' + @GlobalTableName + '

GROUP BY Path

HAVING Sum(SizeBytes/1000000000.0) >= .25

ORDER BY 2 desc

-- Files in Recycle Bin

SELECT *

FROM ' + @GlobalTableName + '

WHERE IsRecycleBin = 1

ORDER BY SizeBytes desc

-- Sum of Files in Recyle Bin

SELECT Admin.dbo.udfFormatInteger(Sum(SizeBytes)) SumBytesInRecycleBin

FROM ' + @GlobalTableName + '

WHERE IsRecycleBin = 1

-- Show most recent files

SELECT *

FROM ' + @GlobalTableName + '

ORDER BY Date desc

'

IF @SuppressOutput = 0

      Print @QueryHelp2