Sql for simplifying and automating the process of loading/backing up multiple databases

db-backup-utils.sql

USE master;
SET NOCOUNT ON 
GO

-- Sql for simplifying and automating the process of loading/backing up multiple databases.
-- Optionally override the restored db name.
-- Also has proc for doing an upgrade of a backup file via an intermediate sql server version.

-- The UI for backup/restore in ssms takes many clicks and doesn't remember anything, this
-- script will allow you to codify repetive actions.

-- https://gist.github.com/timabell/d43b21f889bd9dada355

--------------------------------
-- temporary proc definitions --
--------------------------------
-- these have to be first, skip past them to the calls that use them to define what you want done

create procedure #getBackupDbName
(
    @backupFile as nvarchar (500),
    @dbName AS nvarchar (500) output
) as
    begin
        -- Get db name from backup set. https://dba.stackexchange.com/questions/12437/extracting-a-field-from-restore-headeronly-in-sql-server/76464#76464
    DECLARE @sql NVARCHAR(500); -- for dynamic sql execution
        declare @headers table (
            BackupName varchar(256), BackupDescription varchar(256), BackupType varchar(256), 
            ExpirationDate varchar(256), Compressed varchar(256), Position varchar(256), DeviceType varchar(256), 
            UserName varchar(256), ServerName varchar(256), DatabaseName varchar(256), DatabaseVersion varchar(256), 
            DatabaseCreationDate varchar(256), BackupSize varchar(256), FirstLSN varchar(256), LastLSN varchar(256), 
            CheckpointLSN varchar(256), DatabaseBackupLSN varchar(256), BackupStartDate varchar(256), BackupFinishDate varchar(256), 
            SortOrder varchar(256), CodePage varchar(256), UnicodeLocaleId varchar(256), UnicodeComparisonStyle varchar(256), 
            CompatibilityLevel varchar(256), SoftwareVendorId varchar(256), SoftwareVersionMajor varchar(256), 
            SoftwareVersionMinor varchar(256), SoftwareVersionBuild varchar(256), MachineName varchar(256), Flags varchar(256), 
            BindingID varchar(256), RecoveryForkID varchar(256), Collation varchar(256), FamilyGUID varchar(256), 
            HasBulkLoggedData varchar(256), IsSnapshot varchar(256), IsReadOnly varchar(256), IsSingleUser varchar(256), 
            HasBackupChecksums varchar(256), IsDamaged varchar(256), BeginsLogChain varchar(256), HasIncompleteMetaData varchar(256), 
            IsForceOffline varchar(256), IsCopyOnly varchar(256), FirstRecoveryForkID varchar(256), ForkPointLSN varchar(256), 
            RecoveryModel varchar(256), DifferentialBaseLSN varchar(256), DifferentialBaseGUID varchar(256), 
        BackupTypeDescription varchar(256), BackupSetGUID varchar(256), CompressedBackupSize varchar(256)
        , Containment varchar(256)  -- containment is new, didn't exist in sql server 2000, so take this off when handling old backups
        );
        SET @sql = 'RESTORE HEADERONLY FROM DISK = ''' + @backupFile + '''';
        insert into @headers
            exec sp_executesql @sql
    select @dbName = DatabaseName from @headers;
end
GO

CREATE PROC #restoreDb @backupFile NVARCHAR(500), @restoreFolder NVARCHAR(500), @restoreAs nvarchar(500) = NULL AS
    DECLARE @sql NVARCHAR(500); -- for dynamic sql execution

    if @restoreAs is null
    begin
        exec #getBackupDbName @backupFile, @restoreAs output;
    end

    declare @files table (
        LogicalName varchar(256), PhysicalName varchar(256), Type varchar(256), FileGroupName varchar(256), Size varchar(256),
        MaxSize varchar(256), FileId varchar(256), CreateLSN varchar(256), DropLSN varchar(256), UniqueId varchar(256),
        ReadOnlyLSN varchar(256), ReadWriteLSN varchar(256), BackupSizeInBytes varchar(256), SourceBlockSize varchar(256),
        FileGroupId varchar(256), LogGroupGUID varchar(256), DifferentialBaseLSN varchar(256), DifferentialBaseGUID varchar(256),
        IsReadOnly varchar(256), IsPresent varchar(256), TDEThumbprint varchar(256) );

    -- get file names
    SET @sql = 'RESTORE FILELISTONLY FROM DISK = ''' + @backupFile + '''';
    -- print @sql
    insert into @files
        exec sp_executesql @sql
    DECLARE @LogicalNameData varchar(255);
    DECLARE @LogicalNameLog varchar(255);
    select @LogicalNameData = LogicalName from @files WHERE Type = 'D'
    select @LogicalNameLog = LogicalName from @files WHERE Type = 'L'
    -- select * from @files

    -- http://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/
    SET @sql = 'RESTORE DATABASE [' + @restoreAs + '] FROM DISK = ''' + @backupFile + ''' WITH
        MOVE ''' + @LogicalNameData + ''' TO ''' + @restoreFolder + @restoreAs + '.mdf'',
        MOVE ''' + @LogicalNameLog  + ''' TO ''' + @restoreFolder + @restoreAs + '.ldf'';';
    --print @sql
    exec sp_executesql @sql

    print 'Restored ' + @restoreAs;

    -- fix db diagrams - https://stackoverflow.com/questions/2043382/database-diagram-support-objects-cannot-be-installed-no-valid-owner/
    SET @sql = 'ALTER AUTHORIZATION ON DATABASE::[' + @restoreAs + '] TO [sa];';
    exec sp_executesql @sql
GO -- end of temporary stored proc

CREATE PROC #backupDb @backupPath NVARCHAR(500), @dbName sysname AS
    -- @backupPath - full path to the .bak file to create, including filename and extension
    DECLARE @sql NVARCHAR(500); -- for dynamic sql execution

    set @sql = 'BACKUP DATABASE [' + @dbName + '] TO  DISK = N''' + @backupPath + ''' WITH COPY_ONLY, NOFORMAT, NOINIT,
        NAME = N''' + @backupPath + ''', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
    exec sp_executesql @sql
GO -- end of temporary stored proc

CREATE PROC #killDb @dbName sysname AS
    DECLARE @sql NVARCHAR(500); -- for dynamic sql execution

    if exists (select 1 from sys.databases where name = @dbName)
    begin
        print 'Dropping ' + @dbName
        set @sql = '
            -- kill active connections:
            ALTER DATABASE ' + @dbName + ' SET OFFLINE WITH ROLLBACK IMMEDIATE;
            ALTER DATABASE ' + @dbName + ' SET ONLINE;
            -- drop
            DROP DATABASE ' + @dbName + ';
        ';
        exec sp_executesql @sql
    end
    else
    begin
        print 'Skipping drop of non-existent db ' + @dbName
    end
GO -- end of temporary stored proc

CREATE PROC #makeReadOnly @dbName sysname AS
    DECLARE @sql NVARCHAR(500); -- for dynamic sql execution

    SET @sql = 'ALTER DATABASE [' + @dbName + '] SET READ_ONLY WITH NO_WAIT;';
    exec sp_executesql @sql
GO -- end of temporary stored proc

CREATE PROC #upgradeBackupFile @backupFolder NVARCHAR(500), @backupFile NVARCHAR(500) AS
    -- use this to upgrade a backup file that is too old for you current db
    -- run it on an intermediate version, it will restore, backup and drop the db
    -- during the restore, sql server will automatically upgrade the compatibility of the db.
    -- e.g. to upgrade a sql server 2000 backup to be able to restore it onto sql server 2014, run this script on sql server 2008
    -- automates this: http://stackoverflow.com/questions/15788624/restore-a-sql-server-2000-backup-on-sql-server-2012/15788705#15788705
    -- @backupFolder - will read/write .bak files in this folder
    -- @backupFile - will read this file, and write out another file with the filename pre-pended with "upgraded_"
    -- depends on temporary proc #restoreDb and #backupDb

    DECLARE @sql NVARCHAR(500); -- for dynamic sql execution
    declare @dbName nvarchar (50);
    declare @backupPath nvarchar (500) = @backupFolder + @backupFile;

    print 'Getting db name from backup set...';
    exec #getBackupDbName @backupPath, @dbName output;
    print 'Backup db name was: ' + @dbName;

    declare @newDbName nvarchar (50) = 'upgraded_' + @dbName;

    print '';
    print 'Upgrading backup ' + @backupPath;

    print 'Restoring...';
    exec #restoreDb @backupPath, @backupFolder, @newDbName;

    -- sql server will automatically upgrade and set new compatibility level at this point

    -- backup upgraded db
    set @backupPath = @backupFolder + 'upgraded_' + @backupFile;

    print 'Creating backup ' + @backupFile + ' ...'

    exec #backupDb @backupPath, @newDbName;

    -- cleanup
    exec #killDb @newDbName
GO -- end of temporary stored proc

---------------------------------------
-- end of temporary proc definitions --
---------------------------------------


---------------------------------------
-- config
---------------------------------------

-- Change these paths filenames to suit your system
DECLARE @backupFolder NVARCHAR(500) = 'C:\sqlserver-dbs\backups\'; -- can be a \\unc\path\
DECLARE @restoreFolder NVARCHAR(500) = 'C:\sqlserver-dbs\'; -- where to put the restored .mdf & .ldf (default was to use value from .bak, which is not portable). must exist
DECLARE @dbName NVARCHAR(500) = 'Your_Db_Name_Here';

---------------------------------------
-- vars
---------------------------------------
DECLARE @newName NVARCHAR(500);
DECLARE @backupPath NVARCHAR(500);

---------------------------------------
-- example usage:
---------------------------------------

-- cleanup old copies:
-- !!DANGER!!
--exec #killDb @dbName

-- upgrade a legacy backup file
-- run this on sql server 2005, 2008, or 2008r2 to upgrade the legacy db to a sufficient compatibility to be restored to sql server 2014.
--exec #upgradeBackupFile @backupFolder, 'SomeSql2000Db.bak';

set @backupPath = @backupFolder + @dbName + '.bak'

-- backup a db
--exec #backupDb @backupPath

-- restore a db
--exec #restoreDb 'SomeBackup.bak', @restoreFolder, @dbName

-- (optionally) make it read only if it's a reference copy
--exec #makeReadOnly @dbName

---------------------------------------
-- this is a system for numbered versions, creating new copies as you go, uncomment to use it
---------------------------------------
DECLARE @releasedVersion NVARCHAR(500);
DECLARE @newVersion NVARCHAR(500) = null
--set @releasedVersion = '123'; -- version to use as source, comment out to skip restoring current version
--set @newVersion = '124'; -- version to create, if any, comment out to skip

if @newVersion is not null
begin
    print 'Creating new version: ' + @newVersion + '...';
    set @newName = @dbName + @newVersion;
    set @backupPath = @backupFolder + @dbName + @releasedVersion + '.bak'
    exec #restoreDb @backupPath, @restoreFolder, @newName
end

if @releasedVersion is not null
begin
    -- restore the released version again for reference
    print 'Restore the released version for reference: ' + @releasedVersion + '...';
    set @newName = @dbName + @releasedVersion;
    set @backupPath = @backupFolder + @newName + '.bak'
    exec #restoreDb @backupPath, @restoreFolder
    exec #markReadOnly @newName
end

---------------------------------------
-- cleanup temporary stored procs
---------------------------------------
drop proc #backupDb;
drop proc #killDb;
drop proc #makeReadOnly
drop proc #restoreDb
drop proc #upgradeBackupFile;
drop proc #getBackupDbName;
---------------------------------------
print 'all done'