sql express - backup and restore

[tezzyr]

hi,
I'm creating an application in c++ which uses an sqlexpress database to store data.
I want to be able to add functionality to my application that allows a backup to be made of this database as well as allows the database to be restored from the previous backup.
I understand sqlexpress does not use sqlAgent but I'm not looking to schedule any backups, just to create a backup whenever a user clicks a button.

Is this possible using sqlexpress? would i need to tell my application to run a seperate T-SQL file which has code in it to backup the data? is there an easier way to do this?

Please forgive my ignorance, I've never used sqlexpress before (or had to create a database backup from an application I've written!)

Any help you could give is greatly appreciated.
Thanks in advance.

 

[AlexCuse]

You will just need to put the commands into stored procs that can be called. Here are T-SQL commands to create a backup (these run pretty slowly on large DB's)

--Here's the backup command
 
backup database MyDB 
to disk='d:\MyDB_010407.bak' 
--with stats
--you don't want stats, as they will only show in Query Analyzer
 
 
--Here's the restore command:
 
restore database MyDB_010407
from disk='d:\MyDB_010407.bak' with
move 'MyDB_Data' to  'd:\MSSQL\Data\MyDB_010407.MDF',
move 'MyDB_Log' to  'd:\MSSQL\Data\MyDB_010407.LDF'
--,stats

You will want to build the name (MyDB_MMDDYY) in your procedure using getdate(). Also make sure the drive mappings will work for your setup.

Hope it helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[Catadmin]

Does SQL Express have SMO capabilities? If so, the SMO object can be used for this.

I've actually written a little app to backup DBs, but haven't written one to do any restores. I would think you could restore with an SMO, though.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"