June 27, 2022 at 5:02 pm
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!
June 27, 2022 at 5:06 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 28, 2022 at 12:18 pm
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 28, 2022 at 1:18 pm
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 ?
June 28, 2022 at 9:49 pm
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.
June 29, 2022 at 3:02 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply