Ms SQL database backup and recovery stored procedures

If nothing happens today, write the database backup and recovery stored procedures (to share with you)

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

Posted on aierong reading (3070) Comments (13) EDIT favorite to 365key category: DB
Feedback:

# Re: ms SQL database backup and recovery stored procedures | ahgood thank you!
# Re: ms SQL database backup and recovery stored procedures | Riker is good, I first try
# Re: ms SQL database backup and recovery Stored Procedure | hawk6224if @ 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

Is it reversed ??

If @ rowcount> 0 begin drop table # TEM end does not need to be judged
# Re: ms SQL database backup and recovery stored procedures | aierongto: hawk6224

No errors

For more details, see the following:

Http://www.cnblogs.com/aierong/archive/2004/05/20/10504.html
# Re: ms SQL database backup and recovery Stored Procedure | rrset @ sqlstr = "select * from" + @ tabname + "where" + @ rowleftstr + "=" + @ infoleftstr
If exists (Exec (@ sqlstr ))
Begin
...
End

Where is the above Code wrong?
# Re: ms SQL database backup and recovery Stored Procedure | aierongto: rr

This is wrong.
If exists (Exec (@ sqlstr ))

# Re: ms SQL database backup and recovery stored procedures | rraierong Hello:
Ask for advice! Ask for advice!
My idea is to pass the table name, field name, and field value to the process so that the process can determine the existence of records. What should I do?
I am in urgent need. Please help me. Thank you!
# Re: ms SQL database backup and recovery Stored Procedure | aierongto: rr

You can use output parameters.

By the way
Where do you work?

I'm in Guangzhou
# Re: ms SQL database backup and recovery stored procedures | rr I'm in Shenzhen :) My QQ: 6456136
I won't do anything about how to use output parameters.
Can I provide the code sample? Thank you!
# Re: ms SQL database backup and recovery stored procedures | RR is mainly like the table name, field name and other database object names can be replaced with variables ?????
# Re: ms SQL database backup and recovery Stored Procedure | aierong

Use pubs
Go
Declare @ SQL varchar (100)
Declare @ tablename varchar (100)
Select @ tablename = 'job'
Select @ SQL = 'select [job_id], [job_desc], [min_lvl], [max_lvl] From '+ @ tablename
Exec (@ SQL)
Select @ rowcount

# Re: ms SQL database backup and recovery Stored Procedure | aieronguse pubs
Go
Declare @ SQL varchar (100)
Declare @ tablename varchar (100)
Select @ tablename = 'job'
Select @ SQL = 'select [job_id], [job_desc], [min_lvl], [max_lvl] From '+ @ tablename
Exec (@ SQL)
If @ rowcount> 0
Begin

End