We help IT Professionals succeed at work.

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

arvapal
arvapal asked
on
Medium Priority
701 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.
Comment
Watch Question

CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
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.

Commented:
Do you mean by scheduling the backup so that it happens same time every day/week,etc?
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

Commented:
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
Why download a scheduler when Windows has one built in?

Commented:
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

Commented:
In the same light, why does SQL Server have a Scheduler when Task Scheduler is already on  the system?
Task scheduler does things in the OS.  SQL Server Agent does a lot more.

Commented:
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
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:

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
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Commented:
Link to review of SQL Scheduler that I mentioned earlier:

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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...

Author

Commented:
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

Commented:
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
Commented:
Let it run attended with timeout 0 to see what happens. I run with timeout 0 on several jobs.

Author

Commented:
Thanks a lot Jim

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.