May 22, 2017 at 9:48 am
I am using SQL server native tool to do the full, differential, transaction backup.A question, every time if we do a restore of a database, should we immediately do another full backup after the restore, so that the backup chain can work for next restore?
Or we don't need to.
Thanks,
May 22, 2017 at 9:54 am
Where are you restoring the database? I can't think of any reason to do another backup just because you've restored from a backup of the same database. The only thing that will stop a differential from working is taking a full backup, and the only things that will break a log chain are losing a log backup and changing the recovery model to Simple.
John
May 22, 2017 at 9:59 am
For example I restore a database on a test server from a full backup of production server.
May 22, 2017 at 11:24 am
sqlfriends - Monday, May 22, 2017 9:59 AMFor example I restore a database on a test server from a full backup of production server.
What recovery model are you using for the database you restored to the test server. A log backup required that a full backup has been taken.
May 22, 2017 at 11:30 am
I am using full backup recovery model on both test and production server.
What does 'A log backup required that a full backup has been taken.' mean?
What I was thinking, whenever I do a restore from a production database to a test server, after restore, I need to do a full backup again so that the backup chain is consistency on the test server.
Next time if I want to restore a database from a backup of testing server, it will be in consistent.
Thanks,
May 22, 2017 at 11:38 am
sqlfriends - Monday, May 22, 2017 11:30 AMI am using full backup recovery model on both test and production server.What does 'A log backup required that a full backup has been taken.' mean?
What I was thinking, whenever I do a restore from a production database to a test server, after restore, I need to do a full backup again so that the backup chain is consistency on the test server.
Next time if I want to restore a database from a backup of testing server, it will be in consistent.Thanks,
If you use a full backup from production to restore to test, you'll have to drop the test database first.
"A log backup requires that a full backup has been taken" means that in order to perform a log backup (which is probably done periodically by a job your DBA has created) a full backup must have been taken at some point. Trying to take a log backup when no full backup has been taken will throw an error.
May 22, 2017 at 2:12 pm
sqlfriends - Monday, May 22, 2017 11:30 AMWhat I was thinking, whenever I do a restore from a production database to a test server, after restore, I need to do a full backup again so that the backup chain is consistency on the test server.
Next time if I want to restore a database from a backup of testing server, it will be in consistent.
I don't understand what you're trying to do.
You want point-in-time restores of the *test* environment? Why is your test DB even in full recovery?
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
May 22, 2017 at 2:48 pm
GilaMonster - Monday, May 22, 2017 2:12 PMYou want point-in-time restores of the *test* environment? Why is your test DB even in full recovery?
Thank you. That was next.
May 22, 2017 at 2:52 pm
I say test server, in our case it is both dev/test server, I make it full recovery mode, in case we need to do a point of time restore.
May 22, 2017 at 2:56 pm
I did an experiment, -- restore a copy of backup from production to test server, then do a log backup for the test server database, then restore again from the database itself on the test server, in the restore wizard it shows both the full backup from production plus the log backup.
So this answers I did not need to do a full backup on the test server database after restore.
Somehow I remember I read somewhere it is a good practice to do a full backup after I restore a database from another server.
Thanks,
May 23, 2017 at 1:06 am
Well, you can't make any log backups until you've made a full backup. Therefore if you want point-in-time recovery capability in test/dev right from the start then yes, you need a full backup immediately and regular log backups thereafter.
John
May 23, 2017 at 1:42 am
John Mitchell-245523 - Tuesday, May 23, 2017 1:06 AMWell, you can't make any log backups until you've made a full backup. Therefore if you want point-in-time recovery capability in test/dev right from the start then yes, you need a full backup immediately and regular log backups thereafter.
Not in this case. The full backup that was used to create the database can be used as a base for log backups and differential backups.
It may be a good idea to take a full backup, so that it's in the expected place and with the expected name, but for the purposes of log backups running one isn't needed
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
May 23, 2017 at 8:19 am
Thank you, that answers my question
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply