Backing up and restoring a LIVE database in SQL Server 2000

Database Backup
Microsoft SQL Server 2000
I am using SQL Server 2000, and I backup and restore my LIVE database to a new backup server every night. Do you have any ideas as to why my restored database runs slow when compared to the LIVE database? Do I need to rebuilding indexes? Is there any way of backing up indexes as part of the main backup?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Indexes are backed up as part of the full database backup.

One reason why the database will be running slower is because the live database has most of the database in cache already, while the server you are restoring to does not have any of the data in cache. All the data needs to be loaded off disk for each query until all the needed data is cached in RAM.

Depending on the system configuration of your live and backup database servers the slow response time of the backup system could be normal.

Does the performance ever increase to normal? What’s the hardware configuration for each server?

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    69,125 pointsBadges:
  • Ritchie1
    I would expect the performance of the new database (recovered from backup) to be better than the Live database because the indexes would have been built fresh - i.e. no fragmentation etc, and it would not be handling the load of the Live database. Regarding holding data in cache, this would only really be a factor if the RAM available was large enough to store a significant percentage of database in RAM, i.e. huge RAM or very small database. In a normal system where RAM is a very small proportion of database size, the cache is regularly flushed out. However the tempdb - stored on hard-disk - is often large on a Live system which has not been restarted in a while. Tempdb does store data and aid query speed but I would still expect fresh indexes to outway a populated tempdb. Are you using a backup file to restore (which includes indexes) or are you using another method e.g. data transfer wizard etc (which will not copy indexes). Also are both servers of similar standard - eg RAM, Processor, Hard-drive space etc.
    210 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: