Database backup and restore

  • Hello,

    I've a user database which I want to migrate to new server, so I will be taking up a full backup and restore it on the new server.

    Now, I see there is a Tlog backup job running as well. So, Do I have to also take a tlog backup and restore it as well after I restore the full bacup with no recovery option? I see multiple tlog files meaning the .trn files for every few mins on the source server, but if I restore full back on new server with no recovery and then restore the latest tlog backup is that good? I do not see any differential on source server.

    Steps:

    Source server:-

    Take full backup

    Take a tlog backup

    Target server":-

    Restore the full backup with no recovery option

    Restore the tlog backup with db readable mode

    Sincerely!

  • If you're allowed to, take your own differential backup (or have one taken), then use that to bring the restored db current.  Much easier than restoring a long list of log files.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Are you trying to get up to the moment recovery, or do you just want a copy of the database and transactions that may, or may not, be in flight are immaterial? If you're just looking for a copy, run the backup and restore and be done. If you're trying to capture every possible transaction up to a moment in time, you can certainly get differential backups involved, but you may also have to use the log backups, depending on what you're going for. I'd suggest doing a search for a few terms: tail log backup, copy_only backup, recovery to a point in time.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • DBA wrote:

    Hello,

    I've a user database which I want to migrate to new server, ......

    This sounds like you will be taking the old server off line.

    Can you clarify your situation?

    Timing ?

    Business critical ?

    • This reply was modified 1 month, 2 weeks ago by  homebrew01.
    • This reply was modified 1 month, 2 weeks ago by  homebrew01.
    • This reply was modified 1 month, 2 weeks ago by  homebrew01.
  • Thanks for the reply. This was SSISDB so not sure why tlog where setup initially. I feel there is no need to apply .trn's as this is not transaction server ...like no inserts or updates like OLTP. Latest full backup was taken of the SSISDB and restored. So, it looks good.

  • DBA wrote:

    Thanks for the reply. This was SSISDB so not sure why tlog where setup initially. I feel there is no need to apply .trn's as this is not transaction server ...like no inserts or updates like OLTP. Latest full backup was taken of the SSISDB and restored. So, it looks good.

    There is history stored in the SSISDB database - and if that is important for your organization to keep and track then you would need to apply transaction log backups to restore to a point in time.  I would say that history isn't that important and wouldn't backup/restore the database anyways.  I would perform an export/import - reviewing all folders/projects and excluding those that are no longer utilized from the new system.

    The export process would keep a copy available to be imported if we find it is needed at a future date.  And - since the export is done as a .ISPAC file it can easily be imported into an Integration Services Project to be updated.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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