The original Article is from the previous article on the Internet, but there are some problems with the original storage process. This article has been verified and further corrected, and the backup date is automatically added when backup is added. The content of this article is as follows:
Stored Procedure of backup Processing
Set ansi_nulls on
Set quoted_identifier on
Go
/* -- Back up all databases
The backup file name is database name + date +. Bak
Set all user databases (or the specified database list)
To the specified directory.
/* -- Call example
-- Back up all user Databases
Exec p_backupdb @ bkpath = 'd: \ ', @ dbname =''
-- Backup a specified database
Exec p_backupdb @ bkpath = D: \ ', @ dbname = 'database name'
--*/
Create proc [DBO]. [p_backupdb]
@ Bkpath nvarchar (260) = 'd: \ ', -- directory of the backup file. If this parameter is not specified, the default SQL Backup Directory is used.
@ Dbname nvarchar (4000) = ''-- List of database names to be backed up. If this parameter is not specified, all user databases are backed up.
As
Declare @ SQL varchar (8000)
Declare @ strdate nvarchar (200)
Set @ strdate = convert (nvarchar (10), getdate (), 120)
Set @ strdate = Replace (@ strdate ,'-','')
-- Check parameters
If isnull (@ bkpath, '') =''
Begin
Select @ bkpath = rtrim (reverse (filename) from Master .. sysfiles where name = 'master'
Select @ bkpath = substring (@ bkpath, charindex ('\', @ bkpath) + 1,4000)
, @ Bkpath = reverse (substring (@ bkpath, charindex ('\', @ bkpath), 4000) + 'backup \'
End
Else if right (@ bkpath, 1) <> '\' set @ bkpath = @ bkpath + '\'
-- Obtain the list of databases to be backed up.
If isnull (@ dbname, '') =''
Declare TB cursor local
Select name from Master .. sysdatabases where name not in ('master', 'tempdb', 'model', 'mdb ')
Else
Declare TB cursor local
Select name from Master .. sysdatabases
Where name not in ('master', 'tempdb', 'model', 'msdb') and (name like '%' + @ dbname + '% ')
-- Backup Processing
Open TB
Fetch next from TB into @ dbname
While @ fetch_status = 0
Begin
Set @ SQL = 'backup database' + @ dbname
+ 'To disk = ''' + @ bkpath + @ dbname + '_' + @ strdate
+ '. Bak' with format'
Exec (@ SQL)
Fetch next from TB into @ dbname
End
Close TB
Deallocate TB
Go
You are free to take care of my personal station: http://www.wjg121.cn