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.
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.
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.
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.
try this:
exec sp_MSforeachdb 'BACKUP DATABASE [?] TO DISK
=N''d:\mssqldata\MSSQL\BAC
exec sp_MSforeachdb 'BACKUP DATABASE [?] TO DISK
=N''d:\mssqldata\MSSQL\BAC
ASKER
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.
If there was a way to do this while users are online, that would be sweet.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for clearing that up. Wasn;t sure if I could back it up with users online, but it works fine.
ASKER
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?