SQL o Database Backup

If exists (
Select * From sysobjects
Where name = 'pr _ backup_db 'and xtype = 'P'
)
Begin
Drop proc pr_backup_db
End

Go

/* Back up the database */
Create proc pr_backup_db
@ Flag varchar (10) 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 and obtain 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 (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 (4000)
/*
Determine the validity of the @ filename file format to prevent invalid file names such as D: Or c: \ \.
The parameter @ Filename must contain '\' and cannot 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 stored procedure xp_mongoshell returns Code Value: 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 + '_' + logicalname + '_ data. MDF '','
When 'l' then @ restore_db_name + '_' + logicalname + '_ log. ldf '','
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 )'
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 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