Prev: Online Log Size
Next: Get a list of subscribers
From: Cameron_C on 25 Jan 2010 14:16
Hello everyone,
I have a small Desktop application. When one of the controls on the main
window menu is selected, the underlyinh database is backed up using a stored
procedure.
I am curious if I could (or should) modify the stored procedure to generate
a fixed set of say ten generations of backups.
I believe I could do it with xp_cmdshell. I could appeand a numeric value
"01" through '10" to the backup file name.
And I could check for the existance of a particular entry.
So for example, I would use xp_cmdshell to see if "C:\Backups\File-01"
existed, and is it did NOT exist, I would backup to the file and DELETE
"C:\Backups\File-02".
If it did exist, I would do the same check for File-02, and so on through
File-10.

My question, is this a silly way to offer cycling versions of backups to my
Users?
I know I could use a trigger to run the backups on some regular basis, but I
wanted to offer the choice to take the specific backup, and then they would
be able to take the backup away from the office for archiving or offsite
backups.

Thank you,
From: Jeffrey Williams on 25 Jan 2010 21:33
What version of SQL Server are you using? If you are using Express - I can
see the need for you to set a backup option, however I would recommend that
you consider a plan that schedules the task (using Scheduled Tasks and
SQLCMD) to back up the database on a scheduled basis.

I would also recommend that you define the backup file using a datetime
stamp of when the backup was taken and the database name, for example:
<database name>_backup_YYYYMMDDHHMMSS.bak

Have the user define a backup directory and a retention period. Based upon
that retention period, run a process after successful backup to remove any
backup files that were created more than the retention period back.

And finally, make sure you change the database recovery model from the
default FULL to SIMPLE, or implement frequent transaction log backups in
addition to your full backups. If not, your transaction log will grow until
it consumes all of the space on the drive.

"Cameron_C" <CameronC(a)discussions.microsoft.com> wrote in message
news:C609FE54-3E99-41E0-86C7-2B366A803482(a)microsoft.com...
> Hello everyone,
> I have a small Desktop application. When one of the controls on the main
> window menu is selected, the underlyinh database is backed up using a
> stored
> procedure.
> I am curious if I could (or should) modify the stored procedure to
> generate
> a fixed set of say ten generations of backups.
> I believe I could do it with xp_cmdshell. I could appeand a numeric value
> "01" through '10" to the backup file name.
> And I could check for the existance of a particular entry.
> So for example, I would use xp_cmdshell to see if "C:\Backups\File-01"
> existed, and is it did NOT exist, I would backup to the file and DELETE
> "C:\Backups\File-02".
> If it did exist, I would do the same check for File-02, and so on through
> File-10.
>
> My question, is this a silly way to offer cycling versions of backups to
> my
> Users?
> I know I could use a trigger to run the backups on some regular basis, but
> I
> wanted to offer the choice to take the specific backup, and then they
> would
> be able to take the backup away from the office for archiving or offsite
> backups.
>
> Thank you,

 | 
Pages: 1
Prev: Online Log Size
Next: Get a list of subscribers