server| backup | stored Procedure | recovery /* The process of backing up the database * *
if exists (
SELECT * from sysobjects
where Name= ' pr_backup_db ' and xtype= ' P '
)
Begin
drop proc pr_backup_db
End
Go
Create proc pr_backup_db
@flag varchar Out,
@backup_db_name varchar (128),
@filename varchar (1000)--path + file name
As
declare @sql nvarchar (4000), @par nvarchar (1000)
If not EXISTS (
SELECT * FROM Master. sysdatabases
where Name= @backup_db_name
)
Begin
Select @flag = ' db not exist '/* database does not exist */
Return
End
Else
Begin
If Right (@filename, 1) <> ' and charindex (' \ ', @filename) <>0
Begin
Select @par = ' @filename varchar (1000) '
Select @sql = ' BACKUP DATABASE ' + @backup_db_name + ' to disk= @filename with Init '
Execute sp_executesql @sql, @par, @filename
Select @flag = ' OK '
Return
End
Else
Begin
Select @flag = ' file type error '/* parameter @filename Input format error * *
Return
End
End
Go
Description: The pr_backup_db process is to back up your database
/* Create a function to get the file path * *
if exists (
SELECT * from sysobjects
where Name= ' Fn_getfilepath ' and xtype= ' FN '
)
Begin
Drop function Fn_getfilepath
End
Go
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
/* Process to restore the database * *
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
@flag varchar () out,/* The status of the process running flag, is the input parameters * *
@restore_db_name nvarchar (128),/* The name of the data to be restored * *
@filename nvarchar (260)/* backup file stored path + backup file name * *
As
DECLARE @proc_result tinyint/* Return system stored procedure xp_cmdshell run result * *
DECLARE @loop_time smallint/* Cycle times * *
Declare @max_ids the maximum number of IDs columns in the smallint/* @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 (100)
declare @sql_kill nvarchar (100)
/*
Determine the legality of the parameter @filename file format to prevent users from entering illegal file names such as 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 ' + @filename
EXEC @proc_result = Master. xp_cmdshell @sql_cmd, No_output
IF (@proc_result <>0)/* System stored procedure xp_cmdshell return code value: 0 (successful) or 1 (failure) */
Begin
Select @flag = ' not exist ' * backup file does not exist * *
Return//* Exit Process * *
End
/* Create a temporary table that holds a result set consisting of a 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),/* File physical name or operating system name * *
Type char (1),/* data file (D) or log file (L) * *
FileGroupName nvarchar (128),/* file group name containing file * *
[Size] Numeric (20,0),//current size (in bytes) * *
[MaxSize] Numeric (20,0)/* Maximum size allowed (in bytes) * *
)
[1] [2] Next page
