Solved

SQL express backup

Posted on 2013-11-13
19
602 Views
Last Modified: 2013-11-27
I want to backup SQL express. I have management studio 2008 installed and although there is an option to backup the DB I cannot find any where to run this on schedule.
While running a manual backup I select script and it outputted the script it was running which reads

BACKUP DATABASE [stock] TO  DISK = N'D:\Stock_DB_Backup\Stock_Nov_11_2013' WITH NOFORMAT, NOINIT,  NAME = N'Stock-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

I want to automate this process to have a full backup. I want each backup to have a filename that increments 01-02-03 as oppose to overwriting the previous days backup.

If someone can tell me specifically what I need to do without sending me to a technet link : )
0
Comment
Question by:Sid_F
[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
  • 10
  • 7
  • 2
19 Comments
 
LVL 10

Expert Comment

by:joriszwaenepoel
ID: 39644306
I could copy and paste the text from the following article (Microsoft support) , but I won't do that.

How to schedule and automate backups of SQL Server databases in SQL Server Express

http://support.microsoft.com/kb/2019698
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39644568
you should do more than just backup.

I use http://expressmaint.codeplex.com/
0
 
LVL 6

Author Comment

by:Sid_F
ID: 39669093
The application looks like what I need but unfortunately the links explaining how to use it are broken
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 6

Author Comment

by:Sid_F
ID: 39669112
In the Microsoft link it refers to step A to create a stored procedure. I am unsure how to do this and also if and what I need to edit in the text of step A.
Lets say I want to backup a database called master once a day and save it to d:\backups
Sorry for the basic steps but I am new to this!
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39669155
0
 
LVL 10

Expert Comment

by:joriszwaenepoel
ID: 39669245
For the Microsoft article:

Step A: just copy and paste the code into a  new query window in the SQL Server Management studio (I suppose you installed Management Studio Express?), and run it.  I don't think you need to edit anything here.

Step B: use Example 1 to have the most complete backup (all databases, full backup) or Example 4 for a single database.

"Master" already exists as a database name in every SQL Server instance.  So I hope you didn't put all your tables in the existing master database.  You should create a new database for your application.
0
 
LVL 6

Author Comment

by:Sid_F
ID: 39669366
To make it easier If I try to run a manual backup, setting all the parametres for filename location type etc and then select the option "script action to file" I can then save a file with a .sql extention with these details. I put this in task scheduler but I believe I am missing a step as it does not run until I open management studio
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39669395
check steps 8 and 9 of my last link
0
 
LVL 6

Author Comment

by:Sid_F
ID: 39673961
Everything seems to be in place but it still does not work. If I run the backup directly from the studio console it works fine with the below parametres but it does not work through task scheduler even when I select to "run" manually.
The task scheduler is running with my local admin account details and the option for run with highest privilages is checked. It seems to start but then sticks on "running" The history shows

Task Scheduler successfully completed task "\backup" , instance "{25d171ee-0270-4c1f-a1b2-d60d90c3409f}" , action "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" with return code 1.


I have selected to start a program
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
with add arguments entered as -i "c:\script\backup.sql"

The contents of the backup.sql are
BACKUP DATABASE [master] TO  DISK = N'C:\script\mybackup' WITH NOFORMAT, INIT,  NAME = N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
0
 
LVL 6

Author Comment

by:Sid_F
ID: 39673983
I tried the same on my server and this worked fine so some issue with my test laptop. One final question when the backup runs how can I tell it to create a new backup file each day and ideally append the date to the name of the backup. At the moment each time I run it it overwrites the existing file even though its set to append. Thanks.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39674240
if you are using the last link I gave you, make sure you set these parameters:
   @dbretainunit  = 'days',
   @dbretainval   = 5,
0
 
LVL 6

Author Comment

by:Sid_F
ID: 39674319
I entered those parameters exactly as above on a line on their own but the backup no longer runs. I presume they need to go in in a specific format?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39674529
>>I entered those parameters exactly as above on a line on their own but the backup no longer runs. I presume they need to go in in a specific format?

just to be sure, you are using the script contained in that link?
0
 
LVL 6

Author Comment

by:Sid_F
ID: 39676994
The steps I have done so far are copy the details below into a notepad document, change the name of the database from verba to my database name. I have also created c:\backup and C:\reports folder. I have saved the noted document as backupscript.sql and saved it to c:\backup .
In task scheduler I have selected to start a program
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
with add arguments entered as -i "c:\backup\backupscript.sql"

When I run this task manually it does not create a file. I am doing something wrong


exec expressmaint
   @database      = 'verba',
   @optype        = 'DB',
   @backupfldr    = 'c:\backup',
   @reportfldr    = 'c:\report',
   @verify        = 1,
   @dbretainunit  = 'days',
   @dbretainval   = 4,
   @rptretainunit = 'weeks',
   @rptretainval  = 1,
   @report        = 1
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39677229
do you have any error message?

try to run your command from SSMS and see if there is an error.

did yo run the sql_express_maintanance.sql to install the sp on your server?

are you sure you have read the warning section: "In order to run expressmaint stored procedure, the following options have to be enabled in the Surface Area Configuration:"?
0
 
LVL 6

Author Comment

by:Sid_F
ID: 39678077
In history I can see action completed error code 0.

Not sure where I need to go to run the command in ssms

I downloaded the zip file from http://expressmaint.codeplex.com/releases/view/35577 for sql 2008 extracted it and ran the exe which flashed up a screen for s split second

I have turned on the surface area features as indicated in the article.

MS don't make this easy!
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 39678183
>>Not sure where I need to go to run the command in ssms

open ssms and create a new query. a blank window will open. copy your exec statement and hit F5. this exactly what you would do for any other query.

Did you run this part:

"You can install the stored procedure by executing the sql_express_maintanance.sql file on your SQL Server with a user that has sysadmin rights."
0
 
LVL 6

Author Comment

by:Sid_F
ID: 39680157
Perfect I can see expressmaint procedure did not install correctly. I ran the stored procedure instead of the exe and everything worked perfect. Thank you very much.
0
 
LVL 6

Author Closing Comment

by:Sid_F
ID: 39680158
Thanks
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

By default, Carbonite Server Backup manages your encryption key for you using Advanced Encryption Standard (AES) 128-bit encryption. If you choose to manage your private encryption key, your backups will be encrypted using AES 256-bit encryption.
VM backups can be lost due to a number of reasons: accidental backup deletion, backup file corruption, disk failure, lost or stolen hardware, malicious attack, or due to some other undesired and unpredicted event. Thus, having more than one copy of …
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

687 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