Backing up

[phil22046]

I am currently doing Full backups every evening. But if we had a hard drive failure losing a whole days work is unacceptable. I heard that you can do differential backups along with Transaction log backups and lose little or no data in the event of a hard drive failure. How does this work and how should it be set up?

 

[ptheriault]

First the database needs to be in full recovery.
Right click on your database and select properties.You can create a maint plan to backup the log.
Check the recovery model on the options page.

If it is not already Full recover then you need to change it.

Next create a maint plan to backup your transaction log.
You will need to determine what is the max amount on data/time that you could loose for the schedule. 5 , 10, 30 minutes? I have my t-log backups to run every 30 minutes. This means the most data I could loose would be 30 min worth.

Another way (This is how I do it.)

Create a backup device
Backup your log to the backup device so you'll have one device with multiple backup sets in it.
You have to init the device after the full backup completes.

You should also consider moving you backup log files off the server to a t-log share on another server. This is just another safe guard so you don't loose your point in time backups.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

Another thing to remember. Once you have all that set up, the first thing you need to do when you have a problem is to immediately do a log backup if you can.

-SQLBill

Posting advice: FAQ481-4875

 

[phil22046]

ptheriault by backup device, can that be a shared directory on another server? All I have available is Hard drive space on various servers.

 

[ptheriault]

I've never tried that but I don't think you backup device can be on a mapped drive. What version of SQL are you using?
You could try to create the device on a mapped drive an see what happens.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

I am using SQL 2000. We have a shared directory on another Server that I presently backup to. I use \\othermachine\sharedDirectory as the Device and the backup jobs are working.

 

[ptheriault]

Well if that is working, just add some TLog backups to that location and you should be able to recover to a point in time.

Once you have everything set up you should do a test restore.

You would start by restoring your full database backup then apply the log backups to roll the database forward.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

I adjusted the maintenance plan to do a backup every 1/2 hour beginning today. The first t-log backup occurred!
We have another server that I will use to attempt the restore on. I had training on how to do this some years ago, but I can't remember. How do I do the restore to a different location/server ?

 

[ptheriault]

Move your backup files to the restore server.
Create a empty db
Right click and select all tasks -> restore
Restore the full database backup first.
Then apply your logs starting with the oldest first.

Here is good referrence.

http://www.sqlservercentral.com/columnists/kKellenberger/restoringtoapointintime.asp

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

I am not sure how often I can delete the t-log backups. I am doing an incremental backup every 24hrs. So I guess that I should delete the old-backups every week but keep every t-log backup that is after the incremental. I also noticed that the very first t-log backup was 31 megs, the next one was only a little over 1 meg. Why is the second one so much smaller?

 

[ptheriault]

The size of the backup depends on the number of transactions over that period. For example, if you do a re-index those tlogs will be huge.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

Something interesting with my t-log backups. The file extension changed from .TRN to .OLD overnight. Did my regular backup do that?

 

[ptheriault]

I've never seen a backup job or maint plan change the extention. You will have to go through your job to see if it re-naming your day old TRN backups. You can do that, but you have to set it up manually.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

I figured out that a batch job was being run that renamed the files. I need to be able to add my latest backup file name to the sysdevices table in the Master database. When I go into SQL query analyzer and do an insert. It tells me that the command succeeded, but when I do a Select * from sysdevices, the new record is not there. What is the correct way to add to the sysdevices table?

 

[ptheriault]

You have to create the device. use sp_addumpdevice.
You can look it up in BOL.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

What is BOL?

 

[ptheriault]

Books on-line. It the help software installed with SQL server.

- Paul

- If at first you don't succeed, find out if the loser gets anything.