DECLARE @ServerName VARCHAR(50),
@BackupServer VARCHAR(255),
@Databasename VARCHAR(50),
@StrSQL NVARCHAR(500),
@BackupDesc VARCHAR(50)
DECLARE cur CURSOR FOR
SELECT NAME
FROM sysdatabases
WHERE NAME IN ( 'Database_name' ) --// Inform the Database(s) to backup ex: 'Msdb', 'Master'
SET @BackupServer = '\\Server_Destination\Backups_sql\'
SET @ServerName = @@ServerName
SET @BackupDesc = 'CRM_Deployment'
OPEN cur
FETCH next FROM cur INTO @Databasename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @StrSQL = 'backup database ' + @Databasename
+ ' to disk = ''' + @BackupServer + @ServerName
+ '\' + @Databasename + '\' + @Databasename
+ '_backup_'
+ CONVERT(VARCHAR, Year(Getdate())) + '_'
+ CONVERT(VARCHAR, Month(Getdate())) + '_'
+ CONVERT(VARCHAR, Day(Getdate())) + '_'
+ @BackupDesc
+ '.bak'' with compression, stats = 1'
PRINT Replicate('=', Len(@StrSQL))
PRINT @StrSQL
PRINT Replicate('=', Len(@StrSQL))
EXEC Sp_executesql
@StrSQL
FETCH next FROM cur INTO @Databasename
END
CLOSE cur
DEALLOCATE cur
