Differential Restore Not Working

bigal

We are looking to start using SQL Backup over Litespeed as we had issues restoring the differential backups. But we are having the same problems with SQL Backup now !!

What we want is a full backup on a Saturday night and differentials each other day (Sun, Mon, Tue......). We are using Enterprise Manager to automate this process with scripts taken from the GUI.

Full Backup Is :

master..sqlbackup '-SQL "BACKUP DATABASE [metadata]  TO DISK = ''e:\Microsoft SQL Server\MSSQL\BACKUP\FULL_(local)_metadata_20051031 165609.sqb'' WITH NAME = ''Database (metadata), 10/31/2005 4:55:59 PM'', DESCRIPTION = ''Backup on 10/31/2005 4:55:59 PM  Database: metadata  Instance: (local)  Server: VIRGINMEGAUK49'', ERASEFILES_ATSTART = 7, COMPRESSION = 2" -U ms_admin -P <LOGINPASSWORD>azevYcjWaW4=</LOGINPASSWORD>'

Differenatial Backup Is :

master..sqlbackup '-SQL "BACKUP DATABASE [metadata]  TO DISK = ''e:\Microsoft SQL Server\MSSQL\BACKUP\DIFF_(local)_metadata_20051031 165735.sqb'' WITH NAME = ''Database (metadata), 10/31/2005 4:57:26 PM'', DESCRIPTION = ''Backup on 10/31/2005 4:57:26 PM  Database: metadata  Instance: (local)  Server: VIRGINMEGAUK49'', DIFFERENTIAL, ERASEFILES_ATSTART = 7, COMPRESSION = 2" -U ms_admin -P <LOGINPASSWORD>azevYcjWaW4=</LOGINPASSWORD>'

The backup seems to run fine for the Full and one differential then stops. If I try to copy the backup files over to another server it can restore the full backup but not the differential.

Our spec is
Microsfor Windows 2000 Advanced Server SP4
SQL Backup 3.2.0
SQL Server 2000 SP4

Is there anyone who has a similar set-up and has this working?

Any help gratefully recieved as we want to use SQL Backup as our backup solution but the results so far have not instilled much confidence..........

Thanks in advance

petey

What is the error message that you received upon failure of the differential restore?

bigal

The error I'm getting is as follows :

11/4/2005 10:12:23 AM: Restoring file: E:\Microsoft SQL Server\MSSQL\BACKUP\DIFF_(local)_metadata_20051031 165735.sqb 11/4/2005 10:12:23 AM: Restore started ...

---

SQL Server messages

---

Msg 3136, Level 16, State 1, Server VIRGINMEGAUK57, Line 1
Cannot apply the backup on device 'SQLBACKUP_9637485' to database 'metadata'.
Msg 3013, Level 16, State 1, Server VIRGINMEGAUK57, Line 1
RESTORE DATABASE is terminating abnormally.


I think I may be doing something wrong in the backup process. Do I need to have 1 job for the full backup and 6 seperate jobs for the differential backups all going to different files? At the moment I have 1 job for the full backup and 1 job for the differential backup that runs every night (except Saturday when the full backup is run) going to the same file.

Any help gratefully accepted

petey

No, you do not need to create 6 differential backup jobs unless you want to be able to restore the database to each of the 6 day's state at the time of the backup.

Is it possible that another job made a full database backup between the time you made the full backup and the differential backup you are now trying to restore?

In your example, I guess you restored the full backup made on 29 Oct, and now want to restore the differential backup made on 31 Oct. One reason the differential restore may be failing is because a full backup was made between 29 Oct and 31 Oct. You can run the following query to check:

SELECT backup_start_date, backup_finish_date, * FROM msdb..backupset WHERE type = 'D' AND database_name = 'metadata' AND backup_start_date > '29-Oct-2005' AND backup_start_date < '1-Nov-2005'

bigal

Our normal SQL Server backup was running at 21:30 (just while we were testing SQL Backup), would this mess things up?

I've disabled this now, so I'll monitor it over the weekend and see if we can restore the backups on Monday (7th Nov).

Thanks for the help

petey

If the full backup occured between the full and differential backups made by SQL Backup, then yes. You will need to restore the full backup made by SQL Server, then use the differential backup made by SQL Backup.

bigal

That sound like the problem.

I'll post on Monday with my results.

Again, thanks for the help.