Solved

MSSql Server 2008 express Task Scheduler backup

Posted on 2013-05-17
7
857 Views
Last Modified: 2013-05-20
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

0
Comment
Question by:CityInfoSys
7 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 39176746
you script will do the job
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 100 total points
ID: 39179830
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
 
LVL 1

Author Comment

by:CityInfoSys
ID: 39181263
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 400 total points
ID: 39181892
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
 
LVL 1

Author Comment

by:CityInfoSys
ID: 39181942
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39182309
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
 
LVL 1

Author Comment

by:CityInfoSys
ID: 39182333
Thanks Scott
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question