?
Solved

MSSql Server 2008 express Task Scheduler backup

Posted on 2013-05-17
7
Medium Priority
?
879 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 400 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 1600 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

752 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