Solved

MSSql Server 2008 express Task Scheduler backup

Posted on 2013-05-17
7
867 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 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

732 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