Solved

SQL 2005 Express backup

Posted on 2007-03-26
10
598 Views
Last Modified: 2008-06-01
Hi,
      I need to backup an SQL express database. I know that Express 2005 does not allow for back ups. I was trying to use the Operating Systems Task Manager.

Server 2003
my script to backup is called Showgobackup.sql
exec expressmaint
   @database      = 'ALL_USER',
   @optype        = 'DB',
   @backupfldr    = 'c:\backups',
   @reportfldr    = 'c:\reports',
   @verify        = 1,
   @dbretainunit  = 'days',
   @dbretainval   = 1,
   @rptretainunit = 'weeks',
   @rptretainval  = 1,
   @report        = 1

Then I use the Scheduled task manager to use the SQLCMD.exe
in the run text box I append
                 -S .\SQLExpress -i"c:\backup scripts\ShowGoBackup.sql"

But the database never runs - am I missing a step? Where do you edit a created Scheduled task?

Thanks, Missymadi
0
Comment
Question by:missymadi
  • 5
  • 5
10 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 18795491
Can you not back up using Management Studio (free tool)?
http://www.microsoft.com/technet/prodtechnol/sql/2005/mgsqlexpwssmse.mspx
0
 

Author Comment

by:missymadi
ID: 18795609
THanks!

I am going to use the automated backup solution.

I am creating the first part - an SQL script :

osql -U sa

1>Use Showgo
2>Backup Showgo to disk="C:\Showgo.bak"
1>Go

I am getting an error "ms102, level 15, state1, server showgo-VM\SQLEXPRESS Line 1 incorrect syntax.
what am I doing wrong?
Thanks for your help
0
 
LVL 29

Expert Comment

by:QPR
ID: 18795654
should there be a "go" after showgo?
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:missymadi
ID: 18795700
yes, it is a software package for trade shows.
0
 
LVL 29

Expert Comment

by:QPR
ID: 18795721
:)
I meant should it not be

1>Use Showgo
2>Go
3>Backup Showgo to disk="C:\Showgo.bak"
4>Go
0
 

Author Comment

by:missymadi
ID: 18795762
Oh, sorry! :) Yes I did use Go after both commands.
0
 

Author Comment

by:missymadi
ID: 18795916
Got it!
Use Showgo
Go
Backup Database Showgo to disk=’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL.1\MSSQL\BACKUP\SHOWGO.BAK

But my next challenge -- how do I use the sqlcmd to execute the script by passing the script to SQLCMD?
0
 
LVL 29

Expert Comment

by:QPR
ID: 18796862
sqlcmd -i nameofscript.sql

more examples
http://msdn2.microsoft.com/en-us/library/ms165702.aspx
0
 

Author Comment

by:missymadi
ID: 18799648
I tried to use my script above

osql -U sa
password
Use Showgo
Backup database showgo to disk="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Showgo.bak

This works from the command prompt. How do I use this in an SQL query? What is the syntax? I tried the link from msdn2 but it didn't work?
Thanks for your help!
0
 
LVL 29

Accepted Solution

by:
QPR earned 75 total points
ID: 18804095
How do you mean do in a query?
You want the syntax to back up a database?

An example from one of mine...
EXECUTE master.dbo.xp_sqlmaint N'-D "ShowGo" -Rpt "c:\Microsoft SQL Server\SQLDB\MSSQL\LOG\ShowGo Data Backup.txt" -DelTxtRpt 4WEEKS -WriteHistory  -VrfyBackup -BkUpMedia DISK -BkUpDB "c:\Microsoft SQL Server\SQLBkup" -DelBkUps 3DAYS -CrBkSubDir -BkExt "BAK"'

Change the paths to suit.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

860 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