April 23, 2009 at 1:00 pm
I have a transactional database running at a client site, with weekly full backups on Saturdays, nightly differentials M-F and hourly logs during weekdays - pretty standard. Now, what I need to do is produce a copy of this database (for reporting purposes) nightly Su-Th on our local servers which have limited connectivity to the client site (we can copy files via FTP, but direct SQL-SQL connectivity is not available). Currently, we're copying any new full and differential backups across nightly via FTP, but we seem to have run into a catch-22 regarding the restore process. I thought I'd be able to restore the full backup on Sunday, and the differentials on M-Th, leaving the database in read-only mode in between so that reports can be run. It doesn't seem like this is possible though. If I leave the database in a state where the next day's differential can be applied, it isn't accessible for reporting queries, and if I make it accessible for reporting I can't apply further differentials.
Am I missing something?
-Jesse
April 23, 2009 at 1:11 pm
You have to restore into STANDBY mode, instead of RECOVERY or NORECOVERY. Check the syntax of the RESTORE command in SQL Server 2005 Books Online.
There are limitations:
1. You have to kill all user connections before you can roll the reporting DB forward.
2. You cannot make any changes to the DB, including users and permissions.
April 27, 2009 at 1:09 pm
I want if differential backup cannot be applied during off hours of reporting users.
I had similar sitiuation where we automated the whole process.
April 27, 2009 at 1:58 pm
If you have a separate question, please start a new thread.
Otherwise, your post does not make sense. What are you looking for?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply