?
Solved

How to take database backup in sql server 2005 express edition automatically

Posted on 2009-02-13
18
Medium Priority
?
682 Views
Last Modified: 2012-05-06
Hi,

Is there any possiblity to take the database backup in sql server 2005 express edition automatically. If yes, please help me wtih the process to acheive that task.
0
Comment
Question by:arvapal
  • 9
  • 3
  • 3
  • +2
18 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 23635351
Sure.  Create your backup statement using TSQL...

backup database dbname to disk = 'c:\backup.bak' with init

save this script to a file.  then, create a batch file on your OS that runs a sqlcmd command and points to this file.  create a windows scheduled job to execute this batch file every so often.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 23635355
Do you mean by scheduling the backup so that it happens same time every day/week,etc?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23635367
You need to use the Windows Task scheduler to execute the backup command automatically.

Here's a article on it.
http://www.sqldbatips.com/showarticle.asp?ID=27
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 18

Expert Comment

by:jmoss111
ID: 23635386
If you need a scheduler for SQL Server 2005 Express, dowload SQL Scheduler from www.lazycoding.com . Its free and works very well.

Regards,

Jim
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23635554
Why download a scheduler when Windows has one built in?
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 23635675
Hi Brandon,

SQL Scheduler does a lot more than task scheduler, check it out. I use task scheduler on my db server also.

Regards,

Jim
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 23635699
In the same light, why does SQL Server have a Scheduler when Task Scheduler is already on  the system?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23635865
Task scheduler does things in the OS.  SQL Server Agent does a lot more.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 23636162
I use SQL Scheduler on Express instead of Task Scheduler because it's easier to work with, no osql, gives me notifications, etc. I use Task Scheduler to zip backups, run contig.exe on dbs, etc.

Regards,

Jim
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23641730

Here is an excellent example doing exactly what you want. Also includes a VBS script to clean up "old" versions. Can simply follow the instructions step by step and it does work.

http://www.mssqltips.com/tip.asp?tip=1486
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23641940
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 2000 total points
ID: 23643546
Link to review of SQL Scheduler that I mentioned earlier:

http://www.mssqltips.com/tip.asp?tip=1083
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23643920
Hi Jim, just installed that tool and ran it up - it works extremely well. Thanks for sharing that link, should have looked it up earlier...
0
 

Author Comment

by:arvapal
ID: 23667711
Hi Jim,  
I have installed that SQL Scheduler tool and it is working excellent.

But the problem that i am facing now is: Using that tool i am getting backup file size upto 1.3 GB approximately.

My current database size is 2.5 GB, if i execute backup plan on that database, it is giving the below metnioned error.

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

i have tried it on 2 different machines, but result is same. Any idea if there is any restrication/limitaion of backup file size ?

Please help

0
 
LVL 18

Expert Comment

by:jmoss111
ID: 23669416
I backup 4 GB files regularly. You can set the timeout to 0 and let it run attended. Look at the bottom of the main tab for the job.

Regards,

Jim
0
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 2000 total points
ID: 23669428
Let it run attended with timeout 0 to see what happens. I run with timeout 0 on several jobs.
0
 

Author Closing Comment

by:arvapal
ID: 31546701
Thanks a lot Jim
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 23670833
After you get a feel for the run time, you'll want to modify to match your avg run time, so that if anything goes awry then the job would time out as it should.

Have a great day,

Jim
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Copy Database Wizard 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.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

839 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