SQL Backup

gforce4678 · 2011-01-28

Hello All

I need a guide on the best way of backing up our NAV SQL database, I know how to do the backup through NAV frontend but not sure the best way of backing up the SQL back end.

Any guidance very much appreciated

Mark

Shedman · 2011-01-28

Idealy you would create a job (or maintenance plan) to create your SQL backups. Depending on your needs you schedule it to make:
- A full backup periodically
- Differential backups periodically (to save diskspace and processing time in your backup)
- Transaction Log Backups a couple of times a day if needed

Also create a job that automatically deletes obsolete backup files, say older than 2 weeks.

If you need a full backup at some point outside of your normal backup scheme, be sure to make a 'copy only' backup, so it won't disrupt the backup sequence.

bbrown · 2011-01-28

There is no "one size fits all" backup plan. You need to schedule backups to fit your needs. Things such as DB Size, time available, storage capacity, and recovery needs can all be factors.

Also don't overlook the performance of the destination media. This plays a huge factor in backup performance.

mkpjsr · 2011-01-29

Can we create a Job within NAV for automatic backup? If Yes then how??

kine · 2011-01-29

No, you need to do it in SQL tools (SQL Management). I recommend some basic SQL training or read some SQL manual to understand how e backups works. Setting the backups is one step, than you need to know how to restore it correctly etc.

stryk · 2011-01-29

As bbrown already mentioned the appropriate way of making backups is a somewhat individual thing which needs to be discussed ...

IMHO the very first questions in this discussion should be:

What is the maximum acceptable data-loss?
What is the maximum accepted server down-time?

The answers actually define how and how often to backup your system and how to restore it in case of disaster-recovery:
The higher the backup frequency, the smaller the possible data loss.
The higher the restore-frequency (e.g. using Log Shipping), the shorter the server-downtime.

I dare say that at least one full backup (SQL Backup!) is mandatory. The other things may be discussed ...

jwilder · 2011-01-29

Don't forget to compress your backup (its an option in sql 2008 or later) it is faster to backup, takes up way less space and faster to restore.

bbrown · 2011-01-29

jwilder wrote:

Don't forget to compress your backup (its an option in sql 2008 or later) it is faster to backup, takes up way less space and faster to restore.

With SQL 2008 R2, compression is now available in SQL Standard Edition. Prior it was only available in Enterprise and above. I've been seeing compression of better then 6 to 1.

stryk · 2011-01-29

According to this:
For SQL Server 2005 (or 2008 R1) there are third-party tools available for similar compression:
http://www.idera.com/Products/SQL-Server/SQL-safe-backup/
http://www.quest.com/litespeed-for-sql-server/
http://www.red-gate.com/products/dba/sql-backup/

FSmeets · 2011-11-10

I think the most used schedule is as following:
- A daily backup
- A weekly backup (backup of the last day of the week).
- A monthly backup (backup of the last day of the month).
- A yearly backup (backup of the last day of the year).

Since it's not necessary to save all backups you make every day.
A bit SQL knowledge is needed in this case, but it's not that hard to schedule it.

David_Singleton · 2011-11-10

FSmeets wrote:

I think the most used schedule is as following:
- ...

I think what you meant to say was

I think the best schedule for one of my particular customers is as following:
- ...

Categorically there is no one size fits all for backups.

SQL Backup

Comments

Categories