DIfferential backups - Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DIfferential backups - Help

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-12 : 09:44:08

Leslie writes "I am new to SQL Server 7.0 and desperately need the help of a guru. I noticed a posting on SQLMAG.com and thought I'd run a problem I am having by you.

Basically I have a 25GB database and wish to perform differential backups, so here is what I did. Before proceeding, I created a Test database, took a full back-up to TAPE, took a differential backup to DISK after a time lapse and then tried to restore.

I created two backup devices, one for the TAPE called TestDevice and the other for DISK, called TestDeviceDiff to hold the differentials. So here goes:

1. I take a full back of the database to TAPE.
2. Take a differential backup to DISK.
3. In the event of a disaster the TAPE is sent to our backup location.
4. I restore the full back at the backup location with NORECOVERY to a new database name called Test2.
5. I restore the differential backup at the backup location with RECOVERY to the Test2 database.

Steps, 1 to 4 suceed, but step 5 fails.

The error is as follows:

Processed 112 pages for database 'Test2', file 'Test_Data' on file 1.
Processed 1 pages for database 'Test2', file 'Test_Log' on file 1.
Backup or restore operation successfully processed 113 pages in 0.417 seconds (2.202 MB/sec).
Server: Msg 3136, Level 16, State 1, Line 2
Cannot apply the differential backup on device 'TestDeviceDiff' to database 'Test2'.
Server: Msg 3013, Level 16, State 1, Line 2
Backup or restore operation terminating abnormally.

The syntax I have been using is as follows:

backup database Test to TestDevice with init
go

backup database Test to TestDeviceDiff with differential
go

restore database Test2
from TestDevice
WITH NORECOVERY,
MOVE 'Test_Data' TO 'c:\mssql7\data\Test2_Data.mdf',
MOVE 'Test_Log' TO 'c:\mssql7\data\Test2_Log.ldf'

GO

restore database Test2
from TestDeviceDiff
WITH RECOVERY

GO

Do you know if Differential backups restrict's one to having both the full backup and differentials on the same medium or device?. In my case as the full database is huge , TAPE makes sense. The idea is to put the differentials on DISK and transfer them via FTP or file copy to the backup location.

Also,could it be that I have to keep the database names the same to prevent this prolem?

I need your help,

Desperate Leslie"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources