|
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).
*/
------------
|
