/ * Back up the process of the database * /
IF exisss
SELECT *
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 asdeclare @sql nvarchar (4000), @ par nvarchar (1000) if not exists (SELECT * From master..sdatabasees where name = @ backup_db_name) Begin select @ flag = 'db not exist' / * Database does not exist * / returnendelsebegin 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' / * Parameters @filename Enter format error * / return end
Go
Description: The Pr_Backup_db process is backup your database.
/ * Create a function to get the file * / if EXISTS (Select * from sysobjects where name = 'fn_getfilepath' and xtype = 'fn') Begin Drop Function FN_GetFilePathendgo
create function fn_GetFilePath (@filename nvarchar (260)) returns nvarchar (260) asbegin 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_pathend
Go
/ * Recovery Database Process * / if EXISTS (Select * from sysobjects where name = 'pr_restore_db' and xtype = 'p') Begin Drop Proc Pr_Restore_Dbendgo
Create Proc PR_RESTORE_DB @flag varchar (20) OUT, / * The status flag running is input to the input parameter * / @restore_db_name nvarchar (128), / * To recover the data name * / @ filename nvarchar (260) / * Backup file Path Backup file name * / askECLARE @Proc_Result tinyint / * Return system stored procedure xp_cmdshell running result * / declare @LOOP_TIME smallint / * cycle number * / declare @max_ids smallint / * @ TEM table IDS column maximum number * / Declare @file_bak_path nvarchar (260) / * Original data inventory Path * / declare @flag_file bit / * file storage 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) / * Judgment Parameters @FileName File Format Legism to prevent user input Similar D: or C: / A / Other illegal file name parameters @FileName must have '/' and not with '/' end * / if right (@ filename, 1) <> '/' and charindex ('/', @ filename) <> 0begin 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 (success) or 1 (fail) * / begin SELECT @ flag = 'not exist' / * Backup file does not exist * / return / * exit process * / END / * Create a temporary table, save the result set of databases and log files included in the backup set * / Create table #tem (logicalname nvarchar (128), / * file logical name * / physicalname nvarchar (260), / * file physical name or operating system name * / type char (1), / * Data file (D ) Or log file (L) * / fileGroupname nvarchar (128), / * contains file group name * / [size] numeric (20, 0), / * Current size (in bytes) * / [maxsize ] Numeric (20, 0) / * Allowed Maximum Size (by byte) * /) / * Create a table variable, the table structure is basically two columns, column IDS (self-increment number column) , Column file_path, store the file path * / declare @
Tem Table (IDS Smallint Identity, / * Self-incremental Number * / LogicalName Nvarchar (128), PhysicalName Nvarchar (260), File_Path NVARCHAR (260), TYPE Char (1), FileGroupName Nvarchar (128)) Insert Into #tem Execute ('RESTORE FileListonly from Disk =' ') / * Imports a Temporary Table Demand and calculates 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) / * @ tem table The maximum number of columns ids * / 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 Returns: 0 (success) or 1 (failed) * / if (@Proc_Result <> 0) SELECT @ loop_time = @ loop_time 1 else break / * Did not find the backup data file original There is a storage path, exit loop * / end select @ Master_path = 'f @LOOP_TIME> @MAX_IDS SELECT @ Flag_File = 1 / * Backup Data file original storage path exists * / else begin select @ flag_file = 0 / * Backup Data file original storage path does not exist * / select @ Master_Path = dbo.fn_getfilepath (filename) from master ..sdatabases where name = 'master' end select @ SQL_SUB = '' / * type = 'd' is a data file, type = 'L' is a log file * / / * @ flag_file = 1 new database file or store In the original path, otherwise the storage path and the Master database path are the same * / select @ SQL_SUB = @ SQL_SUB 'MOVE' '' LogicalName '' 'To' '
' 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) / * gives the file number * / '. ' Right (PhysicalName, 3) / * Add to the file to the file, MDF or ndf * / ' ',' When 'l' Then @restore_db_name '_ log' convert (sysname, IDS) / * gives the file number * / '.' Right (PhysicalName, 3) / * Add to the compact name, MDF or ndf * / '', ' end from @tem select @ sql = 'RESTORE DATABASE @db_name fROM DISK = @ filename with' select @ sql = @ sql @ sql_sub 'replace' select @par = '@ db_name nvarchar (128), @ filename nvarchar (260)' / * Turn off related processes, import the corresponding process status into the temporary table * / select identity (int, 1, 1) IDS, SPID INTO #TEMP from master..xysprocesses 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 Drop Table #temp Execute SP_EXECUTESQL @ SQL, @ par, @ db_name = @ restaurantore_db_name, @ filename = @
FileName Select @ flag = 'ok' / * Operation Success * / endelsebegin select @ flag = 'file type error' / * Parameters @FileName Input format error * / EndGo
Run
- Backup Database Test_DatabaseDeclare @fl varchar (10) Execute Pr_backup_db @fl out, 'Test_Database', 'c: /test_database.bak'select @fl
- Restore the database, the input parameter error declare @fl varchar (20) EXEC PR_RESTORE_DB @fl out, 'sa', 'c: /' SELECT @fl
- Restore the database, that is, create a database Test_Database. Test_dbdeclare @fl varchar (20) exec pr_restore_db @fl out, 'test_db', 'c: /test_database.bak'select @fl
The above processes and functions are running successfully in the MS SQL2000. Since MS SQL7 does not support user-defined functions and table variables, you can use the function fn_getfilepath to rewrite it in MS SQL7.
