/*****************************************

Procedure to take Backup of database

@OriginalDatabaseName= Original database name which you want to take Back UP.

@BackUpdatabaseName=   Specify Path with file name For Example

                       if Backfile name is Backup.bak and you want to take back up in D drive then

                       Path will be D:\Backiup.bak  

*****************************************/

ALTER Procedure CreateDatabaseBackUP 'TogoProductLatest','D:\yahoo.bak'

@OriginalDatabaseName Varchar(100),

@BackUpdatabaseName Varchar(100)

AS

DECLARE @query varchar(2000)

IF @OriginalDatabaseName IS NOT NULL

BEGIN

    SET @query = 'BACKUP DATABASE ' + @OriginalDatabaseName + ' TO DISK = ' + QUOTENAME(@BackUpdatabaseName, '''')

    EXEC (@query)

END

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

--Database Backups for all databases For Previous Week

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

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

   msdb.dbo.backupset.database_name,

   msdb.dbo.backupset.backup_start_date,

   msdb.dbo.backupset.backup_finish_date,

   msdb.dbo.backupset.expiration_date,

   CASE msdb..backupset.type

       WHEN 'D' THEN 'Database'

       WHEN 'L' THEN 'Log'

   END AS backup_type,

   msdb.dbo.backupset.backup_size,

   msdb.dbo.backupmediafamily.logical_device_name,

   msdb.dbo.backupmediafamily.physical_device_name, 

   msdb.dbo.backupset.name AS backupset_name,

   msdb.dbo.backupset.description

FROM   msdb.dbo.backupmediafamily

   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)

ORDER BY

   msdb.dbo.backupset.database_name,

   msdb.dbo.backupset.backup_finish_date