Programs

SQL_STORED_PROCEDURE - prc_restore_database

Main program

Name	prc_restore_database
Type_desc	SQL_STORED_PROCEDURE
Comment	SQLServer Stored Procedure PURPOSE Restore a database from it's latest full-, differential- and log-backupfiles on a given dumpdirectory. Backups cannot be of the type copy_only, because they are not part of a backupchain. Please run this script with sufficient restore permissions. The name of the database must be in the leading part of all backupfile names. The restore is done "WITH REPLACE", so an existing destination database will be overwritten. Optionally, a new (destination) database name can be supplied to restore to another database than the original. The destination database can be OFFLINE or in READ_ONLY (STANDBY) mode before the restore operation. Before the full restore operation begins, all sessions are killed in the destination database, except for 'sa', using the dbo.prc_kill_sessions procedure in the local database. After the restore operation the destination database is recovered (left operational). PARAMETERS - 1 @dbname NVARCHAR(128) Name of the database. - 2 @backup_path VARCHAR(265) Path to folder holding all the database backup files. - 3 @subdir BIT 0/1 (default) Backup files are on subdirectory with database name when value = 1 (default). Value 0 means not on subdirectory. - 4 @new_dbname NVARCHAR(128) Optional name of the destination database (must be different from the original database). - 5 @option VARCHAR(20) 'RESTORE' (default) Execute restore. 'SCRIPT' Script restore. The script is presented in grid lines. 'REPORT' Report backup files. The list is presented in grid lines. EXAMPLES -- 1) Restore the database from it's latest backup files and recover the database (leave it operational). EXEC dbo.prc_restore_database @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\backup'; -- 2) Script restore the database from it's latest backupfiles and recover the database (leave it operational). EXEC dbo.prc_restore_database @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\backup', @option='SCRIPT'; -- 3) Report all backupfiles of the database and mark latest backup files. EXEC dbo.prc_restore_database @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\backup', @option='REPORT'; -- 4) Restore a database from it's latest backup files to a new database and recover that database (leave it operational). EXEC dbo.prc_restore_database @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\backup', @new_dbname='DBGERRIT_NEW'; -- 5) Script restore a database from it's latest backup files to a new database and recover that database (leave it operational). EXEC dbo.prc_restore_database @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\backup', @new_dbname='DBGERRIT_NEW', @option='SCRIPT'; USED OBJECTS - dbo.fun_sqlversion (UDF in local database) - dbo.fun_FolderExist (UDF in local database) - dbo.fun_filepath (UDF in local database) - dbo.fun_filename (UDF in local database) - dbo.prc_kill_sessions (SP in local database) - sys.dm_os_enumerate_filesystem SQLSERVER CONFIGURATION - OLE Automation has to be enabled. HISTORY 2011-10-12 - Created procedure 2012-04-25 - Use xp_dirtree instead of xp_cmdshell 2013-11-06 - Build in SQL version independency 2014-11-24 - Made suitable for sql2014 2014-12-15 - PRINT 'OK' after restore database with recovery omitted. - PRINT 'ERROR: ' before "Failed to execute sql - " 2014-12-16 - Changed cursor my_cur1 and my_cur2 to LOCAL READ_ONLY 2015-11-25 - Replaced procedure prc_isdir to function fun_FolderExist - Introduced table @header2 for new header layout in version 2014-SP1 2016-09-15 - Can now restore from splitted full backupfiles - Ignore copy_only backupfiles 2016-11-15 - Accepted SQL version 13.0 - Introduced RAISERROR on every fail 2016-12-28 - Fixed bug: Mark splitted files failed on backups with same FirstLSN 2018-05-08 - Adapted SQL version 14.0 2020-11-23 - Adapted SQL2019 2023-03-13 - Removed compliancy for versions SQL2000 (9.0), SQL2008 (10.0) and SQL2008R2 (10.5) - Added compliancy for version SQL2022 (16.0) - Renamed parameter @dmpdir to @backup_path 2026-03-29 - Removed compliancy for versions below SQL2022 (16.0) - Added minversion tag (16) 2026-04-07 - Re-added compliancy for versions SQL2014-SP1 (12.0) until SQL2022 (16.0) - Renamed procedure from prc_restore_db to prc_restore_database - Use temp tables for backup header info and filelist info - Changed parameter @new_dbname to @dest_dbname - Changed parameter @bakdir to @backup_path - Added parameter @subdir 2026-04-08 - Procedure prc_restore_database now includes all functionality of procedure prc_recover_database. - Changed parameter @dest_dbname to @new_dbname 2026-04-10 - Fixed bug: some commands have no database name - Output script in grid lines - Reduced number of remark lines in output TAGS <program> <description>Restore a database from it's latest backupfiles</description> <generic>1</generic> <minversion>12</minversion> <author>Gerrit Mantel</author> <created>2011-10-12</created> <lastmodified>2026-04-10</lastmodified> </program>
Description	Restore a database from it's latest backupfiles
Minversion	12.0
Generic	1
Author	Gerrit Mantel
Created	2011-10-12 00:00:00
Lastmodified	2026-04-10 00:00:00
Xp_cmdshell	0
Ole_automation	0
Subprogram_cnt	5
Sourcecode

Sub programs