Wednesday 15 July 2015

SQL Server Backup Stored Procedure and Usage and also the Batch file

Batch File:

forfiles /P 
readynas\DevelopersBackup\SEARCHSERVER /M RoadKill*.* /D -20 /C "cmd /c del @path"
sqlcmd -U sa -P Password -S Searchserver -Q "EXEC sp_BackupDatabase @backuplocation='
readynas\DevelopersBackup\SEARCHSERVER\', @databaseName='RoadKill', @backupType='F'"
Stored Procedure:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_BackupDatabase] @databaseName sysname, @backupType CHAR(1), @backuplocation varchar(500)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
IF @backupType = 'F'
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName + ' TO DISK = ''' + @backuplocation + @databaseName + '_Full_' + @dateTime + '.BAK'''
IF @backupType = 'D'
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName + ' TO DISK = ''' + @backuplocation + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'
IF @backupType = 'L'
SET @sqlCommand = 'BACKUP LOG ' + @databaseName + ' TO DISK = ''' + @backuplocation + @databaseName + '_Log_' + @dateTime + '.TRN''' EXECUTE sp_executesql @sqlCommand
END

No comments:

Post a Comment

Note: only a member of this blog may post a comment.