Sql Server: Stored Procedure to backup databases

Sql Server: Stored Procedure to backup databases

Here's an example of Stored Procedure which uses cursor to backup all your databases.

Go to Master database

USE MASTER
GO;

And then create this stored Procedure. Change your path to where you want to do the backup.

-- SP using cursor to back up database

Create Procedure sp_databases_backup

As
BEGIN
set nocount on
set xact_abort on



Declare @name varchar(128)  --database name
Declare @path varchar(256)  --Path to backup database file
Declare @filename varchar(128) -- name of database backup file
Declare @fileDate varchar(20) --Date when the database was backup




SET @path = 'C:\backup\'
SET @fileDate = convert(varchar(20), getdate(),112)

DECLARE db_cursor CURSOR FOR
Select name from dbo.sysdatabases
Where name NOT IN ('master', 'model', 'msdb', 'tempdb')

OPEN db_cursor
FETCH NEXT from db_cursor INTO @name

WHILE @@Fetch_status = 0
BEGIN
SET @filename = @path+@name+'_'+@fileDate+'.Bak'
BACKUP DATABASE @name TO DISK =@filename

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

Deallocate db_cursor

END