sql server 2007

[EternalOptimist]

whats the best way to automate the backing up of a sql database

i want it to kick off at 03:00 every day

[SimonMac]

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?!

[Spacecadet]

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.

[SimonMac]

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.

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

[Durbs]

whats the best way to automate the backing up of a sql database

i want it to kick off at 03:00 every day

If you have access to create a Windows scheduled task on the server, drop me a PM with your email and i'll send you the script I use to back up all DB's on a SQL instance. Its been running for years and never failed so is pretty robust. It keeps the last 7 days backups on the server for each DB then I drag them down via scheduled FTP to my home server for storage.

[DimPrawn]

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

Probably because SQL Server 2007 doesn't exist.

[MarillionFan]

whats the best way to automate the backing up of a sql database

i want it to kick off at 03:00 every day

Have you considered taking it off site as well? Try www.milanbenes.com for tape changing services.

[SimonMac]

Probably because SQL Server 2007 doesn't exist.

Sorry I forgot the at the end of my statement

(sarcasm is wasted on some people here)

[Durbs]

This is what I use to backup the SQL Express instances on my dedicated servers, think they are running 2K5 but think this script should work fine with 2K or 2K8 (and full fat SQL) as well.

To use, create a directory C:\SQLBackups and create the following two files in it:

BackupExpress.sql

/**
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

BackupExpress.cmd (alter the .\SQLEXPRESS to your own SQL instance name)

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

Then simply schedule the BackupExpress.cmd file to run in the wee small hours each day using Windows task scheduler.