How do I rebuild views after database restore

Posted on 2011-10-17
Last Modified: 2012-05-12
We recently changed out one of our core database apps. It was a hosted solution and the new one we are hosting. Rather than do a dirty data conversion we opted to rekey the data. Fair enough.
With the previous application, we had (literally) hundreds of custom crystal reports for pulling data easily. Before the migration we pulled the majority of the core reports for hard reference for the future and the service was discontinued.
They have provided a hefty *.bak file from MSSQL server which contained our old database. Our intention is to have it available with our old reports in case of an audit or we need to look up data we may not have reported out earlier.
I have restored the database to a MSSQL 2008 server and can see data in the tables, however if I select rows from the views there is no data available. Of course the reports are built off of the views so they are displaying no data either once relinked.
What am I missing in regards to the Views not populating?
Question by:michaelaknight
    LVL 12

    Expert Comment

    check if the databasename before the table names in the queries in the views is correct (in case it has changed after the restore).

    To change them, script all views as alter/create by rightclicking on the views in object explorer and execute the script after changing the invalid details.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    It is difficult to say, I have an idea what may be causing the problem, but can you post a typical VIEW that is using tables that you know have data?  Preferably a simple one, as I suspect if we solve that, it will take care of the rest.
    LVL 9

    Author Comment


    in design view the tables do appear to have the same name, albeit without the 'dbo.' prefix


    here's the query for the ARLineItem view:

    SELECT     ARLineItemID, SiteID, OwnerID, ContractID, ARPaymentID, TransactionCodeID, TransactionCode, Description, TransactionDate, Amount, Balance, ApplyToLineID, 
                          Reference, GLAccount, PostedDate, NoLateFee, NoFinanceCharge, FinanceChrgApplied, CrdtCrdTransID, UserName, ApplyToChargeID, DueDate, UsageYearID, 
                          VoluntaryCharge, AssessmentID, TransferFromID, IsHistory, HistoryDate
    FROM         dbo.t_ARLineItem
    WHERE     (SiteID IN
                              (SELECT     SiteID
                                FROM          dbo.SiteSecurity
                                WHERE      (LoginName = CONVERT(varchar, SYSTEM_USER))))

    Open in new window

    the table t_ARLineItem does have data


    you are correct please rezone as necessary.
    LVL 3

    Expert Comment

    Views (except indexed ones) are no more than SQL.
    Are there any custom function/procedures using in Views?
    Are views encrypted?
    Whats about security?
    Also - check database options if they are the same (ex. DATEFORMAT).
     Could you script sample View and post here?

    LVL 75

    Accepted Solution

    As I suspected you have orphaned users.  Here is the problem:
     WHERE      (LoginName = CONVERT(varchar, SYSTEM_USER))))

    Run the following and you can confirm:
    sp_change_users_login 'report'

    The users that are displayed do not have the corresponding login.  Add the login back and you should be fine.
    LVL 9

    Author Comment


    There were two users reported out: 'Ray' and 'Queue'
    I recreated those logins and tied Ray to his old schema (which I scripted out). Now your query doesn't produce any data but the Views are still returning 0 lines. I think you're probably right about this being a security issue but I don't know how to proceed.

    "Are there any custom function/procedures using in Views?"
    not sure, Like I said it was a database given to us by a previous service provider. We are no longer using them, but for our records they gave us a *.bak of the database. Sorry, I'm not really used to having to recreate a database that's not one I made so any pointers would be great.

    "Are views encrypted?" -No

    "Whats about security?"
    Well there in lies the rub, when the database is restored all of the previous logins/schemas/roles are recreated with the database, but I'm not sure how those pertain to my current setup

    "Could you script sample View and post here?"
    Not sure what you mean here, rather than spam a long script do you mean to 'Script View as>>Create'? please advise
    LVL 9

    Author Closing Comment

    Bah, My Bad guys

    acperkins was partially correct, the problem was users. However not SQL logins. It must have been late last night, if you look at the query above it's selecting a SiteID from dbo.SiteSecurity (doh) since there was no translation for the currently logged in SQL user nor my Domain/user the query would obviously fail as I did not have permission to view any SiteIDs as far as the View was concerned
    Simply adding my Domain/user to that table with appropriate SiteID's populated the View.

    Thanks for the quick responses guys

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now