reposting: bkup and resore strategy. Please reply

Hi,
My vb.net app will use database which will be deployed using MSDE on client machines. I will provide UI to client in my app to backup and restore the database. My app will work with one database, for e.g. MyAppDB. My question is what are the best pract
ices for doing this:
a) Should I choose Full or Simple mode. There is no mission critical data. But as my clients will be using MSDE, will setting up the recovery mode as Full cause the database and log size limits to cross the 2GB limit soon. Is it better if I choose SIMP
LE recovery mode. What do you suggest.
b) Should I back up the system databases also, if yes then what should be the order of backing these up (and then in what order should the databases be restored)
c) Does the MSDE login information also needs to be backed up. At max I will have just one custom login and sa. I am using SQL Authentication.
d) Will I need to close my application and make sure that my apps' MSDE instance is not running before backing up the databases.
d) I want to provide user the option to choose the backup location. How will I figure out if they chose something like tape drive or zip drive. How should I handle this scenario.
e) Also how can I find out if there is sufficient space on the hard drive to backup the database. Are there any standard sql functions to check that.
f) Should I do database log file backup too while backing up the database.
sorry for so many questions.
thanks
newbie
Print | Copy URL of this post
Expand All Collapse All
See answers inline:
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:3DFC5E47-73A3-4CFC-9161-0AF13E789AD5@.microsoft.com...
> Hi,
> My vb.net app will use database which will be deployed using MSDE on
client machines. I will provide UI to client in my app to backup and
restore the database. My app will work with one database, for e.g. MyAppDB.
My question is what are the best practices for doing this:
> a) Should I choose Full or Simple mode. There is no mission critical
data. But as my clients will be using MSDE, will setting up the recovery
mode as Full cause the database and log size limits to cross the 2GB limit
soon. Is it better if I choose SIMPLE recovery mode. What do you suggest.
I almost always use either full or bulk-logged, although there are a few
instances where simple is appropriate. Basically you need to determine in
the event of failure do you want to be able to recover to the point of
failure, or is it good engoug to recover from the last full backup? As for
size limits, you can control the size of the log by backing it up regularly.
Besides, I am pretty sure that the 2Gb size limit is on the data file, not
the log.

> b) Should I back up the system databases also, if yes then what should be
the order of backing these up (and then in what order should the databases
be restored)
Yes, at least master and msdb, but the order of backing them up is not
important. Master would be the first to be restored

> c) Does the MSDE login information also needs to be backed up. At max I
will have just one custom login and sa. I am using SQL Authentication.
The logins are stored in master.

> d) Will I need to close my application and make sure that my apps' MSDE
instance is not running before backing up the databases.
No, backups are an online operation.

> d) I want to provide user the option to choose the backup location. How
will I figure out if they chose something like tape drive or zip drive. How
should I handle this scenario.
That's an application issue, you stated that you were providing them a front
end UI to backup the databases. The destination of the backup files is just
one of the BACKUP command arguments. There are also several arguments that
apply only to tape backup operations that you might want to take into
consideration.

> e) Also how can I find out if there is sufficient space on the hard drive
to backup the database. Are there any standard sql functions to check that.
There isn't a function that will estimate the size of the backup, but you
can read the msdb..backupset table to get the size of the last backup (or an
average of the last several) and estimate the size based on that.

> f) Should I do database log file backup too while backing up the database.
Not at the same time, although it won't hurt anything, there's just no
reason to do them at the same time.

> sorry for so many questions.
> thanks
> newbie
>
> Print | Copy URL of this post
>
> Expand All Collapse All
>
|||Thanks don. Here are few more concerns that I have:
1> Is there any way to zip the .bak files into just 1 file ising T-sql or will I have to resort to 3rd party solutions for compression (as .Net doesn't have any compression lib)
2> What happens if one user chooses to backup while others are still working on the same database (imagine a small office setup with few computers and my app on all of them and the db on one central machine). Will the backup work fine in this situation or
will the users need to log off.
3> Also we are not aiming at schedule backups option right now. Our clients will have to go to the UI screen and backup the database themselves when they want to. So in this situation should I restore system databases also when the users choose to restore
my app's db from the backed up location. And do I need to backup the system databases always when they choose to backup my apps db or just before any major important changes.
Thanks