Stored Procedure for restoring SQL2000 database backup

This was accidentally found on the Internet. I tried it before and there was no problem with the backup, but I was prompted that my process could not be killed during restoration.

The Code is as follows:

[@ More @]/* database backup process */
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 (20) 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'/* The 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

Note: The pr_backup_db process backs up your database.

/* Create a function and obtain 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

/* Database restoration process */
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 (20) Out,/* indicates the running status of the process, which is the input parameter */
@ Restore_db_name nvarchar (128),/* Name of the data to be restored */
@ Filename nvarchar (260)/* path for storing the backup file + name of the backup file */
As
Declare @ proc_result tinyint/* return the system stored procedure xp_mongoshell running result */
Declare @ loop_time smallint/* number of cycles */
Declare @ max_ids smallint/* @ maximum number of IDS columns in the TEM table */
Declare @ file_bak_path nvarchar (260)/* original database storage path */
Declare @ flag_file bit/* file storage mark */
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 validity of the @ filename file format to prevent invalid file names such as D: or C://.
The parameter @ Filename must contain '/' and does not end '/'.
*/
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)/* Code returned by the system stored procedure xp_cmdshell: 0 (successful) or 1 (failed )*/
Begin
Select @ flag = 'not exist'/* the backup file does not exist */
Return/* exit process */
End
/* Create a temporary table and save the result set consisting of a list of databases and log files contained in the backup set */
Create Table # TEM (
Logicalname nvarchar (128),/* logical name of the file */
Physicalname nvarchar (260),/* Physical name of the file or operating system name */
Type char (1),/* data file (d) or log file (l )*/
Filegroupname nvarchar (128),/* Name of the file group containing the file */
[Size] numeric (20, 0),/* current size (in bytes )*/
[Maxsize] numeric (20, 0)/* maximum allowed size (in bytes )*/
)
/*
Create a table variable. The table structure is basically the same as that of a temporary table.
There are two more columns,
Column IDS (auto-increment Number Column ),
Column file_path: path for storing files
*/
Declare @ TEM table (
IDS smallint identity,/* auto-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 = ''' + @ filename + '''')
/* Import the temporary table to 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)/* @ maximum number of IDS columns in the 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_mongoshell return code value: 0 (successful) or 1 (failed )*/
If (@ proc_result <> 0)
Select @ loop_time = @ loop_time + 1
Else
Break/* The original storage path of the data file before backup is not found. Exit the loop */
End
Select @ master_path =''
If @ loop_time> @ max_ids
Select @ flag_file = 1/* The original storage path of the data file before backup exists */
Else
Begin
Select @ flag_file = 0/* The original storage path of the data file before backup does not exist */
Select @ master_path = DBO. fn_getfilepath (filename)
From master.. sysdatabases
Where name = 'master'
End
Select @ SQL _sub =''
/* Type = 'D' indicates the data file, and type = 'l' indicates the log file */
/* @ Flag_file = 1 when the new database file is stored in the original path, otherwise the storage path is the same as the master database path */
Select @ SQL _sub = @ SQL _sub + 'move ''' + logicalname + ''' '''
+ Case type
When 'd 'then case @ flag_file
When 1 then file_path
Else @ master_path
End
When 'l' then case @ flag_file
When 1 then file_path
Else @ master_path
End
End
+ Case type
When 'd 'then @ restore_db_name
+ '_ Data'
+ Convert (sysname, IDS)/* number the file */
+ '.'
+ Right (physicalname, 3)/* Add a suffix to the file, MDF or NDF */
+ ''','
When 'l' then @ restore_db_name
+ '_ Log'
+ Convert (sysname, IDS)/* number the file */
+ '.'
+ Right (physicalname, 3)/* Add a suffix to the file, MDF or NDF */
+ ''','
End
From @ TEM
Select @ SQL = 'Restore Database @ db_name from disk = @ filename'
Select @ SQL = @ SQL + @ SQL _sub + 'replace'
Select @ par = '@ db_name nvarchar (128), @ filename nvarchar (260 )'
/* Close the related process and import the Process status to the temporary table */
Select Identity (INT, 1, 1) IDs, spid
Into # temp
From master .. sysprocesses
Where dbid = db_id (@ restore_db_name)
If @ rowcount> 0 -- find the corresponding process
Begin
Select @ max_ids = max (IDS)
From # temp
Select @ loop_time = 1
While @ loop_time <= @ max_ids
Begin
Select @ SQL _kill = 'Kill '+ convert (nvarchar (20), spid)
From # temp
Where IDs = @ loop_time
Execute sp_executesql @ SQL _kill
Select @ loop_time = @ loop_time + 1
End
End
Drop table # temp
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

Go

-- Run

-- Backup database test_database
Declare @ FL varchar (10)
Execute pr_backup_db @ FL out, 'test _ database', 'c:/test_database.bak'
Select @ fl

-- Restore the database. The input parameter is incorrect.
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

The above processes and functions run successfully in MS SQL2000. Because Ms sql7 does not support user-defined functions and table variables, you can rewrite the fn_getfilepath function into a process in MS sql7, rewrite the table variable in pr_restore_db to a temporary table.