Transfer SQL backup set from HDD to tape

[darkod]

Hi.

 

We have Retrospect for Win 7.0 running, with Exchange and SQL agents. We have three different SQL servers (and three agent licenses of course).

 

We have a dedicated backup server with the backup sets on its hard drives. This works fine.

 

In order to have off-site copy we have recently started transfering the backup sets trough Retrospect on a tape drive connected to the backup server. So, the File backup sets get transfered to a Tape backup set.

 

This works fine and reasonably fast for everything except one of the SQL servers. The transfer of that file backup set to the tape backup set can take hours, and sometimes even not finish in hours. I have tested even leaving it over night and on two ocassionas didn't finish after 20h so I stopped it. Retrospect is responding all the time, it's not stuck, I can easily stop the transfer of the backup set, but it can't seem to successfully finish.

 

The other two SQL servers have only few databases each, and it's not a problem to transfer their backup set from file to tape. I don't know whether the db number makes a huge difference. The SQL that makes the problem has around 350 db I would say, and about 200-250 of them have changes on daily basis (so are included in our daily differential backup).

 

The file backup set is not huge, at the end of the month (when we usually replace it with new backup set) it's barely 20GB. I can't see that being a problem to be transfered to tape.

 

What am I doing wrong? Alternatively, any suggestion how to transfer file backup sets to tape except the Transfer Backup Set option in Retrospect?

 

Why would a transfer of SQL backup from file set to tape set just stop? In the Executing window you can see the command being executed, however, the time counter which starts when the command starts just freezes at a certain time (usually at the beginning, after few minutes). Retrospect as a whole IS NOT frozen, it responds. It seems just the task out of whatever reason is not getting executed although it should.

 

I don't know if i have explained everything in detail but please ask questions if you need some particular info. Thanks in advance.

 

I would be really glad to sort it out, I am confused why I can't transfer that set.

 

Cheers.

Darko.

[nekr0phage]

Hi Darko,

 

I think you may have answered your own question with the number of databases per instance. 350 is a rather large number. When transferring the whole backup set every time there is going to be a lot of matching that needs to be performed, this might be what's locking up the execution. What happens if you perform a transfer snapshot and disable the option to transfer any needed intermediate databases?

 

Why are you using file backup sets rather than disk backup sets? And why are you transferring the whole backup set every time rather than using transfer snapshots?

[darkod]

Hi, thanks a lot for your reply.

 

I am really stuck with this. I know Retrospect but not too much in depth.

 

I was not even aware snapshot transfer can help me, the "logical" choice of wording was transfer backup set as I was afraid something might be left out.

 

Sorry if I double the first post but just to make clear what is my goal:

 

1. Initially we had dedicated backup server which was doing daily backups every working day at night. So, disk-to-disk backup I believe it is called. During the next day I was simply copying the .rbf files on external USB HDDs for off-site copy. Every day.

 

2. Because of enlargement of data (which is still not too big in size) we decided the last step, for the off-site copy to be a tape rather then external USB HDD. So what I am trying to do now, the backup server is still doing the backups on its internal drives every night but the transfer of those backup sets (files) the next day to be on tape rather than external USB drives.

 

The logical wording for me seemed Transfer Backup Set as it transfers all files from the internal drives of my backup server to the tapes. I definitely don't want anything left behind.

 

Would snapshot transfer do the same for me? A snapshot sounded to me like just the list of the files, not the actual files. I need to have everything off-site of course, completely.

 

Open to any suggestions as I agree there might be better procedures than what I am doing?

 

Thanks a lot in advance. Cheers.

[nekr0phage]

Performing a snapshot transfer will allow you to select specifc snapshots and their associated files for transfer. Rather than transferring the entire backup set every time, you can transfer just the most recent snapshot of each source which will save you space on the tapes. You can find info on backup set and snapshot transfers starting on page 90 of the 7.0 user's guide.

 

Also, I would recommend switching to disk backup sets rather than file unless you have a specific reason not to. Disk backup sets are designed to be more fault tolerant and give you the option to groom rather than having to recycle.

[darkod]

Thanks. Few questions please...

 

1. By saying "switching to disk backup sets" do you actually mean tape backup sets? A disk backup set is different to tape, right?

 

2. I can't seem to be able to forget (delete from the list) a tape backup set. It was one of the tapes I used during research what would work better, and I can't add the same tape to another backup set until the first one is deleted (which is logical). However, when I go in the Backup Sets option, click on the backup set and click Forget, I confirm that I want to forget that backup set and then Retrospect seems to keep working but it takes ages.

 

I know backup sets might have thousands of files and loads of snapshots, but I left it running for maybe 30-40min and it still didn't delete the backup set. Retrospect is not completely frozen, I can minimize it and maximize it again, but it won't accept any task until the task to forget the backup set is finished. And it doesn't seem to finish it for a while...

 

Is that a result of too many files/snapshots? Am I doing something wrong? I would say the procedure to forget a set is rather simple. What is the best way to do it, or should I just leave it for a while until it finishes? One hour, two hours?

 

Thanks. Cheers.

 

EDIT: PS. I have managed to forget (delete) the tape backup set I mentioned. It was just a matter of waiting long enough for Retrospect to do the forgetting. Cheers.

[nekr0phage]

Darko,

 

By disk backup set I mean 'Disk' backup set. This is quite different from tape, and I would recommend Disk in place of File. So you would have Disk backup sets on your hard drives, then you would transfer from Disk to Tape.

 

You are right, forgetting a backup set is a very simple process. When you select 'Forget' that simply severs the link between Retrospect and the catalog file for that backup set. No data of any sort is deleted. If simply trying to forget a backup set takes a long time I would suspect a problem somewhere, most likely with the configuration files.

[darkod]

Thanks Foster.

 

I will think about introducing the Disk backup sets although it looks like we will move only to Tape backup sets soon (because of data size, not extremely large, but still too expensive to buy internal HDDs to follow the data growth).

 

So, most probably Retrospect will back up direct to Tape backup sets.

 

On another note. When I am backing up SQL server (with the SQL agent of course).

 

What is the bext practice to make sure all of the SQL databases present are backed up?

 

I would assume the first backup is a full one (the simple Backup option when creating the script), which should mean all databases. Afterwars, only the modified databases will be backed up in full.

 

I thought it is enough just to select the SQL Container and that would automatically select all databases, including later if a new database is added (created). Am I right? How to make sure no database is missed?

 

Thanks. Cheers.

[nekr0phage]

Darko,

 

Yes, you are right. Selecting the SQL Server container will resolve to all available databases every time the backup runs. Any new databases will be found at this time, and old ones will no longer be listed. Leaving the SQL backup option to 'full' for every backup would be optimal if you have enough space for it.