1

I created a full backup of my database using SQL Server Management Studio wizard, and then I perform differential partial backup each day via cmd to the same file.

SQL Server version is 2008 R2.

Here is the command I use to perform backup:

BACKUP DATABASE [db_name] 
TO DISK = N'path_to_backup\db_name.bak' 
WITH DIFFERENTIAL,  
     DESCRIPTION = N'Differential backup of the staging database', 
     NOFORMAT, NOINIT,  
     NAME = N'db_name-Differential_Backup', SKIP, NOREWIND, 
     NOUNLOAD, STATS = 10 
GO

I can't figure out how to restore the entire database (full backup and all of the partial backups).

I tried restoring full backup with NORECOVERY and PARTIAL options:

RESTORE DATABASE [db_name] 
FROM DISK = N'path_to_backup\db_name.bak' WITH 
FILE = 1,  
  MOVE N'db_name' TO N'path_to_sql_server_installation\MSSQL\DATA\db_name.mdf',  
  MOVE N'db_name_log' TO N'path_to_sql_server_installation\MSSQL\DATA\db_name_1.ldf', 
  NORECOVERY, PARTIAL, NOUNLOAD,  STATS = 10
GO

This works ok, but then I try to restore the partial backup using the following command:

RESTORE DATABASE [db_name] 
FROM DISK = N'path_to_backup\x12parsedb.bak' WITH  
FILE = 2
GO

This also completes without errors but it overwrites the data from the full backup and I'm left with only small fragment of data that was in this partial backup.

All of my backup data is stored in a single file, and there are around 130 partial backups.

I've tried to use Management Studio wizard but the result is the same.

Improve this question

1 Answer 1

Reset to default
1

Not sure what you expect from partial backups, but they are only useful for read-only filegroups:

partial backups are designed for use under the simple recovery model to improve flexibility for backing up very large databases that contain one or more read-only filegroups.

Differential backups are a different story, but they are not used as you use them. They are supposed to speed to up a full restore by skipping over some log backups, not as a replacement for full backups.

Is rather unclear what you actually have. I guess you have a full backup and 130 differential backups. In such case you can restore the original full and then apply the last differential.

My suggestion is to implement a correct backup plan (full recovery mode, periodic full backup and frequent log backups). There are many examples how to do this. Start with reading Introduction to Backup and Restore Strategies in SQL Server. Go over some tutorials like the ones from SQL Server Backup Best Practices and Articles. Understand what you're doing, don't guess.

Improve this answer
5
  • This is what it looks like in the Management Studio: i.snag.gy/rp6f3.jpg The database contains staging data which is deleted once the load is completed. But on some occasions, I have to restore everything back. This is what I'm trying to do now.
    – quickshare
    May 13, 2015 at 12:58
  • Read the manual Restore a Differential Database Backup. You restore the full first with NORECOVERY and then restore only the last differential (130) with RECOVERY. You will have then the same data as in the original DB at the moment the last differential was taken.
    – Remus Rusanu
    May 13, 2015 at 13:15
  • The problem in my case is that the entire database gets truncated after the backup is taken, so when I restore the latest differential I'll only have one day's worth of data (at least I finally understand that part now). Is there a way to 'merge' all of the differentials and restore them all at once?
    – quickshare
    May 13, 2015 at 13:32
  • 3
    I don't think you understand what a backup is. If the DB is 'truncated', you restore the truncated DB.
    – Remus Rusanu
    May 13, 2015 at 20:49
  • 1
    Also worth noting that, should your FULL backup be unrestorable or damaged, all those differentials are worthless, as are the TLogs. This is because the FULL backup is the base for the differentials/tlogs. If it doesn't work, nothing else will, either. You will have no restore capability and won't have a safety net unless you are keeping the data in sync on another server through replication methods.
    – Kris Gruttemeyer
    May 14, 2015 at 2:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.