Stored procedures for database backup and recovery

Backup | Stored Procedures | recovery | data | database
if exists (
SELECT * from sysobjects
where Name= ' pr_backup_db ' and xtype= ' P '
)
Begin
drop proc pr_backup_db
End

Go

/* BACKUP DATABASE * *
Create proc pr_backup_db
@flag varchar (a) 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 ' + @backup_db_name + ' to disk= @filename 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 file path * *
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/* RESTORE DATABASE * *
@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 (4000)
/*
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) * *
)
/*
CREATE table variables, table structure is the same as temporary tables
Just two more columns,
Column IDs (from numbered columns),
Column File_path, storing the path to the file
*/
DECLARE @tem table (
IDS smallint identity,/* self-added numbering 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= ' + @filename + ' ")
/* The temporary table is imported into the table variable and the corresponding path is calculated.
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
The maximum number of IDs columns in the Select @max_ids =max (IDS)/* @tem Table * *
From @tem
While @loop_time <= @max_ids
Begin
Select @file_bak_path =file_path
From @tem where ids= @loop_time
Select @sql_cmd = ' dir ' + @file_bak_path
EXEC @proc_result = Master. xp_cmdshell @sql_cmd, No_output
/* System stored procedure xp_cmdshell return code value: 0 (Success) or 1 (failure)/
IF (@proc_result <>0)
Select @loop_time = @loop_time +1
Else
Break/* Did not find the original storage path of the data file before the backup, exit cycle * *
End
Select @master_path = '
If @loop_time > @max_ids
Select @flag_file = 1/* Pre-backup data file original store path exists * *
Else
Begin
Select @flag_file = 0/* Pre-backup data file original repository path does not exist * *
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 path is the same as the master database path.
Select @sql_sub = @sql_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 ' 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 ' disk= @filename with '
Select @sql = @sql + @sql_sub + ' replace '
Select @par = ' @db_name nvarchar (128), @filename nvarchar (260) '
Print @sql
Execute sp_executesql @sql, @par, @db_name = @restore_db_name, @filename = @filename
Select @flag = ' OK '/operation successful *
End
Else
Begin
SELECT @flag = ' File type error '/* parameter @filename Input format error * *
End




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

--Restore 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