MSSql Server 2008 express Task Scheduler backup

I would like to backup all databases on a Sql Server 2008 Express server and this is the script I am using. We will not be backing up log files just the full database. I have been using "simple" under Database Properties as the Recovery model is this still correct with Sql Server 2008 Express ? Also does someone have a better script they use?
Thanks

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 = 'E:\Backups\'  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
   + '_' 
   + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR  
SELECT name
FROM master.dbo.sysdatabases 
	Where name NOT IN ('tempdb')
--- WHERE DATABASEPROPERTYEX(name, 'Recovery') IN ('SIMPLE')

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_Full_' + @fileDate + '.bak'  
       BACKUP database @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor
 

Open in new window

LVL 2
CityInfoSysAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Be sure to use unicode literals (N'...' rather than just '...') for system values: it actually can affect performance sometimes, although not in this specific case.  Still, it's a good habit to get into.

Suggested script changes:

...
DECLARE db_cursor CURSOR FOR  
SELECT name
FROM master.dbo.sysdatabases
WHERE
    name NOT IN (N'tempdb') AND
    DATABASEPROPERTYEX(name, N'Status') IN ( N'EMERGENCY', N'ONLINE' )
...
WHILE @@FETCH_STATUS <> -1  
BEGIN  
       SET @fileName = @path + @name + '_Full_' + @fileDate + '.bak'  
       PRINT 'Backup Database command being issued on database "' + @name + '".'
       BACKUP database @name TO DISK = @fileName WITH CONTINUE_AFTER_ERROR
       IF @@ERROR > 0
       BEGIN
           --add code here to record and/or notify yourself that an error occurred during BACKUP
       END --IF
       FETCH NEXT FROM db_cursor INTO @name  
END
...
0
 
Aaron ShiloChief Database ArchitectCommented:
you script will do the job
0
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

I'm using Ola Hallengren's script, but yours looks like it will work.
http://ola.hallengren.com/

In the where name not in 'tempdb', do add the check that the database is online. Easier to do so now than wonder why things have suddenly stopped because you take a database off-line.

Regards
  David
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
CityInfoSysAuthor Commented:
I'm glad you pointed that out. How would I go about putting the online check in? "Where name NOT IN ('tempdb')"? Also do I need to offline the database to change the Recovery model like from simple to full? Thanks
0
 
CityInfoSysAuthor Commented:
Under the "--add code here to record and/or notify yourself that an error occurred during BACKUP" How would I put my e-mail address in case the backup fail? Thanks

       BEGIN
           myemailaddress.com? "backup failed"
       END --IF
0
 
Scott PletcherSenior DBACommented:
If and only if you've configured database mail (which despite what Books Online says you can do in SQL Server 2008 Express, you just have to do it with command line commands and not the gui), you can use procedure msdb.dbo.sp_send_dbmail to send an email from SQL.

Until then, you might just want to log an error message in a table:


CREATE TABLE dbo.error_messages (
    error_message varchar(500) NOT NULL,
    when datetime NOT NULL DEFAULT GETDATE()
    )



BEGIN
    INSERT INTO dbo.error_messages ( error_message )
    SELECT 'Backup failed for database = "' + @name + '"'
END --IF
0
 
CityInfoSysAuthor Commented:
Thanks Scott
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.