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
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.