locked
Backing up data of dynamic management views RRS feed

  • Question

  • Hi,

    I want to analyze data present in dynamic management views of production environment. Instead of giving the access permissions to the person who is analyzing this, I want to take a backup of them and sent the same across. I think if I take database backup, the DMV data will not be backed up as we have both database/object leve DMVs as well as the server level DMVs. But we cannot restore the DMV which is available at server level. Please advide on how the DMV data can be moved across servers.

    Regards,
    Phani Note: Please mark the post as answered if it answers your question.
    Thursday, January 7, 2010 11:05 AM

Answers

  • Correct. A database backup do not in any way contain the data exposed by DMVs (with the exception of sys.db_db_index_phycial_stats since it reads the physical data to return the results).
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, January 7, 2010 1:29 PM

All replies

  • The DMV's are views so there is no 'data' in the views itself. What you can do is create a job that selects the data in the views and insert this data in another table...
    • Proposed as answer by onpnt Thursday, January 7, 2010 1:30 PM
    Thursday, January 7, 2010 12:47 PM
  • Hi Aiki,

    Thanks for the reply. I understand what you said. Based on that understanding only I wanted to confirm that database backup will not have DMV data captured on the source server.

    To be precise, is this statement true:
    "Database backup will not hold the DMV data which is captured because of the activity happened on that server".

    Thank you.
    Phani Note: Please mark the post as answered if it answers your question.
    Thursday, January 7, 2010 1:04 PM
  • Correct. A database backup do not in any way contain the data exposed by DMVs (with the exception of sys.db_db_index_phycial_stats since it reads the physical data to return the results).
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, January 7, 2010 1:29 PM