May 25, 2011 at 3:52 am
Hi there,
On my production server, I have a daily full backup at 01:00, differential backups from 07:00 to 22:00 every three hours, and transactional backups from 07:10 to 23:59 every fifteen minutes.
Now I am installing a report SQL Server. I am going to write a script that restores the full backup (lets say at 05:00) from the production server to the report server.
I need a script that is fired every 15 minutes that, from the production server to the report server, restores all the transactional backups that haven't been restored yet. Does anyone have a script that accomplishes this?
Thanks,
Raymond
May 25, 2011 at 4:41 am
You might want to look up a process called log shipping. I think that will do what you need much easier than what you're talking about.
Also, you won't be able to restore individual log files to the reporting server and have that database remain online for querying.
----------------------------------------------------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
May 25, 2011 at 4:44 am
Yep, I second that grant. You may setup the logshipping with standby mode and you can read the data.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
May 25, 2011 at 4:47 am
Configuring Log Shipping
http://msdn.microsoft.com/en-us/library/ms188698(v=sql.90).aspx
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
May 26, 2011 at 4:34 am
Thanks for your replies! I have set up Log Shipping succcessfully and it works okay.
However, one issue!
I would like to use the secondary server for reporting. When I set up "Restore Transaction Log" I can choose the following:
"No recovery mode" -> users cannot access database and restore will always succeed..... but then I can't use this server for reporting
"Standby mode" (without disconnecting users) -> users can access database (read-only) so reporting is possible..... but then restoring fails!
(Setting "disconnect users" is no option; users must have access or no access. Otherwise they will be disconnected every 15 minutes which is unacceptable.)
Is there a way to grant users read-only access to the databases, and still be sure the restores go well?
Thanks,
May 26, 2011 at 1:16 pm
Raymond van Laake (5/26/2011)
Thanks for your replies! I have set up Log Shipping succcessfully and it works okay.However, one issue!
I would like to use the secondary server for reporting. When I set up "Restore Transaction Log" I can choose the following:
"No recovery mode" -> users cannot access database and restore will always succeed..... but then I can't use this server for reporting
"Standby mode" (without disconnecting users) -> users can access database (read-only) so reporting is possible..... but then restoring fails!
(Setting "disconnect users" is no option; users must have access or no access. Otherwise they will be disconnected every 15 minutes which is unacceptable.)
Is there a way to grant users read-only access to the databases, and still be sure the restores go well?
Thanks,
For this purpose, I recommend "Database Mirroring".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply