Go MS SQL database backup and recovery stored procedures

Using the following sections of code and revelations to fulfill your needs, write down this code:

if exists (
SELECT * from sysobjects
where Name= ' pr_backup_db ' and xtype= ' P '
)
Begin
drop proc pr_backup_db
End
Go
/* Back up the database */
Create proc pr_backup_db
@flag varchar (ten) out,
@backup_db_name varchar (128),
@filename varchar (1000)--path + file name
As
declare @sql nvarchar (4000), @par nvarchar (1000)
Select @par = ' @filename varchar (1000) '
Select @sql = ' BACKUP DATABASE '[email protected]_db_name+ ' to [e-mail protected] with Init '
Execute sp_executesql @sql, @par, @filename
Select @flag = ' OK '
Go

if exists (
SELECT * from sysobjects
where Name= ' Fn_getfilepath ' and xtype= ' FN '
)
Begin
Drop function Fn_getfilepath
End
Go
/* Create a function to get the path to the file */
Create function Fn_getfilepath (@filename nvarchar (260))
Returns nvarchar (260)
As
Begin
declare @file_path nvarchar (260)
declare @filename_reverse nvarchar (260)
Select @filename_reverse =reverse (@filename)
Select @file_path =substring (@filename, 1,len (@filename) +1-charindex (' \ ', @filename_reverse))
Return @file_path
End

Go

if exists (
SELECT * from sysobjects
where Name= ' pr_restore_db ' and xtype= ' P '
)
Begin
drop proc pr_restore_db
End
Go
CREATE proc pr_restore_db/* Recover database */
@flag varchar out,/* status flag for process run, input parameter */
@restore_db_name nvarchar (128),/* Name of data to restore */
@filename nvarchar (260)/* Path of backup file + backup file name */
As
DECLARE @proc_result tinyint/* Returns the system stored procedure xp_cmdshell run result */
DECLARE @loop_time smallint/* Cycle times */
DECLARE @max_ids smallint */* The maximum number of IDs columns in the @tem table */
declare @file_bak_path nvarchar (260)/* Original database storage path */
DECLARE @flag_file bit/* File Store flag */
declare @master_path nvarchar (260)/* Database Master file path */
declare @sql nvarchar (4000), @par nvarchar (1000)
declare @sql_sub nvarchar (4000)
declare @sql_cmd nvarchar (4000)
/*
Determine the legitimacy of the parameter @filename file format to prevent users from entering illegal file names like D: or C:\a\
Parameter @filename must have ' \ ' and not end with ' \ '
*/
If Right (@filename, 1) <> ' \ ' and charindex (' \ ', @filename) <>0
Begin
Select @sql_cmd = ' dir '[email protected]
EXEC @proc_result = Master: xp_cmdshell @sql_cmd, No_output
IF (@proc_result <>0)/* System stored procedure xp_cmdshell return code value: 0 (Success) or 1 (failed) */
Begin
Select @flag = ' not exist '/* * backup file does not exist */
Return/* Exit process */
End
/* Create a temporary table to hold a result set consisting of the list of databases and log files contained in the backup set */
CREATE TABLE #tem (
LogicalName nvarchar (128),/* The logical name of the file */
PhysicalName nvarchar (260),/* The physical name of the file or the operating system name */
Type char (1),/* data file (D) or log file (L) */
FileGroupName nvarchar (128),/* file group name of the containing file */
[Size] Numeric (20,0),/* Current size (in bytes) */
[MaxSize] Numeric (20,0)/* Maximum allowable size in bytes */
)
/*
Create a table variable with a table structure that is basically the same as a temporary table
It's two more columns,
Column IDs (self-increment number column),
Column File_path, the path where the file is stored
*/
DECLARE @tem table (
IDS smallint identity,/* self-increment number column */
LogicalName nvarchar (128),
PhysicalName nvarchar (260),
File_path nvarchar (260),
Type char (1),
FileGroupName nvarchar (128)
)
INSERT INTO #tem
Execute (' Restore FILELISTONLY from disk= '[email protected]+ "")
/* Import the temporary table into the table variable and calculate the corresponding path */
Insert into @tem (logicalname,physicalname,file_path,type,filegroupname)
Select Logicalname,physicalname,dbo.fn_getfilepath (physicalname), Type,filegroupname
From #tem
If @ @rowcount >0
Begin
drop table #tem
End
Select @loop_time =1
Select @max_ids =max (IDS)/* The maximum number of IDs columns for the @tem table */
From @tem
While @loop_time <[email protected]_ids
Begin
Select @file_bak_path =file_path
From @tem where[email protected]_time
Select @sql_cmd = ' dir '[email protected]_bak_path
EXEC @proc_result = Master: xp_cmdshell @sql_cmd, No_output
/* System stored procedure xp_cmdshell return code value: 0 (Success) or 1 (failed) */
IF (@proc_result <>0)
Select @[email protected]_time+1
Else
Break/* Did not find the original storage path of the data file before backup, exit the loop */
End
Select @master_path = "
If @loop_time > @max_ids
Select @flag_file = 1/* Existing storage path for data files before backup */
Else
Begin
Select @flag_file = 0/* The original storage path of the data file does not exist before backup */
Select @master_path =dbo.fn_getfilepath (filename)
From Master. sysdatabases where name= ' master '
End
Select @sql_sub = "
/*type= ' d ' is a data file, type= ' l ' is a log file */
/* @flag_file = 1 o'clock the new database file is still stored in the original path, otherwise the storage path is the same as the master database path */
Select @[email protected]_sub+ ' move ' +logicalname+ ' to '
+case type
When the ' d ' then case @flag_file
When 1 then File_path
else @master_path
End
When the ' l ' then case @flag_file
When 1 then File_path
else @master_path
End
End
+case type
When the ' d ' then @restore_db_name + ' _ ' +logicalname+ ' _data.mdf ', '
When ' l ' then @restore_db_name + ' _ ' +logicalname+ ' _log.ldf ', '
End
From @tem
Select @sql = ' RESTORE DATABASE @db_name from[email protected]With '
Select @[email protected][email protected]_sub+ ' replace '
Select @par = ' @db_name nvarchar (+), @filename nvarchar (260) '
Print @sql
Execute sp_executesql @sql, @par, @[email Protected]_db_name,@[email protected]
Select @flag = ' OK '/* Operation succeeded */
End
Else
Begin
SELECT @flag = ' file type ' ERROR '/* parameter @filename Input format incorrect */
End

--Backup Database Test_database
DECLARE @fl varchar (10)
Execute pr_backup_db @fl out, ' test_database ', ' C:\test_database.bak '
Select @fl

--Recover database, input parameter error
DECLARE @fl varchar (20)
EXEC pr_restore_db @fl out, ' sa ', ' \ C '
Select @fl

--Restore the database, that is, create a copy of the database Test_database test_db
DECLARE @fl varchar (20)
EXEC pr_restore_db @fl out, ' test_db ', ' C:\test_database.bak '
Select @fl

Go MS SQL database backup and recovery stored procedures