June 25, 2010 at 3:09 pm
I've restored a Database (BAK) to a Report Server using a full backup that is done on the Primary Server. The Primary Server also performs Transaction Log backups every 15 minutes.
My question is after the Primary Server does a Transaction Log backup and do a copy/paste to the Report Server, how can I apply (restore) that Transaction Log backup to the Report Server's database? This will be an on-going process and using SQL replications are not an option.
Do I put the Report Server's database into another "mode" so I am to apply (restore) the transaction log?
please help
June 25, 2010 at 3:22 pm
You'd need to restore the full backup either WITH NORECOVERY or WITH STANDBY in order to restore further transaction logs. The logs will need to be restored with the same options. This means that the database is either inaccessible (norecovery) or readonly (standby)
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
June 25, 2010 at 3:25 pm
You have to rstore the full backup with norecovery so you can apply the logs.
Have you considered log shipping? Are you planning on using the copy on the RS server for reporting? If so, I'm not sure this solution will work well for you.
June 25, 2010 at 3:27 pm
D'oh. Yeah, what Gail said.
June 25, 2010 at 3:32 pm
Thanks for your replies, Gail and David.
SQL log shipping is not an option available to me. So I have to do this myself.
Yes, this will be a Reporting Server.
So for me to do this I should follow this...
You'd need to restore the full backup either WITH NORECOVERY or WITH STANDBY in order to restore further transaction logs. The logs will need to be restored with the same options. This means that the database is either inaccessible (norecovery) or readonly (standby)
...so I can keep performing Database and Transaction Log restores, and it'll be in READONLY mode so that all my Users can access it as needed?
June 26, 2010 at 2:55 am
rew-370421 (6/25/2010)
SQL log shipping is not an option available to me. So I have to do this myself.
Why is log shipping not an option?
You'd need to restore the full backup either WITH NORECOVERY or WITH STANDBY in order to restore further transaction logs. The logs will need to be restored with the same options. This means that the database is either inaccessible (norecovery) or readonly (standby)
...so I can keep performing Database and Transaction Log restores, and it'll be in READONLY mode so that all my Users can access it as needed?
Yes, providing you do all the restores WITH STANDBY. See Books Online for full details. Do note that you'll have to disconnect all users to do a log restore.
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
June 27, 2010 at 8:43 pm
Log Shipping is not an option due to internal management issues.
The Primary database is backed up every 15 minutes. I was hoping I could script a job to copy that file to the Report Server and then restore WITH STANDBY to it every 15 minutes, too. Is that NOT possible without DISCONNECTING everyone? If so, then a up-to-date Report Server is also NOT possible?
June 27, 2010 at 11:36 pm
rew-370421 (6/27/2010)
I was hoping I could script a job to copy that file to the Report Server and then restore WITH STANDBY to it every 15 minutes, too. Is that NOT possible without DISCONNECTING everyone?
No, to restore a log, all users must be disconnected.
If so, then a up-to-date Report Server is also NOT possible?
Depends what you mean by 'up to date'
To be honest, I'd never suggest log shipping (automatic or manual) as a method of keeping a report server up to date. Replication's a far better option. Why is transactional replication not an option here?
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
June 28, 2010 at 7:52 am
"up to date" meaning that when the Primary Server does a Transaction Log backup every 15 minutes, then that LOG Backup should then be applied to the Report Server at that time, too.
The Primary Server database is a VENDOR application & database. Doesn't using SQL replication update or make changes to the database objects it replications? I'm not allowed to touch VENDOR databases.
Is there a better method in keeping the Report Server database up-to-date with the Primary database?
June 28, 2010 at 7:55 am
rew-370421 (6/28/2010)
"up to date" meaning that when the Primary Server does a Transaction Log backup every 15 minutes, then that LOG Backup should then be applied to the Report Server at that time, too.
No problem there, you can restore the log as soon as it is copied over. You'll just have to disconnect all users first.
The Primary Server database is a VENDOR application & database. Doesn't using SQL replication update or make changes to the database objects it replications? I'm not allowed to touch VENDOR databases.
Transactional doesn't. It requires primary keys on all replicated tables, but it doesn't add columns or modify replicated tables
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
June 28, 2010 at 8:00 am
Thanks so much for that information, Gail!
This is all new to me, so I appreciate the help!
I shall try to write scripts to copy over the LOGS, DISCONNECT ALL USERS and then RESTORE WITH STANDBY that LOG backup.
...thanks
June 28, 2010 at 8:05 am
That'll work, but I strongly suggest that you consider transactional replication.
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
June 28, 2010 at 10:48 am
Thanks, Gail.
Question: with Transactional Replication, the Users are able to access the Reporting Database continuously?
June 28, 2010 at 2:00 pm
Yes
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply