Post reply

Restore Copy of Live DB to Production Server?

  • July 19, 2012 at 8:19 am

    #278053

    Hi All,

    I am wondering about others views about restoring a copy of a production db to the same server, obviously using another name.

    I always under the belief that restoring any database on a Live box should only be done in time of emergencies as the restore flushes the procedure cache, and would have a negative impact on performance, but what are others thoughts?

    Many thanks in advance

    E

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

  • July 19, 2012 at 8:24 am

    #1514871

    So how do you release new production databases?

    Jared
    CE - Microsoft

  • July 19, 2012 at 8:25 am

    #1514873

    I don't want to go off-topic but..

    Upgrade scripts, Data-Tier Applications plus numerous other approaches

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

  • July 19, 2012 at 8:35 am

    #1514879

    Well... I'm not completely sure about this in 2008, they may have changed it to a database context. I can't find that info right now though. To be honest, I was not aware that in 2005 a database restore flushed the entire cache. However, I have not noticed any issues in doing this in our production environments on our 2005 servers. Best to wait until someone else chimes in for more experiences.

    Jared
    CE - Microsoft

  • July 19, 2012 at 8:35 am

    #1514880

    Ness (7/19/2012)

    Hi All,

    I am wondering about others views about restoring a copy of a production db to the same server, obviously using another name.

    I always under the belief that restoring any database on a Live box should only be done in time of emergencies as the restore flushes the procedure cache, and would have a negative impact on performance, but what are others thoughts?

    Many thanks in advance

    E

    restoring under a different name would be a new database with no procedure cache anyway.

    for me it depends on the purpose of the restore.

    if i needed to do an emergency update on production, based on a snapshot of a previous copy of the database, like undoing some changes in a couple of tables, then i'd lean towards using the same server.

    if this was for QA or dev testing, or even reporting, i'd try to use another machine, and leave the production server alone to minimize performance impacts.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 19, 2012 at 8:44 am

    #1514892

    Thanks for the responses but I'm not sure that I am being clear. Restoring clears the cache for the instance, so if you are hosting more than one client on a server you clear ALL of their plan caches

    I was after opinions of clearing the instance cache, just for a copy - I would normally advise a restore to a PS instance but I wanted to know what others thought

    BOL SQL2008R2:

    Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. As of SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval.

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

  • July 19, 2012 at 9:09 am

    #1514917

    Ness thank you; I didn't know a restore cleared the whole cache on the whole instance; i had to see it to believe it, adn Now i see your point more clearly.

    on one of my test servers, i ran this command and saw there were 18630 plans in the cache; after a resotre of an arbitrary database, by the time i ran the query again, there were only 23.

    that might be a good reason to consider whether a restore is worthy.

    select * from sys.dm_exec_cached_plans--18630

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 19, 2012 at 9:12 am

    #1514920

    No worries.

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

  • July 19, 2012 at 9:24 am

    #1514934

    Lowell (7/19/2012)

    Ness thank you; I didn't know a restore cleared the whole cache on the whole instance; i had to see it to believe it, adn Now i see your point more clearly.

    on one of my test servers, i ran this command and saw there were 18630 plans in the cache; after a resotre of an arbitrary database, by the time i ran the query again, there were only 23.

    that might be a good reason to consider whether a restore is worthy.

    select * from sys.dm_exec_cached_plans--18630

    Hi Lowell,

    I have a question, did you restore existing database (replaced existing from backup) in your test or created new database from backup?

    Just curious

    Thanks in advance

  • July 19, 2012 at 9:31 am

    #1514942

    Daxesh Patel (7/19/2012)[hr

    Hi Lowell,

    I have a question, did you restore existing database (replaced existing from backup) in your test or created new database from backup?

    Just curious

    Thanks in advance

    i tested it both ways; created a new database from a restore, and also restored an existing database with an existing backup.

    it didn't matter which method, the restore cleared out all my cache from that system view. it's something you should do to your own dev server, just to see it and know it yourself.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 19, 2012 at 10:20 am

    #1514978

    Lowell (7/19/2012)

    Daxesh Patel (7/19/2012)[hr

    Hi Lowell,

    I have a question, did you restore existing database (replaced existing from backup) in your test or created new database from backup?

    Just curious

    Thanks in advance

    i tested it both ways; created a new database from a restore, and also restored an existing database with an existing backup.

    it didn't matter which method, the restore cleared out all my cache from that system view. it's something you should do to your own dev server, just to see it and know it yourself.

    Yes I observed before I asked and tested after your suggestion

    What I observed was: I have a server where I have automated CHECKDB process, I checked event log and didn't find following message.

    "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations".

    Test on Dev box:

    test 1: Restored as new database ([ReportServer20120719]) from a backup taken on different server(SQL 2005)

    No of cached plans before/after was 10482/10526

    test 2: Restored as new database ([msdb20120719]) from a backup taken on same server

    No of cached plans before/after was 10552/10568

    I did perform this on SQL 2008 SP2 (Enterprise Edition (64-bit))

    Thanks

    Edit: here [/url]is good test with different operations on database and effect on cache

    Edit: After this I decided to run test on SQL 2005 SP3 (Enterprise Edition)and it does exactly what BOL says

    Restored as new database ([ReportServer20120719]) from a backup taken on same server

    No of cached plans before/after was 260/8 (also found message in event log)

    I guess, behaviour has been changed with different releases

  • July 20, 2012 at 5:42 am

    #1515458

    So...how if every restore will cause this problem then how can this be handled....like is there a hint or something which can be specified with the restore statement to avoid the clearing of the cache??

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • July 20, 2012 at 6:31 am

    #1515479

    vinu512 (7/20/2012)

    So...how if every restore will cause this problem then how can this be handled....like is there a hint or something which can be specified with the restore statement to avoid the clearing of the cache??

    I believe if you have sql2005, you have no way to avoid clearing of the entire instance cache.

    For sql2008 onwards it will drop the cache for only the database that is being resotred and you cannot avoid that (what if current database and restored database has different sets of objects?).

    Best way is to test in dev box with the same version of sql server

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply