Technical Article

Import databases using backups

,

Import database from source server using full and transaction log backups as recorded in msdb. Use it to import one or more databases to your development environment, to test your backups regularly or to move databases to a new server. Works on SQL 7 and SQL 2000 with native and/or LiteSpeed full backups and native transaction log backup.

SET QUOTED_IDENTIFIER OFF 
GO
use Filer
go
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'spF_build_import' AND type = 'P' )
    DROP PROC spF_build_import
GO
CREATE procedure spF_build_import
@sourcedbserversysname = ''
,@dbnamesysname= ''
,@newdbnamesysname = ''
,@targetdir varchar(255) = ''
,@runrestore char(1) = 'N'
,@overwritedbchar(1) = 'N'
,@checkbackupchar(1) = 'Y'
,@ok int OUTPUT
AS
-- INPUT PARAMETERS
-----------------------------------------------------------------------------------------------------------
-- @sourcedbserver:  This is the source server where the database(s) are imported from.
--    Source server must be a linked server on the local importing server.
--Database must have been backed up to different server (UNC-path).
-----------------------------------------------------------------------------------------------------------
-- @dbname  :  If blank, all dbs are imported, except master, model, msdb and tempdb.
-----------------------------------------------------------------------------------------------------------
-- @newdbname  :  If @dbname is not blank, imported database can be renamed on target.
--   Database file names will also be renamed with 'IMP_' prefix. 
-----------------------------------------------------------------------------------------------------------
-- @targetdir  :  Database directory on target server.
--   If parameter value is blank, it will default to SQL Server Default 
--   Data Directory. If Default Data Directory is NULL, SP will abort.
-----------------------------------------------------------------------------------------------------------
-- @runrestore:  Enter Y to run import on target (current) server or N (default)  
--   to generate import script. Run import is not allowed to self.
--   Check is built in.
-----------------------------------------------------------------------------------------------------------
-- @overwritedb:  Enter Y to overwrite all databases with the same name or N if you don't
--want overwrite. Check is built in. RESTORE with REPLACE will only be
--generated or executed if you specify @overwritedb = 'Y'.
-----------------------------------------------------------------------------------------------------------
-- @checkbackup:Check existence of backup files with UNC-path on backup server.
--Default is Y for yes check. Enter N to skip check if you want to be able
--to generate script when target server is running on an account without
--access to backup the files. Script info from msdb system tables is 
--selected using the privileges of the linked server account. Note that
--in order to actually run the generated script, you must have access
--to the backup files with the account that is running SQL Server.
-----------------------------------------------------------------------------------------------------------
-- OUTPUT PARAMETER
-----------------------------------------------------------------------------------------------------------
-- @ok:  Returns 0 on success or 1 on failure. 
-----------------------------------------------------------------------------------------------------------
--
-- DOCUMENTATION
-----------------------------------------------------------------------------------------------------------
-- Import database from source server using full and transaction log backups as recorded in msdb. Use it to 
-- import one or more databases to your development environment, to test your backups regularly or to move 
-- databases to a new server. 
-----------------------------------------------------------------------------------------------------------
-- To move to a new server, take a transaction log backup and put all in read only mode on the source 
-- server, then run this SP on the target server with the source server name as an input parameter.
-----------------------------------------------------------------------------------------------------------
-- Import one or all databases. Generate import scripts or run import at once. Existence or access to  
-- backup files will be verified.
-----------------------------------------------------------------------------------------------------------
-- Run your destination SQL Server with an NT-account with privileges on the server with the backup files.
-----------------------------------------------------------------------------------------------------------
-- Link source server using an account that is allowed to read msdb system tables on source server.
-----------------------------------------------------------------------------------------------------------
-- Run this SP on target server where dbs are to be imported.
-----------------------------------------------------------------------------------------------------------
-- This SP can be put in any DB, Filer is the DBA database at our site.
-----------------------------------------------------------------------------------------------------------
-- Works on SQL 7 and SQL 2000 with native and/or LiteSpeed full backups and native transaction log backup.
-----------------------------------------------------------------------------------------------------------
--
-- REQUIREMENTS
-- ********************************************************************************************************
-- * BUILD AND IMPORT REQUIRES THAT ALL FULL BACKUPS ARE ZIPPED WITH SQL LITESPEED (FILE EXTENSION .ZBAK) *
-- * OR WITH NATIVE BACKUP. TRANS LOG BACKUPS ARE ALWAYS NATIVE BACKUPS. REQUIRES THAT ALL BACKUPS ARE    *
-- * KEPT ON DISK. REMEMBER TO KEEP ALL TRANSACTION LOG BACKUPS SINCE LAST TOTAL BACKUP AVAILABLE ON DISK.*
-- * DATABASES BACKUPS ON SOURCE SERVER MUST HAVE BEEN MADE TO A SHARED UNC-PATH, E.G. ON A BACKUP SERVER.*
-- ********************************************************************************************************
--
-- LIMITATIONS
-- ********************************************************************************************************
-- * Works only for restore from disk, preferably from a backup server.                                   *
-- * Only one target disk can be referenced. Works with native and/or LiteSpeed full backups and native   *
-- * transaction log backups, NOT with differential backups. Works best if you have a backup server.      *
-- * Databases master, model, msdb, tempdb, Northwind and pubs are always excluded from import when   *
-- * @dbname is not specified. If one of them is specified, commented (/*---*/) scripts are generated.    *
-- * Replicated databases are excluded from import. Restoring backups of replicated databases to a        *
-- * different server or database requires special considerations. For this reason, replicated databases  *
-- * are automatically excluded for import. Logging of excluded replicated databases is made to output.   *
-- ********************************************************************************************************
--
-- EXAMPLES
-----------------------------------------------------------------------------------------------------------
-- Import a database named 'ARKIV4706' from a source server named 'XXVIS009DB', run an immediate restore 
-- with REPLACE of an eventually existing database with the same name on the target server, place the 
-- database files in the SQL Server Default Data Directory on the target server and check for the   
-- existence of the backup files for 'ARKIV4706':
/*
DECLARE @rc int
exec Filer.dbo.spF_build_import 'XXVIS009DB','ARKIV4706','','','Y','Y','Y',@ok = @rc OUTPUT
*/-----------------------------------------------------------------------------------------------------------
-- Use a database named 'Filer' from a source server named 'XXVIS009DB', rename it to 'Test' on the 
-- target server, generate a restore script that will REPLACE any existing database with the name 'Test',  
-- place the database files in a directory named 'D:\Databases' on the target server and check for the  
-- existence of the backup files for 'Filer':
/*
DECLARE @rc int
exec Filer.dbo.spF_build_import 'XXVIS009DB','Filer','Test','D:\Databases','N','Y','Y',@ok = @rc OUTPUT
*/-----------------------------------------------------------------------------------------------------------
-- Use all databases except master, model, msdb, Northwind and pubs from a source server named 
-- 'XXVIS009DB', generate a restore script that does not overwrite any existing databases, locate the 
-- database files in the SQL Server Default Data Directory on the target server and check for the 
-- existence of backup files.
/*
DECLARE @rc int
exec Filer.dbo.spF_build_import 'XXVIS009DB','','','','N','N','Y',@ok = @rc OUTPUT
*/-----------------------------------------------------------------------------------------------------------
-- Use a database named 'Apps' from on the current server, generate a restore script with REPLACE of the  
-- existing database with the same name on the current server, place the database files in the SQL Server 
-- Default Data Directory on the server and check for the  existence of the backup files for 'Apps':
/*
DECLARE @rc int
exec Filer.dbo.spF_build_import '','Apps','','','N','Y','Y',@ok = @rc OUTPUT
*/-- Note that only script generation is supported, when source is same as target, as in the example above.
-----------------------------------------------------------------------------------------------------------
--
-- BACKGROUND
-----------------------------------------------------------------------------------------------------------
-- This stored procedure is based on one that was originally written by Greg Larsen for  
-- Washington State Department of Health, USA. Date of original: 12/16/2001 
-- I found it at http://www.sqlservercentral.com - thank you Greg!
-- Modified: By Lennart Gerdvall, Faktab Finans AB, Visby, Sweden.
-- Dates: 2002-11-07, 2005-06-10, 2006-06-16
-- Greg Larsens SP was designed to help rebuilding a crashed database server.
-- The original SP has been re-written to a great extent in order to allow creation of script on a  
-- database source server and execution of a restore script including transaction log backups on a 
-- target server to allow for easy import of databases from a running source server to a target server.
-- Use SP when moving to a new server in production or importing databases to a development server.
-- This SP generates or runs TSQL script that will restore one or all the databases specified, to the 
-- current SQL Server (target server). This SP takes into account when the last full backup was taken 
-- on the source server and all succeeding transaction log backups taken on the source server since 
-- the last database backup, based on the information in the source servers msdb database.
-----------------------------------------------------------------------------------------------------------
--
-- CHANGES
-- ********************************************************************************************************
-- Modified to include several backup files for one database. 
-- Modified to include MOVE to logical and physical file.
-- Skipped restore of diff backups - we don't do that kind.
-- Added input/output for one database, target directory and source server.
-- Added option to print script or to run restore script. DEFAULT IS PRINT SCRIPT.
-- Blank target directory will use SQL Server Default Data Directory. If this also is blank, SP will stop 
-- before doing anything.
-- Added check for existence of backup files on backup server.
-- Added check for existence of target directory.
-- Added option to change database name on target server. If this option is selected, database file names
-- will be preceeded by the prefix 'IMP_<new database name>_' to minimize the risk of of overwriting files.
-- Added option to control overwrite of databases. Default is NO OVERWRITE (N).
-- Added option to disable check of existence of backup files.
-- Added check of target disk size. No import is made if database takes more than 90 % of available space.
-- Added check for existence of named database on source server.
-- Added check for existence of a full backup for the current database.
-- Added check of attempt to import replicated databas. This is not allowed.
-- Added space of replaced database files on target drive, when calculating available disk space on target.
--
-----------------------------------------------------------------------------------------------------------
-- INITS, DECLARATIONS AND CHECKS
-----------------------------------------------------------------------------------------------------------
-- Cleanup temp tables
IF OBJECT_ID('tempdb..#sourcebackups') IS NOT NULL DROP TABLE #sourcebackups
IF OBJECT_ID('tempdb..#FileExists') IS NOT NULL DROP TABLE #FileExists
IF OBJECT_ID('tempdb..#DriveSpace') IS NOT NULL DROP TABLE #DriveSpace

SET QUOTED_IDENTIFIER OFF 
SET NOCOUNT ON
SET ANSI_NULLS OFF 
SET @ok = 0

-- Variables used in original SP
DECLARE 
 @cmd nvarchar (4000)
 ,@cmd2 nvarchar (4000)
 ,@db varchar(128)
 ,@filename varchar(4000)
 ,@cnt int
 ,@num_processed int
 ,@name varchar(4000) 
,@phys  varchar(4000)
 ,@physical_device_name varchar(512) 
 ,@backup_start_date datetime
 ,@type char(1)

-- Variables added by LGE
DECLARE 
 @omvand varchar(500)
,@slashpos integer
,@mypartlen integer
,@mydir varchar(200)
,@fyspath varchar(500)
,@backupid int
,@devcount int
,@valcount int
 ,@mybackupfilevarchar(8000)
,@SSQL varchar(8000)
,@SQLMOVE varchar(8000)
,@SQLFULL varchar(8000)
,@myHeadervarchar(400)
 ,@logical_name nvarchar(4000) 
,@physical_name nvarchar(4000) 
,@logdevcount int
,@logvalcountint
,@zippedvarchar(2)-- N or Y
,@remotedbcursorsqlvarchar(500)
,@currentbackupstartdatetime
,@thepos int
,@default_data_dir varchar(255)
,@sqlphysicalnvarchar(2000)
,@parmdefdevice nvarchar(200)
,@sqlbackupidnvarchar(2000)
,@parmdefbackupid nvarchar(200)
,@returnrowint-- General rowcounter
,@ret             int-- Return code for SP
,@user            sysname         -- Current user
,@commentvarchar(3)-- Comment prints
,@logbakfilenamevarchar(512)-- Test of transaction log file backup name
,@customerrmessvarchar(500)    -- Keeps my customized error messages.
,@quotesqlvarchar(100)-- To SET QUOTED_IDENTIFIER OFF 
,@mydbsize NUMERIC(18,2)
,@getdbsize NVARCHAR(1000)
,@currdbsizeINT
,@parmdefdbsize nvarchar(200)
,@StatusReport varchar(1000)
,@DirName varchar(255)
,@spacelimit NUMERIC(5,2)
,@dbspace  NUMERIC(5,2)
,@numdbINT
,@sqlnumdbNVARCHAR(200)
,@parmsnumdbNVARCHAR(50)
,@ispublishedINT
,@sqlpublisheddbNVARCHAR(200)
,@parmspublisheddbNVARCHAR(50)
,@publtypeVARCHAR(50)
,@mydbsizetarget NUMERIC(18,2)
,@getdbsizetarget NVARCHAR(1000)
,@currdbsizetargetINT
,@parmdefdbsizetarget nvarchar(200)


SET @user = SUSER_SNAME()
SET @ret = 0
SET @quotesql = 'SET QUOTED_IDENTIFIER OFF'

-- Check if current user is sysadmin
IF IS_SRVROLEMEMBER('sysadmin') = 0
BEGIN                                                 
SET @customerrmess = 'The current user %s is not a member of the sysadmin role on ' + @@servername
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP   
END

-- Disallow multiple rename of databases
IF (@dbname = '' AND @newdbname > '' )
BEGIN
SET @customerrmess = 'You are not allowed to rename multiple databases for import to target server'
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END

-- Check input parameter for running import or generating import script
SET @runrestore = UPPER(@runrestore)
IF (@runrestore <> 'Y' AND @runrestore <> 'N' )
BEGIN
SET @customerrmess = 'Enter Y to restore database to target server or N to generate script for restore'
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END
ELSE
BEGIN
-- Comment information output if script is generated
IF @runrestore = 'Y' SET @comment = '' ELSE SET @comment = '-- ' 
END

-- Check input parameter for overwriting database or not
SET @overwritedb = UPPER(@overwritedb)
IF (@overwritedb <> 'Y' AND @overwritedb <> 'N' )
BEGIN
SET @customerrmess = 'Enter Y to overwrite database on target server or N to avoid overwriting'
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END

-- Check input parameter for checking existence of database backup files or not
SET @checkbackup = UPPER(@checkbackup)
IF (@checkbackup <> 'Y' AND @checkbackup <> 'N' )
BEGIN
SET @customerrmess = 'Enter Y for checking existence of database backup files or N to skip check'
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END

-- If this is an import from last backup on current server to itself, do not allow run, only script generation
IF ((@sourcedbserver = '' OR @sourcedbserver = @@servername) AND @runrestore = 'Y')
BEGIN
SET @customerrmess = 'Only script generation is supported for self import to ' + @@servername + ' - change parameter @runrestore to N'
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END

-- Check existence of the linked server where you want to import the database(s) from
IF @sourcedbserver > ''
BEGIN
SELECT @returnrow = count(srvname) from master..sysservers where srvname = @sourcedbserver
IF @returnrow = 0
BEGIN
SET @customerrmess = 'This is not a linked server on ' + @@servername + ' - this is a requirement'
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END
END

-- Headlines
BEGIN
IF @sourcedbserver > ''
     SELECT @myHeader = @comment + 'Import of databases to SQL Server ' + @@SERVERNAME + ' created from msdb on ' + UPPER(@sourcedbserver) + ' at ' + convert(varchar(30),Getdate(),120)
ELSE
     SELECT @myHeader = @comment + 'Import of databases to SQL Server ' + @@SERVERNAME + ' created from msdb on LOCAL server at ' + convert(varchar(30),Getdate(),120)
END

-- Fix the input parameter @sourcedbserver and add '[servername].' to allow reference to linked tables
IF @sourcedbserver > '' SET @sourcedbserver = '[' + @sourcedbserver + '].'

-- Do we have a database on the source server?
IF @dbname > ''
BEGIN
SET @parmsnumdb = N'@pnumdb INT output'
SET @sqlnumdb = N'SELECT @pnumdb = COUNT(name) FROM ' + @sourcedbserver + '[master].[dbo].[sysdatabases] WHERE name = '+ CHAR(39) + @dbname + CHAR(39)

-- Do a database count on source server
EXECUTE sp_executesql @sqlnumdb, @parmsnumdb, @pnumdb = @numdb output
IF @numdb = 0 
BEGIN
IF @sourcedbserver = '' SET @customerrmess = 'Source database ' + @dbname + ' was not found on SQL Server ' + @@servername
ELSE SET @customerrmess = 'Source database ' + @dbname + ' was not found on SQL Server ' + @sourcedbserver
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END
END

-- Fix the input parameter @targetdir if no target directory is specified. Get it from the registry if defined.
IF @targetdir = ''
BEGIN
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\MSSQLServer','DefaultData',@default_data_dir OUTPUT   
IF @default_data_dir IS NULL
BEGIN
SET @customerrmess = 'Missing value for SQL Server Default Data Directory on LOCAL Server ' + @@servername + ' - specify a target directory'
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END
ELSE
BEGIN
SELECT @targetdir = @default_data_dir
END
END

