To backup all database in an instance we create a stored procedure, and later we will create a SQL Agent job to automate the Backup using stored procedure we created,
In the given stored procedure we will declare variables,use some Functions like Getdate() etc... and you can download our scripts from the download link given below,
Let's start with coding
Step 1: Open SSMS,
Step 2: Open a new query window
Step 3: Download the SQL file here
/*----------------------- CREATE PROCEDURE USP_FULL_DBBACKUP -----------------------------*/
IF OBJECT_ID('USP_FULL_DBBACKUP') IS Not Null
Drop Proc USP_FULL_DBBACKUP
Go
Create Procedure USP_FULL_DBBACKUP
As
Begin
Set NoCount On
/* Declaring required Variables */
Declare @path varchar(255) -- Set Backup path
Declare @date varchar(20)
Declare @time varchar(20)
Declare @datetime varchar(20)
Declare @dbname sysname
-- Declaring a Cursor
Declare dbcursor cursor
for
Select name from sys.databases where database_id > 6
-- Open Cursor
Open dbcursor
Fetch next from dbcursor into @dbname
-- Starting Loop
While @@FETCH_STATUS = 0
Begin
Set @time = REPLACE(Convert(varchar(20),Getdate(),108),':','-')
Set @date = REPLACE(Convert(varchar(20),Getdate(),102),'.','-')
Set @datetime = @date+'_'+@time
Set @path = 'D:\Backup\'+@dbname+@datetime+'.BAK'
-- Backup Database
Backup Database @dbname
To
Disk = @path
with stats = 10
Fetch next from dbcursor into @dbname
END -- While Loop end
END -- Procedure End
/*----------------------- CREATE PROCEDURE USP_FULL_DBBACKUP -----------------------------*/
/* --------------------------- Executing Stored Procedures ------------------------*/
Exec USP_FULL_DBBACKUP
/* --------------------------- Executing Stored Procedures -------------------------*/
/* -------------------------------- Closing Cursor --------------------------------------*/
Close Dbcursor
Deallocate Dbcursor
/* -------------------------------- Closing Cursor ---------------------------------------*/
Download Scripts
Download Txt File : Click Here to Backup all databases using stored procedure
Download SQL File: Click Here to Backup all databases using stored procedure
This is dummy text. It is not meant to be read. Accordingly, it is difficult to figure out when to end it. But then, this is dummy text. It is not meant to be read. Period.
ConversionConversion EmoticonEmoticon