July 14, 2008 at 4:52 am
I have two server in two different location with SQL server 2005. one is main server other is backup server. on main server full backup is happening every after 12 hrs, differential backup is happening every after 4 hrs and log backup is happening every after one hour.
Every day i transfer full backup on other server and restore it. transferring full backup and restoring it takes lots of time. it will be faster if i restore only differential backup, but when i do so i get error.
pls tell me the procedure to restore differential backup.
July 14, 2008 at 5:29 am
Each differential backup is based off a full backup. You can only restore a diff if you have previously restored (with norecovery) the full backup that it is based off.
A differential backup is just a backup of the changes made in the DB since the last full backup. It's not a complete backup of the database.
since you are doing full backups every 12 hours and restoring on the other server every 24 hours, you will have to use the full backups.
Depending what you're doing with the other server, you could also consider log shipping or potentially database mirroring with a database snapshot
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2008 at 11:36 pm
A differential backup is just a backup of the changes made in the DB since the last full backup.
your backup startegy should be
FULL BACKUP - WEEKLY
DIFF BACKUP - DEFINED INTERVALS
TRANSACTION LOG BACKUP - REGULAR INTERVALS
July 14, 2008 at 11:54 pm
shahbaz.oradba (7/14/2008)
your backup startegy should be
Backup strategies vary based on the size of the system, the available space for backups, the time allowed for restore and a number of other factors. You can't say there's one and only one way yo do a backup strategy.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2008 at 12:05 am
Thanks u are right:)
July 16, 2008 at 12:08 am
Gila,
Can we perform the restoration strategy this below manner..
1 Full say full1, full2
2 Differential say diff1, diff2, diff3....
3 Transaction say trn1, trn2....
When restoring the database.. Suppose u have already restored the full backup full1... and after that can u just maintain the other database by restoring the diff1, diff2, diff3.... and trn1, trn2,....
July 16, 2008 at 1:00 am
Abhijit (7/16/2008)
Gila,Can we perform the restoration strategy this below manner..
1 Full say full1, full2
2 Differential say diff1, diff2, diff3....
3 Transaction say trn1, trn2....
When restoring the database.. Suppose u have already restored the full backup full1... and after that can u just maintain the other database by restoring the diff1, diff2, diff3.... and trn1, trn2,....
Not if you're taken a full backup between any of those diffs.
Differential backups are just the changes made to a database since the last full backup. Since they are based on the last full backup that's the only backup they can be restored to.
Transaction logs can be applied regardless of the full/diff backup status, providing the first log backup used starts with an LSN before the full/dff that you're restoring the log to, and the log chain is unbroken.
Example time. Say this is the chronological sequence of backups taken.
Full 1
Tran 1
Tran 2
Diff 1
Tran 3
Tran 4
Diff 2
Tran 5
Tran 6
Full 2
Tran 7
Tran 8
Diff 3
Tran 9
Tran 10
Diff 4
Tran 11
Tran 12
If you need to restore that database to a point after Tranlog backup 12, the restore paths are:
Full 1, Diff 2, Tran backups 5,6,7,8,9,10,11,12
Or
Full 2, Diff 4, Tran backups 11,12
If you try to restore diff 4 onto full backup 1 (or full backup 1 with diff 2) you will get an error message.
Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2008 at 1:12 am
It menas if i'll not take the full2 then I can restore the database in below sequece...
Full 1, Diff 4, Tran backups 11,12
correct?
July 16, 2008 at 1:14 am
Abhijit (7/16/2008)
It menas if i'll not take the full2 then I can restore the database in below sequece...Full 1, Diff 4, Tran backups 11,12
correct?
Yes, if full 2 was not taken, then you can use the latest diff (since it will be based off the first full backup) and then the remaining tran logs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2008 at 1:18 am
GilaMonster (7/16/2008)[hr
If you need to restore that database to a point after Tranlog backup 12, the restore paths are:
Full 1, Diff 2, Tran backups 5,6,7,8,9,10,11,12
Or
Full 2, Diff 4, Tran backups 11,12
There are other valid restore paths as well:
Full 1, Tran backups 1,2,3,4,5,6,7,8,9,10,11,12
Full 1, Diff 1, Tran backups 3,4,5,6,7,8,9,10,11,12
Full 2, Tran backups 7,8,9,10,11,12
Full 2, Diff 3, Tran backups 9,10,11,12
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2008 at 10:10 pm
Gila,
I have following backup sequence
full-1 - time - 0130
tran-1- - 0200
diff - 1 0200
tran-2 0300
tran-3 0400
tran-4 0500
tran-5 0600
diff-2 0600
tran-6 0700
tran-7 0800
tran-8 0900
tran-9 1000
diff-3 1000
tran-10 1100
tran-11 1200
tran-12 1300
after restoring full backup -1, i tried restoring diff-3 backup and tran-10,11,12. but i m getting following error
[font="Arial"]TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Restore failed for Server 'SANKALP-2\AISPL'. (Microsoft.SqlServer.Express.Smo)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476%5B/font%5D
pls help
July 17, 2008 at 11:57 pm
All of the backups, other than the last transaction log backup must be restored WITH NORECOVERY. Once any backup has been restored WITH RECOVERY, no more backups can be applied (differential or log).
The error indicates that you restored one of the backups WITH RECOVERY, then tried to apply another backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2008 at 10:13 pm
Mr. Gail Shaw
with non recovery option my restoration procedure works.
thanx a lot
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply