Solved

MSSql Server 2008 express Task Scheduler backup

Posted on 2013-05-17
7
863 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server stored procedure parameter 10 20
removing unwanted rows from an sql server ranked table 13 34
Convert VBA UDF to SQl SERVER UDF 4 47
SQL 2014 missing dll from Bin? 3 31
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

713 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