Solved

How can I backup an entire database in MS SQL Server Express 2005?

Posted on 2008-06-25
6
230 Views
Last Modified: 2010-04-21
I have a copy of MS SQL Server 2005 Express Edition that is running on database engine at this time.

Included in this instance is about 100 seperate databases.  I need to automate a way to back these databases on a nightly basis and because I am using SQL Express I do not have access to SQL Server Agent to automate this.

Any help is appreciated.
0
Comment
Question by:hyphenpipe
  • 3
  • 2
6 Comments
 
LVL 6

Author Comment

by:hyphenpipe
ID: 21865078
I did find this article which looks promising:

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

But I need to backup about 100 databases and I am adding more all the time so I need it to be dynamic without me having to go an modify the SQL command every time I add a new database.

Also, I am am unsure as to how this tutorial handles attached databases that may have open connections to them.

Can anyone clear that up for me?
0
 
LVL 7

Expert Comment

by:dineesh
ID: 21865095
hi,

is there a time when you can stop the SQL services for some time depending on your database size?

in that case you can write a vbscript to stop the sql service and copy the physical files from the data folder into another path.

else try this too..

use ntbackup - Start -> Programs -> Accessiories -> System Tools -> Backup

and backup the data folder, that does a volume shadow copy, once you have a backup, try to restore on another system with SQL Express and Attach the database and point it to the data files.

if that works then you can automate it from scheduler.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21865126
try this:

exec sp_MSforeachdb 'BACKUP DATABASE [?] TO DISK
=N''d:\mssqldata\MSSQL\BACKUP\[?]_Backup.bak'', INIT'
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 6

Author Comment

by:hyphenpipe
ID: 21865231
I just realized I only need to back up one database in the instance.  The other databases just contain views that point the this one.

If there was a way to do this while users are online, that would be sweet.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 21865238
sure...you can backup w/ users online:

backup database yourdbname
to disk = 'c:\backupfile.bak' with init
0
 
LVL 6

Author Closing Comment

by:hyphenpipe
ID: 31470945
Thanks for clearing that up.  Wasn;t sure if I could back it up with users online, but it works fine.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl Agent job fails--SSIS package looses password 6 54
Delete from table 6 47
Why is the output of this function is like this? 4 38
Help  needed 3 36
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

861 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