July 25, 2007 at 8:02 am
Where is a good place to go to learn some basics and the different options for backup and restore? I'm a developer, learning more administrative functions.
We have a production database of about 300 GB for transactional processing and copy of that for reporting. They sit on different servers. Our current process is to do a backup of the prod db, copy the files to a new location, then restore them to the reporting server. We do this nightly. The problem is that it takes too long, around 5 hours, and due to the schedule the report server database isn't available until around 8:30 am. Sometimes the restore fails, and we get a data consistency error. Then we have no report database until 2 or 3 in the afternoon.
Looking for a solution or a primer on basic strategies to support this. One idea we had was to restore to a new name, if successful then we drop the old copy and rename the new one. This way the database is up all the time except for the drop and rename. If not successful, we're only a day behind. I have a test job that works, but I read the help and it says I can't do it if anyone is on the database. I'm not sure how to turn off the service so that no one can login during the drop and rename. Is there a listener like in oracle that can be turned off, then on?
Thanks for any help.
Myles
July 25, 2007 at 8:29 am
Forgot to say microsoft's MSDN too.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 25, 2007 at 8:29 am
For you req look for log shipping or replication. Log shipping will transfer the database fully and can be only in read only mode. Replication can be made to transfer only the data nneded and can be in read/write mode.
Then best source for reading is
BOL
sqlservercentral.com
sql-server-performance.com and few more
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 26, 2007 at 6:34 am
try this link=>
http://www.sqlugcr.net/Portals/23/Sesión%209-LogShipping.pdf
and this also,
http://msdn2.microsoft.com/en-us/library/aa198224(SQL.80).aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply