sql server 2007

Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:

You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

Durbs replied

1 July 2011, 11:54

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

Code:

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

Code:

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.