DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to build a dynamic sproc for database backup?

Execute the following Microsoft SQL Server 2008 T-SQL database scripts in Management Studio Query Editor to demonstrate the architecture and use of dynamic stored procedure for database backup.

-- SQL Server T-SQL full database backup basic syntax
BACKUP DATABASE [AdventureWorks2008] TO  DISK = N'D:\backup\AdventureWorks2008\aw8.bak'
GO
-- SQL Server T-SQL full database backup syntax with options
BACKUP DATABASE [AdventureWorks2008] TO  DISK = N'D:\backup\AdventureWorks2008\aw8.bak'
WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2008-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
------------
-- SQL Server T-SQL transaction log backup basic syntax
BACKUP LOG [AdventureWorks2008] TO  DISK = N'D:\backup\AdventureWorks2008\aw8_20120201_1430.trn'
GO
------------
-- SQL Server T-SQL differential backup basic syntax
BACKUP DATABASE [AdventureWorks2008] TO  DISK = N'D:\backup\AdventureWorks2008\aw8.dif'
WITH  DIFFERENTIAL
GO
------------

 

-- T-SQL dynamic stored procedure for database backup

USE AdventureWorks2008;

GO

 

CREATE PROC sprocFullBackup

           @DatabaseName SYSNAME,

           @Folder       VARCHAR(256),

           @FileName     SYSNAME

AS

  BEGIN

    DECLARE  @DynaSql NVARCHAR(MAX)

    IF RIGHT(@Folder,1) != '\'

      SET @Folder = @Folder + '\'

    SELECT @DynaSql = 'BACKUP DATABASE ' + @DatabaseName + ' TO DISK = ' +

                   CHAR(39) + @Folder + @FileName + CHAR(39)

    PRINT @DynaSql

    EXEC sp_executeSQL @DynaSql

  END

GO

 

-- Execute dynamic stored procedure - Transact-SQL

exec sprocFullBackup 'AdventureWorks2008',

                     'f:\data\AdventureWorks2008\backup\',

                     'test1.bak'

 

/*

BACKUP DATABASE AdventureWorks2008 TO DISK

        = 'f:\data\AdventureWorks2008\backup\test1.bak'

 

Processed 23288 pages for database 'AdventureWorks2008',

file 'AdventureWorks2008_Data' on file 1.

Processed 36 pages for database 'AdventureWorks2008',

file 'FileStreamDocuments' on file 1.

Processed 1 pages for database 'AdventureWorks2008',

file 'AdventureWorks2008_Log' on file 1.

BACKUP DATABASE successfully processed 23325 pages

in 4.664 seconds (39.069 MB/sec).

*/

------------

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE