In development environment, often it is required to take backup of all databases on weekly basis. During whole week, development is going on by different teams on different databases, so it is very vital to have a backup mechanism for all the database.
So objective is to take backup of all databases in a SQL Server Instance on first day of week during lunch time that is 2:00 pm
In SQL Server, Jobs and schedules are used to run any action with specific time plan.
SQL Server agent must be running to run the jobs.
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 = 'F:\DBWeeklyBackup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 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
It will create backup of all databases in SQL Server at F:\DBWeeklyBackup\ with date appended at the end.