/ * 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' / * 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' / * Parameters @FileName Input Format Error * / Return End End Go Description: The pr_backup_db process is backup your database / * creation function, get the files * / 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 / * Recovery Database Process * / if EXISTS (SELECT * from sysobjects where name = 'pr_restore_db' and xtype = 'p'
Begin Drop Proc PR_RESTORE_DB END Go CREATE PR_RESTORE_DB @flag varchar (20) OUT, / * The status flag running is the input parameter * / @restore_db_name nvarchar (128), / * To recover the data name * / @filename nvarchar (260) / * Backup file storage path Backup file name * / as declare @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 stockpath * / 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 Parameter @FileName file format legality to prevent user input D: or C: \ A \ et al illegal file name parameter @FileName must have '\' and not with '\' 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) / * System Store XP_CMDSHELL Returns: 0 (success ) Or 1 (failed) * / 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 (by byte) * / [maxSize] Numeric (20, 0) / * Allowed maximum size (byte) * /) / * Create a table variable, table structure and temporary table basics The same is more than two columns, column IDs (self-incremental number columns), column file_path, path of files * / 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 =' @ filename ') / * Imports a temporary table into the table variable, 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 = ids 1 select @ max_ids = max (ids) / * @ tem maximum column of the table number * / 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 (failed) * / if (@Proc_Result <> 0) SELECT @ loop_time = @ loop_time 1 else Break / * No Backup Data file original storage path , Exit loop * / end select @ master_path = 'if @LOOP_TIME> @MAX_IDS SELECT @ Flag_File = 1 / * Backup Data file original storage path existing * / else begin select @flag_fil E = 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 'Yes, type =' L 'is a log file * / / * @ flag_file = 1 When a new database file is stored 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' Ten Case @flag_file by @ File_path Else @
Master_path end when 'l' kil_path Else @master_path end end case type gen 'd' @restore_db_name '_ data' convert (sysname, IDS) / * gives file number * / '. ' Right (PhysicalName, 3) / * Add to the reput name, mdf or ndf * / ', 'when' l 'Then @restore_db_name ' _ log ' convert (sysname, IDS) / * to the file number * / '.' Right (PhysicalName, 3) / * Added to the file to the reput 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)' / * Close related processes, import the corresponding process status into a 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 end drop Table #TEMP EXECUTE SP_EXECUTESQL @ SQL, @ par, @ db_name = @ restaurantore_db_name, @ filename = @ filename select @ flag = 'ok' / * operation success * / end else begin select @ flag = 'file type error' / * Parameters @FileName Enter format Error error * / End Go - Run - Backup Database Test_Database Decute @fl Varchar (10) Execute Pr_Backup_DB @fl out, 'Test_Database', 'C: \ Test_Database.bak' SELECT @
