SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

User Stored Procedure for Backup of user databases

By Junior Galv√£o - MVP,

This script creates a stored Procedure for the user named P_BackupAllUsersDatabases.
Using the Select command in conjunction with the existing DBId and Name columns in the Sys.SysDatabase system view is creating a column named command, which is responsible for storing the instructions later used by the Backup Database command.
The result of this Select command is stored in a temporary table called #CommandBackupDatabases, which will later be using within the While execution block.
In this column named Command is specified a default storage location of the backup files defined with the corresponding backup name of concatenated with the. bak extension.
The Master, MSDB, Model, and TempDB system databases are not involved in this backup procedure.
The While execution block is responsible for performing the Backup command set for each database through the command column and its assigned value to a variable named @Command executed through the Exec() command.
The While execution block will run until all of the identified user databases have their backup process run, in the event that any error message stored procedure appears on screen in which line or part of code happened due Error.
Its execution form is quite simple, just use the Exec command accompanied by the name of the User stored Procedure P_BackupAllUsersDatabases, as shown in the example below:
Exec P_BackupAllUsersDatabases
Go

Total article views: 259 | Views in the last 30 days: 18
 
Related Articles
FORUM

Using alter command in Stored Procedure

Using alter command in Stored Procedure

FORUM

DBCC command execution History

When did a DBCC command executed in a DB

FORUM

SSIS error while executing in command line

SSIS error while executing in command line

FORUM

Execute stored Procedure from SSIS

Execute stored Procedure from SSIS

FORUM

Execute stored proc question!

Execute stored proc question!

Tags
 
Contribute