Let's say we have to backup multiple databases from one of the server and also few other databases for which we have created on linked server. After each of the backup we want to send an email with time taken by the database backup.
Solution:
Here is the code that can be used to backup database from Local Server as well from Linked Server.
--DECLARE VARIABLES
DECLARE @DataBaseName VARCHAR(500)
DECLARE @BackupFolderPath VARCHAR(250)
DECLARE @BackupFileName VARCHAR(500)
DECLARE @TimeStamp VARCHAR(20)
DECLARE @ServerName NVARCHAR(500)
--Specify database backup directory
SET @BackupFolderPath = 'C:\MonthlyBackups\'
--TimeStamp
SELECT @TimeStamp = CONVERT(VARCHAR(20), Getdate(), 112) + '_'
+ Replace(CONVERT(VARCHAR(20), Getdate(), 108), ':', '')
DECLARE db_cursor CURSOR FOR
--Include All Database here those needed to be backedup with ServerName
SELECT DatabaseName,ServerName
FROM (
SELECT 'TESTDB' AS DataBaseName, 'local' AS ServerName
UNION ALL
SELECT 'DBNAME' AS DatabaseName, 'LinkserverName' AS ServerName
) DB
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DataBaseName,@ServerName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(MAX)
DECLARE @STARTTIME DATETIME
DECLARE @ENDTIME DATETIME
DECLARE @SEC INT
DECLARE @BackupTimeTaken VARCHAR(50)
IF @ServerName='local'
BEGIN
SET @BackupFileName = @BackupFolderPath + @DataBaseName + '_'
+ @TimeStamp + '.BAK'
SET @SQL=' BACKUP DATABASE ' + @DataBaseName
+ ' TO DISK = N''' + @BackupFileName + '''WITH COMPRESSION'
PRINT @SQL
END
IF @ServerName<>'local'
BEGIN
SET @BackupFileName = @BackupFolderPath + @DataBaseName + '_'
+ @TimeStamp + '.BAK'
SET @SQL=@ServerName+'.master.dbo.sp_executesql N''BACKUP DATABASE '+@DataBaseName+' TO DISK = N''''' + @BackupFileName + ''''' WITH COMPRESSION'''
PRINT @SQL
END
SET @STARTTIME=GETDATE()
EXEC ( @SQL)
SET @ENDTIME=GETDATE()
SET @SEC=(SELECT DATEDIFF(SECOND,@StartTime,@EndTime))
SET @BackupTimeTaken=(select convert(varchar(5),@sec/3600)+':'+convert(varchar(5),@sec%3600/60)+':'+convert(varchar(5),(@sec%60)))
--Build Notification
DECLARE @Mail_Profile_Name VARCHAR(100)
DECLARE @MessageBody NVARCHAR(1000)
DECLARE @RecipientsList NVARCHAR(500)
DECLARE @MailSubject NVARCHAR(500)
DECLARE @ServerFullName NVARCHAR(500)
IF @ServerName='local'
SET @ServerFullName=@@SERVERNAME
ELSE
SET @ServerFullName=@ServerName
--SET VARIABLES FOR NOTIFICATION
SET @Mail_Profile_Name='YourMailProfile'
SET @RecipientsList='aamir.nmhu@yahoo.com'
SET @MailSubject='Database::'+@DataBaseName+ ' backup completed successfully from Server::'+@ServerFullName
SET @MessageBody='Hi All,'+CHAR(13)+' The database ::'+@DataBaseName +' backup is completed successfully from Server::'+@ServerFullName
+CHAR(13)+'TO Folder Path:: '+@BackupFileName+CHAR(13)
+'Time taken for backup(HH:MM:SS):: '+@BackupTimeTaken+CHAR(13)+CHAR(13)+'Thank you'+CHAR(13)+'Technology Team'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Mail_Profile_Name,
@recipients = @RecipientsList,
@body = @MessageBody,
@subject = @MailSubject;
FETCH NEXT FROM db_cursor INTO @DataBaseName,@ServerName
END
CLOSE db_cursor
DEALLOCATE db_cursor
--To get the profile Account
--select * from msdb..sysmail_profile
Mua vé máy bay tại Aivivu, tham khảo
ReplyDeletegia ve may bay di my
vé máy bay hà nội sài gòn tháng 1
giá vé máy bay từ tphcm ra hà nội
vé giá rẻ đi nha trang
giá vé máy bay đà nẵng đi đà lạt
taxi sân bay nội bài 180k
combo hải phòng nha trang
Maharashtra Board High School Textbooks 2023 are Very Important to Students on the Preparations of the Monthly and Final Examination. Here, we are Providing the Latest Edition of MH 6th, 7th, 8th, 9th, 10th Class Book 2023 which is Published by the Balbharati. ebalbharati 7th Class Textbook All the chapters can be Downloaded in form of PDFs. Maharashtra High School Students Follows These Textbooks are Updated as per the Syllabus Prescribed by e- Balbharati, Students of 6th, 7th, 8th, 9th, 10th Class Should follow Prescribed Textbooks while Preparing for Exam. Our Team Refer to the Respective Subject Textbook while Preparing the Final Important questions.
ReplyDelete