Tlog backups - indicate no data change

Question

Tlog backups - indicate no data change

sqlguy80

SSCrazy

Points: 2765
More actions
March 24, 2017 at 12:38 pm

#323233

Hi,
For the db backup restore project moving ~ 1200 dbs ( some vldbs 500 GB as well ) from 32 bit sql server 2008 old to 64 bit new hardware sql server 2014 after the full backup is taken at soucre and restored WITH NO RECOVERY
After the final cut off, is it ok to just 2 Transaction Log backups and once the size of T log backups are same, I assume it confirms no data has changed, at that point
just restore the 2 Tlog backups with no recovery and last step restore database with recovery . CORRECT? or did I miss anything?
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

That is the correct process, but the assumption on data change is not appropriate.

If you really want to ensure data is not changing, I recommend you take the applications offline and then perform the final backup when there are 0 connections to the database.

If you cannot take that much time, then explore an alternative such as logshipping to move the databases from old server to new server.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

Or set the dbs to restricted_user if you app login doesn't have permissions to access them this way. Drain off the connections and do the log backup

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Steve Jones - SSC Editor - Friday, March 24, 2017 3:12 PM
Or set the dbs to restricted_user if you app login doesn't have permissions to access them this way. Drain off the connections and do the log backup

Key point in there is "if". Too many apps connect as a sysadmin so connectivity would be unaffected.

On the flipside, a quick offline, then online and set to single_user immediately followed by the log backup would be an option here in lieu of the "restricted_user".

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

sqlguy80 - Friday, March 24, 2017 12:38 PM
Hi,
For the db backup restore project moving ~ 1200 dbs ( some vldbs 500 GB as well ) from 32 bit sql server 2008 old to 64 bit new hardware sql server 2014 after the full backup is taken at soucre and restored WITH NO RECOVERY
After the final cut off, is it ok to just 2 Transaction Log backups and once the size of T log backups are same, I assume it confirms no data has changed, at that point
just restore the 2 Tlog backups with no recovery and last step restore database with recovery . CORRECT? or did I miss anything?
Thank you

My recommendation would be to do a "Tail Log" backup to set the source DB to a "recovery" state so that no new transactions can be inserted, deleted, or updated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

To repeat what I said on your near duplicate post on this topic...

My recommendation stands as it did on the other thread.

1. I assume you're doing normal full backups at the very least. If they're not being done daily, that's fine but run all full backups no earlier than the day before you're ready to do the migration.
2. As soon as the backups are done for step #1 above, begin and complete the full restores with NO RECOVERY.
3. About an hour before you want to complete the migration, turn off any log file backups and do a DIF backup for all of the databases.
4. Once step 3 is complete, immediately begin the restore of the DIFs. Again, use with NO RECOVERY.
5. When you're ready to complete the migration, do TAIL LOG backups of the databases. This will set each of the source databases to a "Recovery" mode where no one can make any additions or modifications. The "downtime" clock starts at the beginning of this step.
6. As soon as step 5 completes, immediately begin doing the restores of the log files. This time, use WITH RECOVERY. The "downtime" clock ends at the end of this step.

This will minimize your downtime to just several minutes rather than the hour or more that your proposed "FULL backup only" plan would entail. It will also guarantee that no one has slipped something into your databases because your network guys can't actually guarantee that someone local or a local job won't slip something in.

Obviously, you want a script that does each step for you. Using a little dynamic SQL, it should all be fairly easy.
If you try to shortcut around this, one of two things will happen... maybe both.
1. The total downtime will be a lot longer than it needs to be.
2. You can't guarantee that you've gotten every bit of data because of what I said before.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)