backup for restored or refreshed database

Question

Post reply

backup for restored or refreshed database

sqlfriend

SSC Guru

Points: 52794
More actions
July 15, 2011 at 1:36 pm

#254569

We have a maintence backup set for a server, to do full on Sunday, daily differenticial, transaction log every hour.
Now if I do a database restore from from an older or other newer version of the same database, is the database backup chain broken ?
So I should do a full backup right after the retore to keep database maintence backup in consistant, correct? If not, the full backup taken before on Sunday will no longer valid, correct?
Thanks

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234065 More actions · Answer 1

not entirely sure what it is you are getting at??

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

Thanks, I edited the original post.

What I mean, whenever I refresh a database from an older or newer version of it, I need to do a full backup right after it, is that right?

If not, the regular database chain is broken.

This is what I expericened, want to confirm that.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

sqlfriends (7/15/2011)
Thanks, I edited the original post.
What I mean, whenever I refresh a database from an older or newer version of it, I need to do a full backup right after it, is that right?
If not, the regular database chain is broken.
This is what I expericened, want to confirm that.

I do not see the correlation between refreshing a database and breaking the Transaction Log Chain.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sqlfriend SSC Guru Points: 52794 More actions · Answer 4

So for example I restore my testing database from a version from Production server.

Then I don't need to do a full backup after the restore.

So in the future if I want to restore the databaes in this state, I can use the full backup that was created for the old testing database, and use the following transactions to restore the database?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

sqlfriends (7/15/2011)
So for example I restore my testing database from a version from Production server.
Then I don't need to do a full backup after the restore.

A full backup of what? Testing or production?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52794 More actions · Answer 6

A full back up for the testing right after the restore.

Let me rephrase this:

1. for example the database name is AdvancedWork on testing server.

I restore the database with overwrite from a db of the same name from production server.

2. In order not to break up backup chains on testing server, do I need to backup the database right after the restore?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Depends. If you need to restore testing back to how it was at the time, you can just use the backup from production. If you change things before testing and want a consistent start point, then yes, you probably want a backup of testing after those changes were made.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52794 More actions · Answer 8

Thanks, that make sense.

What I meant is do the backup before I do any change.

All below is on testing server.

So when I do a restore from SSMS later to the beginning point, if choose restore from database, Usually SSMS is smart enough to list all the full backup, and diffecial and also transaction backup,

But for this case, it will not find the full backup if I didn't do a particular backup for the restored database. Correct? Because the fullbackup I copied from prodution will not be shown on the list.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

That's just a GUI thing, it gets the backup history from MSDB if it's there. Instead of selecting databases select device and navigate to the backup. Or write and save the RESTORE DATABASE command.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52794 More actions · Answer 10

Yes, so in the case we do need a restore from a full bk, a dif, and a series of transaction backup,

I would prefer to use SSMS to sort out those.

And in this case, I think do a full back up on the testing server really helps.