IF exists (select * from sysobjectswhere name = 'pr_backup_db' and xtype = 'p') begindrop Proc PR_BACKUP_DBEND
Go
/ * Backup database * / create proc PR_BACKUP_DB @ flag varchar (10) OUT, @ backup_db_name varchar (128), @ filename varchar (1000) - Path file name asdeclare @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, @ filenameselect @ flag =' ok'go
If EXISTS (SELECT * from sysobjectswhere name = 'fn_getfilepath' and xtype = 'fn') begindrop function fn_getfilepathendgo
/ * Create function to give to give the file path * / create function fn_GetFilePath (@filename nvarchar (260)) returns nvarchar (260) asbegindeclare @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
IF exists (select * from sysobjectswhere name = 'pr_restore_db' and xtype = 'p') begindrop Proc Pr_Restore_DBENDGO
Create Proc PR_RESTORE_DB / * Recovery Database * / @ 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 * / 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 list * / declare @file_bak_path nvarchar (260) / * Original data stock 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 (4000) / * Judgment Parameters @FileName file format legality to prevent user input Similar D: or C: / A / etc. Illegal file name parameter @FileName must have '/' and not with '/' end * / if right (@ filename, 1) <> / 'and charindex (' / ', @ filename) <> 0begin select @SQL_CMD = 'DIR' @ filenameeexec @Proc_Result = MASTER..XP_CMDSHELL @ SQL_CMD, NO_O_O_OUTPUTIF (@Proc_Result <> 0) / * System stored procedure xp_cmdshell return code value: 0 (success) or 1 (failed) * / beg ISELECT @ flag = 'NOT EXIST' / * Backup file does not exist * / return / * Exit Procedure * / 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), / * Document logical name * / Physicaln AME NVARCHAR (260), / * File Physical Name or Operating System Name * / Type Char (1), / * Data File (D) or Log File (L) * / FileGroupName NVARCHAR (128), / * Contains files File group name * / [size] numeric (20, 0), / * Current size (in byte) * / [maxsize] numeric (20, 0) / * Allowed maximum size (in bytes) * /) / * Create a table variable, the table structure is basically two columns, column IDS (self-increment number), column file_path, store files * / declare @tem table (IDS Smallint Identity, / * Self-increment 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 into the table variable, and calculates the corresponding path * / insert Into @tem (LogicalName, PhysicalName, File_Path, Type, FileGroupname "Select SELECT LogicalName, PhysicalName, dbo.fn_GetFilePath (PhysicalName), Type, FileGroupName from #temif @@ rowcount> 0 begindrop table #temendselect @ loop_time = 1select @ max_ids = max (ids) / * ids @ tem maximum number of columns in the table * / from @temwhile @loop_time <= @ max_idsbeginselect @ file_bak_path = file_path from @tem where ids = @ loop_timeselect @ sql_cmd = 'dir' @ file_bak_pathEXEC @proc_result = master..xp_cmdshell @ sql_cmd, no_output / * xp_cmdshell system stored procedure return code values: 0 (success) or 1 (failed) * / if (@Proc_Result <> 0) SELECT @ loop_time = @ loop_time 1 elsebreak / * Did not find a backup data file original storage path, exit loop * / endselect @ master_path = ' 'IF @LOOP_TIME> @max_ids select @ flag_file = 1 / * Backup Data file original storage path existing * / elsebeginselect @ FLAG_FILE = 0 / * Backup Data file The original storage path does not exist * / select @ Master_Path = Dbo. FN_GETFILEPATH (filename) from master..xysdatabases where name = 'master'endselect @SQL_SUB =' '/ * type =' d 'is a data file, type =' L 'is a log file * // * @ flag_file = 1 The database file is still stored in the original path, otherwise the storage path is like the Master database path * / s elect @ 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_filewhen 1 then File_path Else @master_path end end case type d h @restore_db_name '_' logicalname '
_data.mdf '', 'when' l 'then @restore_db_name ' _ ' LogicalName ' _ log.ldf '', 'endfrom @temselect @ sql =' RESTORE DATABASE @db_name FROM DISK = @ filename with 'select @ sql = @ sql @ sql_sub 'replace'select @par =' @ db_name nvarchar (128), @ filename nvarchar (260) 'print @sqlexecute sp_executesql @ sql, @ par, @ db_name = @ restore_db_name, @ filename = @ filenameselect @ flag =' ok '/ * operation is successful * / endelsebeginSELECT @ flag =' file type error '/ * input parameter @filename malformed * / end-- 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
