SQL Backup

R

ReD-DaWg-OnE

n00b
Joined
Jul 23, 2009
Messages
36
I have a problem client that doesn't want to spend the money on a Datto Siris device which I recommended to them.

They are using SQL 2008 and I know it has a built in backup automation but I am not very familiar with SQL.
 
You have to dump your databases, you can't just back the files up on the server live.
 
Are they using SQL Standard, Enterprise or Express?

Express does NOT allow maint plans so you can't automate backups with it.

Std and Ent will allow you to schedule. But i suggest the datto, why not go with the G Series and not the Siris?
 
ReD-DaWg-OnE said:
I have a problem client that doesn't want to spend the money on a Datto Siris device which I recommended to them.

They are using SQL 2008 and I know it has a built in backup automation but I am not very familiar with SQL.
Click to expand...
I see you needing to know both -- machine backups are not database backups.

Go learn SQL Server, its administration processes, and best practices. There may even be a SQL Server user group in your area, too.
 
PTNL said:
I see you needing to know both -- machine backups are not database backups.

Go learn SQL Server, its administration processes, and best practices. There may even be a SQL Server user group in your area, too.
Click to expand...

SQL 2005 and above are shadow copy aware, so you can get a full DB backup using a VSS aware backup utility like storagecraft.
 
this is what i use OP:

http://www.sqldbatips.com/showarticle.asp?ID=29

free and works great....

have a backup folder on the root of every SQL server (only have a couple) and i use these w/ a batch file and dump each DB i want every night... then offsite backup to another location using cobian


i backup images of the systems quarterly (i've got them virtualized, so this generally no biggie)
 
Maintenance plans... We backed up the db to a flat file once a day. We also dumped Transaction Logs every hour..

Those files were backup ed up via backupexec.. (Which by the way I think their SQL agent sucks)
 
k1pp3r said:
SQL 2005 and above are shadow copy aware, so you can get a full DB backup using a VSS aware backup utility like storagecraft.
Click to expand...

That's true, but you miss the depth/capability such as what calvinj hinted at later on in the thread...

calvinj said:
Maintenance plans... We backed up the db to a flat file once a day. We also dumped Transaction Logs every hour..
Click to expand...
 
robvas said:
You have to dump your databases, you can't just back the files up on the server live.
Click to expand...

Yes you can. You need a backup application with the proper SQL agent, but yes you certainly can.

k1pp3r said:
SQL 2005 and above are shadow copy aware, so you can get a full DB backup using a VSS aware backup utility like storagecraft.
Click to expand...

Backup Exec, ARCServe, NetBackup, and I think even TSM can do it.
 
k1pp3r said:
Are they using SQL Standard, Enterprise or Express?

Express does NOT allow maint plans so you can't automate backups with it.

Std and Ent will allow you to schedule. But i suggest the datto, why not go with the G Series and not the Siris?
Click to expand...

You can't do maintenance plans but you can still just use a simple script with the proper TSQL to do the backup in Express. Or a simple job with the proper TSQL using SQL Agent would do the same thing (think Express has Agent).

But back to the OP, you do need to read about backups with SQL as they are similar but different from system backups. If they are using a purchased product contact the vendor about any recommended SQL backup strategies as an easy starting point.

If it's an internally created DB than you need to define the amount of risk your client is willing to take on losing data (15 minutes is our limit) and then define the proper strategy (IE hourly transaction log backups, 12 hour differential, 24 hour full, etc.). If the DB is just set to simple recovery then you're only looking at performing full backups and it's just a question of how often.

A very important difference between system backups and SQL backups is a full recovery database HAS to have a good backup strategy to run efficiently and to also maintain a log file that doesn't just keep growing. And not just a file backup using VSS but a proper SQL backup that actually removes the commited transactions from the log file to the backup file.

TL;DR - SQL backups are different from file/system bakcups and you need to read about simple vs full recovery models to implement a proper backup strategy using TSQL jobs or SQL maintenance plans.
 
Thank you guys for all the advice.

I was able to get them into a Siris Lite device, the lower device and monthly costs are what did it. We are going to setup the backups for every 5min.

The software they use is OfficeMate, which is horrible BTW. In the past when I did a DB restore we took it from the server image backup that runs every night and stopped the SQL Server copied the data over and restart the SQL Server.

Datto has a SQL Agent, but I will check with them on what they recommend for the logs and such if that is necessary.

Thanks again for you help, this will be my first BDR install and am really looking forward to it.
 
Be wary of running DB backups on the Siris box every 5 minutes. The chain gets outlandishly large, make sure to truncate your chains on a very regular basis to ensure that your restores are as reliable as possible.

Having just gone through a restore when another one of our guys did this, it is a huge hassle when the chain doesn't work right...
 
Zetro said:
Be wary of running DB backups on the Siris box every 5 minutes. The chain gets outlandishly large, make sure to truncate your chains on a very regular basis to ensure that your restores are as reliable as possible.

Having just gone through a restore when another one of our guys did this, it is a huge hassle when the chain doesn't work right...
Click to expand...

Depends if you are using shadow snap or not. Shadowsnap has some strange issues but it isn't bad but make sure you set your retention correctly
 
Datto does use Shadowsnap, I have to do some more reading on their "inverse chain". Is there ever a need to start with a new image? What do you recommend for retention?
 
You must log in or register to reply here.
Back
Top