Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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

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
hyphenpipe
Asked:
hyphenpipe
  • 3
  • 2
1 Solution
 
hyphenpipeAuthor Commented:
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
 
dineeshCommented:
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
 
chapmandewCommented:
try this:

exec sp_MSforeachdb 'BACKUP DATABASE [?] TO DISK
=N''d:\mssqldata\MSSQL\BACKUP\[?]_Backup.bak'', INIT'
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
hyphenpipeAuthor Commented:
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
 
chapmandewCommented:
sure...you can backup w/ users online:

backup database yourdbname
to disk = 'c:\backupfile.bak' with init
0
 
hyphenpipeAuthor Commented:
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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now