Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-02-24 : 08:54:36
|
| yjpr writes "I am currently refreshing the Reporting Server Database from Production database(size 200mb) every night by using backup and restore as follows:Restore Database ProjectDB from disk = 'D:\MSSQL\BACKUP\RptBak\ProjectDB.bak' with move 'ProjectDB_log' to 'd:\Mssql\Data\ProjectDB.ldf', move 'ProjectDB_data' to 'd:\Mssql\Data\ProjectDB.mdf'I am working on MS SQL Server 2000.I take Transaction Log Backup from Production every hour everyday. I am planning to apply the transaction log every hour on Destination Server Database in order to refresh DB.I am confused whether to use WITH RECOVERY OR WITH NORECOVER or STANDBY while restoring a database.I am getting errors like Msg 4306 etc. when applying the log.Can anyone please help me how to apply incremental transaction log to the restored database(step by step - I need to automate the script)?I am basically looking for the simple & right t-sql script to Restore Full database backup every night from production server to reporting server and also the script to restore transaction log every hour from production server.I really appreciat your help.-YJPR" |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-02-24 : 12:25:27
|
| As far as I know, you can't do this and have your database in a recovered state, therefore the database is unusable..There are other ways to go about this though, you could always set up transactional replication so any changes made to the Production Database are replicated to the Reporting database, the restriction with this is that any replicated tables structures cannot be changed without taking that article out of the replication first..The other way is log shipping, this will also leave your Reporting Database in a read-only state, so not sure if this is what you are after either.. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-24 : 13:31:42
|
| You can use the STANDBY option, which would mean the reporting database would be in read-only mode. But your users would get disconnected each time the backup tlog runs as it needs exclusive access. This is usually why log shipping or custom log shipping like you've mentioned is not used for a reporting database. We use transactional replication. Depending on how new you need the database to be, you could get away with snapshot replication as well. So given this information, do you really want to go down the restore log approach?Tara Kizeraka tduggan |
|
|
|
|
|
|
Refresh the Report Server from Production DB.