whats the best way to automate the backing up of a sql database
i want it to kick off at 03:00 every day
i want it to kick off at 03:00 every day
/** File Name: BackupExpress.sql Description: Backs up all databases. The script requires a C:\SQLBackups directory by default to backup to but can be changed with the @OutputPath variable. **/ SET QUOTED_IDENTIFIER OFF USE master GO SET NOCOUNT ON DECLARE @dayofweek varchar(20) SELECT @dayofweek = CASE datepart(dw, getdate()) WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' END DECLARE @OutputPath varchar(500) DECLARE @DatabaseBackupFile varchar(500) DECLARE @FolderName varchar(25) DECLARE @DatabaseName varchar(35) DECLARE @strSQL varchar(2000) DECLARE @hostname varchar(255) SET @hostname = (select replace(convert(varchar(255),serverproperty('SERVERNAME')), '\','_')) SET @OutputPath = 'C:\Backups\SQLBackup' DECLARE cur_Backup CURSOR FOR select name from sysdatabases where name !='tempdb' OPEN cur_Backup -- Fetch the db names from Cursor FETCH NEXT FROM cur_Backup INTO @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN SET @DatabaseBackupFile = @OutputPath + '\' + @hostname + '-' + @DatabaseName + '-' + @dayofweek + '.bak' print @DatabaseBackupFile SET @strSQL = 'BACKUP DATABASE '+@DatabaseName+' TO DISK = "'+ @DatabaseBackupFile+ '" WITH RETAINDAYS = 1, NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10' PRINT @strSQL EXEC (@strSQL) FETCH NEXT FROM cur_Backup INTO @DatabaseName END -- Distroy Cursor CLOSE cur_Backup DEALLOCATE cur_Backup SET NOCOUNT OFF
CLS ECHO OFF ECHO Testing to make sure directories exist. IF NOT EXIST C:\SQLBackups MD C:\SQLBackups ECHO Complete with directory creation ECHO ********************************************************** ECHO Backing up databases....... sqlcmd -S .\SQLEXPRESS -i c:\Backups\SQLBackup\BackupExpress.sql -o c:\Backups\SQLBackup\backup.log ECHO ********************************************************** ECHO Backup complete. Look in the C:\SQLBackups\Backup.log file for information. ECHO ON
Comment