Back up all data in SQL Server at a one-time

This article is originally from an online article, but there are some problems with the original stored procedures, this article has been validated and further modified to increase the backup time automatically increase the backup date, the article reads as follows:

Stored Procedures for backup processing

Set ANSI_NULLS on

SET QUOTED_IDENTIFIER ON

Go

/*--Back up all databases

The backed up file name is the database name + Date +.bak

All user databases (or list of specified databases)

Ready to be assigned to the specified directory.

/*--Call Example

--Back up all user databases

exec p_backupdb @bkpath = ' D: ', @dbname = '

--Backing up the specified database

EXEC p_backupdb @bkpath =d: ', @dbname = ' database name '

--*/

CREATE proc [dbo]. [P_backupdb]

@bkpath nvarchar = ' D: ',--backup file directory, do not specify use SQL default backup directory

@dbname nvarchar (4000) = '--List of database names to be backed up, and no backup of all user databases

As

DECLARE @sql varchar (8000)

DECLARE @strdate NVARCHAR (200)

Set @strdate = Convert (NVARCHAR), 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 + '

--Get the list of databases to back up

If IsNull (@dbname, ') = '

Declare TB cursor Local for

Select name from Master. sysdatabases where name isn't in (' master ', ' tempdb ', ' model ', ' msdb ')

Else

Declare TB cursor Local for

Select name from Master. sysdatabases

Where name isn't 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