How to Automate backups in SQL Server with TSQL Script
Using Maintanence plans for taking the backups is best option as it keep the log for you to monitor and any other errors reporting in the database while operation.
Below script is used to take the backups of all databases in the SQL Server.
Mainly used for Express Edition and Adhoc backups in SQL Server other editions.
Script:
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\'
SELECT @fileDate = REPLACE(CONVERT(VARCHAR(100),GETDATE(),112)+CONVERT(VARCHAR(100),GETDATE(),114),':','')
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Hope the above information helps
Thanks
