One-time backup of all data in SQLServer

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: backup processing Stored Procedure setANSI_NULLSONsetQUOTED_IDENTIFIERONgo * -- backup all database backup file names are data

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: backup processing stored procedure set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go/* -- backup all database backup file names are data

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: ', -- specifies the directory where backup files are stored. 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

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

,