--- Put an ending backslash for @targetdir if missing
IF @targetdir > ''
BEGIN
SELECT @thepos = CHARINDEX ( '\' , REVERSE(@targetdir), 1) 
IF @thepos > 1  or @thepos = 0
BEGIN
SELECT @targetdir = @targetdir + '\'
END
END

-- Create table for file and directory existence test, in order to suppress row message from exec master..xp_fileexist
Create Table #FileExists (File_Exists INT,File_is_a_Directory INT,Parent_Directory_Exists INT )

-- Check existence of target directory
IF @targetdir > ''
BEGIN
-- Check existence of target directory on server
-- PRINT @comment + 'Checking existence of target directory ' + @targetdir + ' on server ' + @@servername
INSERT INTO #FileExists exec master..xp_fileexist @targetdir
IF NOT EXISTS (Select * From #FileExists Where File_is_a_Directory = 1)
BEGIN
SET @customerrmess = 'Target directory ' + @targetdir + ' does not exist on server ' + @@servername
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END
-- Empty file existence table
TRUNCATE TABLE #FileExists
END
ELSE
BEGIN
SET @customerrmess = 'Target directory has not been specified on server ' + @@servername
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END
-----------------------------------------------------------------------------------------------------------
-- SECTION 1, GET DATA ON BACKUPS FROM MSDB
-----------------------------------------------------------------------------------------------------------
-- DECLARE database cursor
DECLARE @dbcursor CURSOR
DECLARE @mysetcursor nvarchar(1000)

-- Set SELECT for database cursor
IF @dbname = ''
SET @remotedbcursorsql = 'SELECT name from ' + @sourcedbserver + 'master.dbo.sysdatabases 
where name not in (''' + 'master' + ''',''' + 'model' + ''',''' + 'msdb' + ''',''' + 'tempdb' + ''',''' + 'Northwind' + ''',''' + 'pubs' + ''') order by name asc'
ELSE
SET @remotedbcursorsql = 'SELECT name from ' + @sourcedbserver + 'master.dbo.sysdatabases where name = ''' + @dbname + ''''

-- Open cursor containing list of database names.
SET @mysetcursor = 'SET @dbcursor = CURSOR FOR ' + @remotedbcursorsql + ' FOR READ ONLY; OPEN @dbcursor'
EXEC sp_executesql @mysetcursor,N'@dbcursor cursor OUTPUT', @dbcursor OUTPUT

-- Headings and settings
IF UPPER(@runrestore) = 'Y' EXEC(@quotesql)
ELSE
BEGIN 
PRINT (@myHeader)
PRINT REPLICATE('-',LEN(@myHeader))
PRINT @quotesql + CHAR(13) + 'GO'
END

-- Create a global temporary table that will hold the name of the backup, the database name, and the type of database backup.
CREATE table #sourcebackups (id int IDENTITY(1,1) PRIMARY KEY CLUSTERED, name nvarchar(255), database_name nvarchar(50), type char(1), phys nvarchar(3500), start datetime NULL)
SET IDENTITY_INSERT #sourcebackups OFF

FETCH next from @dbcursor into @db
-- Process until no more databases are left
WHILE @@FETCH_STATUS = 0
BEGIN
-----------------------------------------------------------------------------------------------------------
-- Subsection 1A: FULL BACKUPS
-----------------------------------------------------------------------------------------------------------
-- Check if the database is replicated - do not allow import of replicated databases.
SET @parmspublisheddb = N'@pispublished INT output'
SET @sqlpublisheddb = N'SELECT @pispublished = category FROM ' + @sourcedbserver + '[master].[dbo].[sysdatabases] WHERE name = ' + CHAR(39) + @db + CHAR(39)

-- If replicated, @ispublished will be 1 = Published, 2 = Subscribed, 4 = Merge Published or 8 = Merge Subscribed.
-- Note: Other category values in SQL 7 - e.g. 5 is transactional replication.
EXECUTE sp_executesql @sqlpublisheddb, @parmspublisheddb, @pispublished = @ispublished output
IF @ispublished > 0 
BEGIN
IF @ispublished = 1 SET @publtype = 'Published' 
ELSE IF @ispublished = 2 SET @publtype = 'Subscribed'
ELSE IF @ispublished = 4 SET @publtype = 'Merge Published'
ELSE IF @ispublished = 8 SET @publtype = 'Merge Subscribed'
ELSE SET @publtype = 'Unknown Publication Type'
IF @sourcedbserver = '' SET @customerrmess = 'Source database ' + @db + ' is a replication database (' + @publtype + ') on SQL Server ' + @@servername + CHAR(13) + 'It will be excluded from import'
ELSE SET @customerrmess = 'Source database ' + @db + ' is a replication database (' + @publtype + ') on SQL Server ' + @sourcedbserver + CHAR(13) + 'It will be excluded from import'
RAISERROR(@customerrmess,1,1)
SET @ret = 0
GOTO GETNEXTDB
END

-- Check if overwrite is allowed or not, unless you want script only
IF (UPPER(@overwritedb) = 'N' and UPPER(@runrestore) = 'Y')
BEGIN
IF @newdbname > ''
BEGIN
IF EXISTS (Select name From master..sysdatabases WHERE name = @newdbname)
BEGIN
SET @customerrmess = 'New database ' + @newdbname + ' already exists on target server ' + @@servername + ', change the @overwritedb parameter to Y to overwrite'
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP   
END
END
ELSE
BEGIN
IF EXISTS (Select name From master..sysdatabases WHERE name = @db)
BEGIN
SET @customerrmess = 'Database ' + @db + ' already exists on target server ' + @@servername + ', change the @overwritedb parameter to Y to overwrite'
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP   
END
END
END

SET @parmdefbackupid = N'@pbackupid int output,@pbackup_start_date datetime output'

-- Get ID and time of last full backup.
SET @sqlbackupid = 
N'SELECT @pbackupid = 
MAX(bs.backup_set_id), 
@pbackup_start_date = 
MAX(bs.backup_start_date) 
FROM ' + @sourcedbserver + 'msdb.dbo.backupset as bs ' +
'INNER JOIN ' +
@sourcedbserver + 'msdb.dbo.backupfile as bf ON 
bs.backup_set_id = bf.backup_set_id
WHERE
bs.type = ''' + 'D' + ''' and bs.database_name = ''' + @db + ''''

EXECUTE sp_executesql @sqlbackupid, @parmdefbackupid, @pbackupid = @backupid output, @pbackup_start_date = @backup_start_date output

-- Did a full database backup name get found?
IF @backupid IS NULL
BEGIN
SET @customerrmess = @comment + 'A full backup is missing for source database ' + @db + '. Backup database before running import'
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END

-- Get size of database on source server, check for disk space on target server
SET @parmdefdbsize = N'@pmydbsize NUMERIC(18,2) output'
SET @getdbsize = N'SELECT @pmydbsize = (SUM(size)/128.0) FROM ' + @sourcedbserver + '[' + @db + '].[dbo].[sysfiles]'
EXECUTE sp_executesql @getdbsize, @parmdefdbsize, @pmydbsize = @mydbsize output
SET @currdbsize = CAST(@mydbsize AS INT)

-- Get the drive letter from the target directory
set @DirName = SUBSTRING(@targetdir,1,1) 
set @StatusReport = ''

-- Allow use of max 90% of available space on target disk
set @spacelimit = 0.9 

-- Get size of an existing database on the target server that is to be replaced
-- If there is one, add size in @currdbsizetarget to @dbspace since we drop the old one
SET @currdbsizetarget = 0
IF (@dbname > '' AND @newdbname > '')
BEGIN
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @newdbname)
BEGIN
-- Get total filesize of database in target directory on target server
SET @parmdefdbsizetarget = N'@pmydbsizetarget NUMERIC(18,2) output'
SET @getdbsizetarget = N'SELECT @pmydbsizetarget = (SUM(size)/128.0) FROM ' + 
'[' + @newdbname + '].[dbo].[sysfiles] ' +
'WHERE SUBSTRING(filename,1,1) = ' + CHAR(39) + @DirName + CHAR(39)

-- Get size of database on source server
EXECUTE sp_executesql @getdbsizetarget, @parmdefdbsizetarget, @pmydbsizetarget = @mydbsizetarget output
SET @currdbsizetarget = CAST(@mydbsizetarget AS INT)
PRINT @comment + 'Size of replaced local database files for ' + @newdbname 
+ ' on drive ' + @DirName + ' is ' + CAST(@currdbsizetarget AS VARCHAR(30)) + ' MB'
END
END
ELSE IF (@dbname > '' AND @newdbname = '')
BEGIN
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @dbname)
BEGIN
-- Get total filesize of database in target directory on target server
SET @parmdefdbsizetarget = N'@pmydbsizetarget NUMERIC(18,2) output'
SET @getdbsizetarget = N'SELECT @pmydbsizetarget = (SUM(size)/128.0) FROM ' + 
'[' + @dbname + '].[dbo].[sysfiles] ' +
'WHERE SUBSTRING(filename,1,1) = ' + CHAR(39) + @DirName + CHAR(39)

-- Get size of database on target server
EXECUTE sp_executesql @getdbsizetarget, @parmdefdbsizetarget, @pmydbsizetarget = @mydbsizetarget output
SET @currdbsizetarget = CAST(@mydbsizetarget AS INT)
PRINT @comment + 'Size of replaced local database files for ' + @dbname
+ ' on drive ' + @DirName + ' is ' + CAST(@currdbsizetarget AS VARCHAR(30)) + ' MB'
END
END
ELSE
BEGIN
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db)
BEGIN
-- Get total filesize of database in target directory on target server
SET @parmdefdbsizetarget = N'@pmydbsizetarget NUMERIC(18,2) output'
SET @getdbsizetarget = N'SELECT @pmydbsizetarget = (SUM(size)/128.0) FROM ' + 
'[' + @db + '].[dbo].[sysfiles] ' +
'WHERE SUBSTRING(filename,1,1) = ' + CHAR(39) + @DirName + CHAR(39)

-- Get size of database on source server
EXECUTE sp_executesql @getdbsizetarget, @parmdefdbsizetarget, @pmydbsizetarget = @mydbsizetarget output
SET @currdbsizetarget = CAST(@mydbsizetarget AS INT)
PRINT @comment + 'Size of replaced local database files for ' + @dbname 
+ ' on drive ' + @DirName + ' is ' + CAST(@currdbsizetarget AS VARCHAR(30)) + ' MB'
END
END

-- Calculate if there is enough disk space for import of database, as compared to @spacelimit
IF OBJECT_ID('tempdb..#DriveSpace') IS NULL CREATE TABLE #DriveSpace (DriveName char(1),DriveSpace int)
TRUNCATE TABLE #DriveSpace
INSERT INTO #DriveSpace exec master..xp_fixeddrives
-- Calculate space usage, but add space for database removed
SELECT @dbspace = (cast(@currdbsize as numeric)/cast((DriveSpace + @currdbsizetarget) as numeric)) from #DriveSpace where DriveName = @DirName
IF @dbspace > @spacelimit
BEGIN
SET @customerrmess = @comment + 
'Not enough disk space to import database ' + 
@db + ', ' + 
CAST((@dbspace * 100) AS VARCHAR(30)) + 
' percent of free disk on ' + @DirName + ' would be used' + CHAR(13) + @comment + 
CAST(@currdbsize AS VARCHAR(20)) + ' MB disk space is required for this database on ' + @targetdir
IF @runrestore = 'Y'
BEGIN
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END
ELSE PRINT @customerrmess
END
ELSE
BEGIN
SET @StatusReport = @comment + 
'Enough space available to import database ' + 
@db + ', ' + 
CAST((@dbspace * 100) AS VARCHAR(30)) + 
' percent of free disk on ' + @DirName + ' would be used' + CHAR(13) + @comment + 
CAST(@currdbsize AS VARCHAR(20)) + ' MB disk space is required for this database on ' + @targetdir
PRINT @StatusReport
END

-- Print start of import message for current database
IF @newdbname = ''
BEGIN
IF @runrestore = 'Y'
BEGIN
PRINT 'Import of database ' + @db + ' has started'
PRINT 'All files for database ' + @db + ' will be placed in directory ' + @targetdir
END
ELSE
BEGIN
PRINT @comment + 'Generating script for import of database ' + @db
PRINT @comment + 'All files for database ' + @db + ' will be placed in directory ' + @targetdir
END
END
ELSE
BEGIN
IF @runrestore = 'Y'
BEGIN
PRINT 'Import of database ' + @db + ' has started - database is renamed to ' + @newdbname
PRINT 'All files for renamed database ' + @newdbname + ' will be placed in directory ' + @targetdir
END
ELSE
BEGIN
PRINT @comment + 'Generating script for import of database ' + @db + ' - database is renamed to ' + @newdbname
PRINT @comment + 'All files for renamed database ' + @newdbname + ' will be placed in directory ' + @targetdir
END
END

-- Initialize the physical device name and get the device names to builde restore statements.
SET @physical_device_name = ''
SET @devcount = 0
SET @valcount = 1

SET @parmdefdevice = N'@xphysical_device_name varchar(512) output'

WHILE@physical_device_name IS NOT NULL 
BEGIN
SET @sqlphysical =
N'SELECT DISTINCT 
@xphysical_device_name = MIN(bfam.physical_device_name)
FROM         
 ' + @sourcedbserver + 'msdb.dbo.backupset bs INNER JOIN
 ' + @sourcedbserver + 'msdb.dbo.backupfile bf ON bs.backup_set_id = bf.backup_set_id INNER JOIN
 ' + @sourcedbserver + 'msdb.dbo.backupmediafamily bfam ON bs.media_set_id = bfam.media_set_id INNER JOIN
 ' + @sourcedbserver + 'msdb.dbo.backupmediaset bmed ON bs.media_set_id = bmed.media_set_id
WHERE
 bs.backup_set_id = ' + cast(@backupid as varchar(50)) + ' AND bfam.physical_device_name > ''' + @physical_device_name + ''''

EXECUTE sp_executesql @sqlphysical, @parmdefdevice, @xphysical_device_name = @physical_device_name output

IF @physical_device_name IS NOT NULL 
BEGIN
-- Check existence of physical full backup file
-- PRINT @comment + 'Checking existence of physical full backup file ' + @physical_device_name + ' for database ' + @db
IF UPPER(@checkbackup) = 'Y'
BEGIN
INSERT INTO #FileExists exec master..xp_fileexist @physical_device_name
If NOT EXISTS (Select * From #FileExists Where File_Exists = 1)
BEGIN
SET @customerrmess = 'Backup file ' + @physical_device_name + ' does not exist for database ' + @db
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END
-- Empty file existence table
TRUNCATE TABLE #FileExists
END

-- Keep track of the sequence number of the file in order to generate the correct RESTORE statement
SET  @devcount = @devcount + @valcount
IF @devcount = 1 
BEGIN
-- If extension is 'ZBAK' assume backup is compressed 
     IF UPPER(RIGHT(@physical_device_name,4)) ='ZBAK'
BEGIN
-- Check SQLLitespeed extended procedures are present
IF NOT EXISTS(SELECT * FROM master.dbo.sysobjects (nolock)
         WHERE [name] = 'xp_backup_database' AND xtype='X')
BEGIN                                                 
SET @customerrmess = 'SQLLitespeed is not installed on SQL server instance ' + @@servername + ' - required for zipped backups'
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP   
END
IF @newdbname > '' SET @SSQL = 'exec master.dbo.xp_restore_database @database = ' + '[' + @newdbname + ']' + CHAR(13) + CHAR(10)
ELSE SET @SSQL = 'exec master.dbo.xp_restore_database @database = ' + '[' + @db + ']' + CHAR(13) + CHAR(10)

SELECT @mybackupfile = ',@filename = ' + CHAR(39) + CHAR(39) + @physical_device_name + + CHAR(39) + CHAR(39) + CHAR(13) + CHAR(10)
SET @SSQL =  @SSQL + @mybackupfile
SELECT @zipped = 'Y'
END
ELSE
BEGIN
IF @newdbname > '' SET @SSQL = 'RESTORE DATABASE ' + '[' + @newdbname + ']' + ' FROM ' + CHAR(13) + CHAR(10)
ELSE SET @SSQL = 'RESTORE DATABASE ' + '[' + @db + ']' + ' FROM ' + CHAR(13) + CHAR(10)

SELECT @mybackupfile = ' DISK = ' + CHAR(34) + @physical_device_name + CHAR(34) + CHAR(13) + CHAR(10)
SET @SSQL =  @SSQL + @mybackupfile
SELECT @zipped = 'N'
END
END
ELSE BEGIN
-- If extension is 'ZBAK' assume backup is compressed 
     IF @zipped = 'Y'
BEGIN
SELECT @mybackupfile = ',@filename = ' + CHAR(39) + CHAR(39) + @physical_device_name + + CHAR(39) + CHAR(39) + CHAR(13) + CHAR(10)
SET @SSQL =  @SSQL + @mybackupfile
END
ELSE
BEGIN
SELECT @mybackupfile = ',DISK = ' + CHAR(34) + @physical_device_name + CHAR(34) + CHAR(13) + CHAR(10)
SET @SSQL =  @SSQL + @mybackupfile
END
END
END
END

-- Initialize the logical and physical file names.
SET @logical_name = ''
SET @physical_name = ''
SET @logdevcount = 0
SET @logvalcount = 1
SET @SQLFULL = ''
SET @SQLMOVE = '' 

   /*Cursor for select of logical and physical files*/DECLARE @lognamecursor CURSOR
DECLARE @logcursorsql varchar(500)
DECLARE @mysetlogcursor nvarchar(1000)
SET @logcursorsql = 'SELECT logical_name, physical_name from ' + @sourcedbserver + 
'msdb.dbo.backupfile where backup_set_id = ' + cast(@backupid as varchar(50))

-- Open cursor containing list of database logical names.
SET @mysetlogcursor = 'SET @lognamecursor = CURSOR FOR ' + @logcursorsql + ' FOR READ ONLY; OPEN @lognamecursor'
EXEC sp_executesql @mysetlogcursor,N'@lognamecursor cursor OUTPUT', @lognamecursor OUTPUT

-- Fetch data from cursor containing list of database backups files for specific database being processed  
FETCH NEXT FROM @lognamecursor INTO @logical_name, @physical_name
WHILE @@FETCH_STATUS = 0
BEGIN
     BEGIN
IF @targetdir > '' -- target directory is always not blank
BEGIN
-- Put an ending backslash if missing
SELECT @omvand = REVERSE(@physical_name) 
SELECT @slashpos = PATINDEX('%\%', @omvand)
SELECT @mypartlen = @slashpos - 1
IF @newdbname > '' SELECT @physical_name = @targetdir + 'IMP_' + @newdbname + '_' + REVERSE(SUBSTRING(@omvand, 1, @mypartlen))
ELSE SELECT @physical_name = @targetdir + REVERSE(SUBSTRING(@omvand, 1, @mypartlen))
IF @newdbname > '' print @comment + 'New file name for renamed database ' + @newdbname + ' is ' + @physical_name
END

SET @logdevcount = @logdevcount + @logvalcount
IF @logdevcount = 1 BEGIN
-- If extension is 'ZBAK' assume backup is compressed 
     IF @zipped ='Y' SET @SQLMOVE = ',@with = ' + CHAR(39) + CHAR(39) + 'MOVE ' + CHAR(34) + @logical_name + CHAR(34) + ' TO ' + CHAR(34) + @physical_name + CHAR(34) + CHAR(39) + CHAR(39) + CHAR(13) + CHAR(10)
ELSE SET @SQLMOVE = ' MOVE ' + CHAR(34) + @logical_name + CHAR(34) + ' TO ' + CHAR(34) + @physical_name + CHAR(34) + CHAR(13) + CHAR(10)
END
ELSE BEGIN
-- If extension is 'ZBAK' assume backup is compressed 
     IF @zipped ='Y' SET @SQLMOVE = @SQLMOVE + ',@with = ' + CHAR(39) + CHAR(39) + 'MOVE ' + CHAR(34) + @logical_name + CHAR(34) + ' TO ' + CHAR(34) + @physical_name + CHAR(34) + CHAR(39) + CHAR(39) + CHAR(13) + CHAR(10)
ELSE SET @SQLMOVE = @SQLMOVE + ',MOVE ' + CHAR(34) + @logical_name + CHAR(34) + ' TO ' + CHAR(34) + @physical_name + CHAR(34) + CHAR(13) + CHAR(10)
END
END

-- Get next logical file to process
FETCH NEXT FROM @lognamecursor INTO @logical_name, @physical_name
     END 

SET @cmd = 'insert into #sourcebackups (name,database_name,type,phys,start) SELECT ' +  CHAR(39) + @SSQL + CHAR(39) + ',' + CHAR(39) + @db + CHAR(39) + 
 ',' + CHAR(39) + 'D' + CHAR(39) + ',' + CHAR(39) + @SQLMOVE + CHAR(39) + ',' + CHAR(39) + CAST(@backup_start_date AS VARCHAR(30)) + CHAR(39)

-- Execute command to place records in table to hold backup names 
-- for all transaction log backups from the last database backup
EXEC sp_executesql @cmd

     -- Close and deallocate logical file cursor for current database being processed
     CLOSE @lognamecursor
     DEALLOCATE @lognamecursor

-----------------------------------------------------------------------------------------------------------
-- Subsection 1B: LOG BACKUPS
-----------------------------------------------------------------------------------------------------------
-- Build command to place records in table to hold backup names for all 
-- transaction log backups from the last database backup
SET @cmd = 'insert into #sourcebackups (name,database_name,type,phys,start) SELECT physical_device_name,' + CHAR(39) + @db + CHAR(39) + 
 ',' + CHAR(39) + 'L' + CHAR(39) +  ',' + CHAR(39) + '' + CHAR(39) + 
 ',backup_start_date from ' + @sourcedbserver + 'msdb.dbo.backupset a join ' + @sourcedbserver + 'msdb.dbo.backupmediaset b on a.media_set_id = b.media_set_id join ' + 
 @sourcedbserver + 'msdb.dbo.backupmediafamily c on a.media_set_id = c.media_set_id ' +  
       ' where type= ' + CHAR(39) + 'L' + CHAR(39) + ' and backup_start_date >  @backup_start_dat and ' + 
 CHAR(39) + @db + CHAR(39) + ' = database_name order by backup_start_date asc'

-- Execute command to place records in table to hold backup names 
-- for all transaction log backups from the last database backup
EXEC sp_executesql @cmd,@params=N'@backup_start_dat datetime', @backup_start_dat = @backup_start_date

-- Check existence of log backup files
DECLARE trans_file_name cursor local for SELECT name from #sourcebackups where database_name = @db and type = 'L' order by id asc
-- Open cursor containing list of database transaction log backup files for the specific database being processed  
OPEN trans_file_name
-- Get first database backup for specific database being processed
FETCH next from trans_file_name into @logbakfilename
-- Process until no more log backup files exist for specific database being processed
WHILE @@FETCH_STATUS = 0
BEGIN
-- Check existence of physical transaction log backup file
-- PRINT @comment + 'Checking existence of physical transaction log backup file ' + @logbakfilename + ' for database ' + @db
IF UPPER(@checkbackup) = 'Y'
BEGIN
INSERT INTO #FileExists exec master..xp_fileexist @logbakfilename
IF NOT EXISTS (Select * From #FileExists Where File_Exists = 1)
BEGIN
SET @customerrmess = 'Transaction log backup file ' + @logbakfilename + ' does not exist for database ' + @db
RAISERROR(@customerrmess,16,1)
SET @ret = 1
GOTO CLEANUP
END
-- Empty file existence table
TRUNCATE TABLE #FileExists
END

-- get next log backup file name to process 
FETCH next from trans_file_name into @logbakfilename
END
-- close cursor
CLOSE trans_file_name
DEALLOCATE trans_file_name

-- Get next database to process
GETNEXTDB:
FETCH next from @dbcursor into @db
END
-- Close database cursor 
CLOSE @dbcursor
-----------------------------------------------------------------------------------------------------------
-- Section 2: BUILD RESTORE SCRIPTS FOR FULL BACKUPS AND TRANSACTION LOG BACKUPS
-----------------------------------------------------------------------------------------------------------
-- Process full backups and transaction logs and build scripts for all databases
open @dbcursor
-- Get first recod from database list cursor
FETCH next from @dbcursor into @db
WHILE @@FETCH_STATUS = 0
BEGIN
     -- Define cursor for all database and log backups for specific database being processed
     -- RESTORE BACKUPS FOR DATABASE ' + @db
     DECLARE backup_name cursor local for SELECT name, type, phys, start from #sourcebackups where database_name = @db order by id asc
-- Open cursor containing list of database backups for specific database being processed  
     OPEN backup_name
     -- Determine the number of different backups available for specific database being processed
     SELECT @cnt = count(*) from #sourcebackups where database_name = @db 
     -- Get first database backup for specific database being processed
     FETCH next from backup_name into @physical_device_name, @type, @phys, @currentbackupstart
     -- Set counter to track the number of backups processed
     SET @num_processed = 0
     -- Process until no more database backups exist for specific database being processed
     WHILE @@FETCH_STATUS = 0
     BEGIN
-- Increment the counter to track the number of backups processed
  SET @num_processed = @num_processed + 1
-- Is the number of database backup processed the same as the number of different backups available for DB?
  IF @cnt = @num_processed
-- If so, is the type of backup currently being processed a transaction log backup or a full backup?
    IF @type = 'L'
    BEGIN
-- Build restore command to restore the last transaction log
IF @newdbname > ''
      SELECT @cmd = 'RESTORE LOG ' + '[' + RTRIM(@newdbname) + ']' + CHAR(13) +
              ' FROM DISK = ' + CHAR(39) +  
                rtrim(substring(@physical_device_name,1,len(@physical_device_name))) + 
                CHAR(39) + CHAR(13) + CHAR(10) + ' WITH REPLACE'
ELSE
      SELECT @cmd = 'RESTORE LOG ' + '[' + RTRIM(@db) + ']' + CHAR(13) +
              ' FROM DISK = ' + CHAR(39) +  
                rtrim(substring(@physical_device_name,1,len(@physical_device_name))) + 
                CHAR(39) + CHAR(13) + CHAR(10) + ' WITH REPLACE'

SELECT @cmd2 = ''
    END
    ELSE
    BEGIN
-- Last backup was a full backup, not a transaction log backup (no logbackups exist)
-- Build restore command to restore the last database backup 
IF @zipped ='Y'
BEGIN
IF @overwritedb = 'Y' SELECT @cmd = @physical_device_name + ',@with = ' + CHAR(39) + 'REPLACE, RECOVERY' + CHAR(39) + CHAR(13)
ELSE SELECT @cmd = @physical_device_name + ',@with = ' + CHAR(39) + 'RECOVERY' + CHAR(39) + CHAR(13)
END
ELSE
BEGIN
IF @overwritedb = 'Y' SELECT @cmd = @physical_device_name + ' WITH REPLACE, RECOVERY, STATS=1, ' + CHAR(13)
ELSE SELECT @cmd = @physical_device_name + ' WITH RECOVERY, STATS=1, ' + CHAR(13)
END
SELECT @cmd2 = @phys
    END
  ELSE 
-- Current backup is not the last backup, so we do a NORECOVERY.
-- Check if the current backup being processed a transaction log backup or not!
    IF @type = 'L'
BEGIN
-- Build restore command to restore the current transaction backup, with no recovery
IF @newdbname > ''
      SELECT @cmd = 'RESTORE LOG ' + '[' + RTRIM(@newdbname) + ']' + CHAR(13) +
              ' FROM DISK = ' + CHAR(39) +  
               rtrim(substring(@physical_device_name,1,len(@physical_device_name))) + 
                 CHAR(39) + CHAR(13) + ' WITH REPLACE, NORECOVERY'
ELSE
      SELECT @cmd = 'RESTORE LOG ' + '[' + RTRIM(@db) + ']' + CHAR(13) +
              ' FROM DISK = ' + CHAR(39) +  
               rtrim(substring(@physical_device_name,1,len(@physical_device_name))) + 
                 CHAR(39) + CHAR(13) + ' WITH REPLACE, NORECOVERY'

SELECT @cmd2 = ''
END
    ELSE
BEGIN
-- Current backup being processed is a full backup, not a transaction log backup (log backups exist)
-- Build restore command to restore the currrent database backup, with no recovery
IF @zipped ='Y'
BEGIN
IF @overwritedb = 'Y' SELECT @cmd = @physical_device_name + ',@with = ' + CHAR(39) + 'REPLACE, NORECOVERY' + CHAR(39) + CHAR(13)
ELSE SELECT @cmd = @physical_device_name + ',@with = ' + CHAR(39) + 'NORECOVERY' + CHAR(39) + CHAR(13)
END
ELSE
BEGIN
IF @overwritedb = 'Y' SELECT @cmd = @physical_device_name + ' WITH REPLACE, NORECOVERY, STATS=1, ' + CHAR(13)
ELSE SELECT @cmd = @physical_device_name + ' WITH NORECOVERY, STATS=1, ' + CHAR(13)
END
SELECT @cmd2 = @phys
END
   -- Comment restore of system and demo databases
   IF @db IN ('master','model','msdb','tempdb','Northwind','pubs') 
BEGIN
      SET @cmd = '/* ' + CHAR(13) + @cmd + CHAR(13)
SET @cmd2 = @cmd2 + CHAR(13) +  + '*/'
END
   -- Generate the restore command and other commands for restore script
   IF @cmd2 > ''
BEGIN
SET @cmd = @cmd + @cmd2
END

   -- Run restore or print restore scripts?
   IF @runrestore = 'Y'
   BEGIN
   -- Execute restore from full backup and transaction log backups
IF @newdbname > ''
IF @currentbackupstart IS NOT NULL print 'Restoring database ' + @db + ' to database ' + @newdbname + ' from backup with start date ' + convert(char(24),@currentbackupstart,121)
ELSE
IF @currentbackupstart IS NOT NULL print 'Restoring database ' + @db + ' from backup with start date ' + convert(char(24),@currentbackupstart,121)

EXEC (@cmd)
  END
  ELSE
  BEGIN
   -- Print restore script for full backup and transaction log backups
IF @newdbname > ''
IF @currentbackupstart IS NOT NULL print @comment + 'Script for restoring database ' + @db + ' to database ' + @newdbname + ' from backup with start date ' + convert(char(24),@currentbackupstart,121)
ELSE
IF @currentbackupstart IS NOT NULL print @comment + 'Script for restoring database ' + @db + ' from backup with start date ' + convert(char(24),@currentbackupstart,121)

PRINT @cmd
PRINT 'GO'
  END

  -- Get next database backup to process
  FETCH next from backup_name into @physical_device_name, @type, @phys, @currentbackupstart
     END 
     -- Close and deallocate database backup name cursor for current database being processed
     CLOSE backup_name
     DEALLOCATE backup_name
  --Get next database to process
  IF @newdbname > ''
  PRINT @comment + 'Import of database ' + @db + ' to new dabase name ' + @newdbname + ' has completed'
  ELSE
  PRINT @comment + 'Import of database ' + @db + ' has completed'
  PRINT REPLICATE('-',LEN(@myHeader))
  FETCH next from @dbcursor into @db
END
-----------------------------------------------------------------------------------------------------------
-- CLEANUP
-----------------------------------------------------------------------------------------------------------
CLEANUP:
-- Close and deallocate cursor containing list of databases to process
IF Cursor_Status('variable', '@dbcursor') >= 0
BEGIN
CLOSE @dbcursor
DEALLOCATE @dbcursor
END
-- Close and deallocate cursor containing list of database logical names
IF Cursor_Status('variable', '@lognamecursor') >= 0
BEGIN
CLOSE @lognamecursor
DEALLOCATE @lognamecursor
END
-- Close and deallocate cursor containing list of backup files to process
IF Cursor_Status('local', 'backup_name') >= 0
BEGIN
CLOSE backup_name
DEALLOCATE backup_name
END
-- Close and deallocate cursor containing list of transaction log files to process
IF Cursor_Status('local', 'trans_file_name') >= 0
BEGIN
CLOSE trans_file_name
DEALLOCATE trans_file_name
END
-- Drop temporary tables
IF OBJECT_ID('tempdb..#sourcebackups') IS NOT NULL DROP TABLE #sourcebackups
IF OBJECT_ID('tempdb..#FileExists') IS NOT NULL DROP TABLE #FileExists
IF OBJECT_ID('tempdb..#DriveSpace') IS NOT NULL DROP TABLE #DriveSpace
SET QUOTED_IDENTIFIER OFF 
SET ANSI_NULLS ON
SET @ok = @ret
RETURN @ret
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating