- Joined
- Jul 8, 2005
- Messages
- 22,492
- Likes received
- 999
- Thanks received
- 191
- Location
- Castle Saburac
whats the best way to automate the backing up of a sql database
i want it to kick off at 03:00 every day
2007?!
The sign of someone who really needs some basic SQL Server help
Backups can be automated either by scripting them out in T-SQL (best way to do this is to use the backup GUI and then hit the script button which will then create the SQL for you) and creating a SQL Server agent job or by creating a maintenance plan and using the wizard there.
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'd agree with SC and create a maintenance plan, using the wizard they are straight forward, but I have not tried this on 2007 so can't give exact details

whats the best way to automate the backing up of a sql database
i want it to kick off at 03:00 every day
Probably because SQL Server 2007 doesn't exist.
![]()
at the end of my statement/**
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