Post reply

Custom Views gone after a restore

  • October 4, 2011 at 3:08 am

    #248883

    Hi All

    I've restored a database from a SQL 2005 Express instance to a SQL 2008 instance and all my custom views have gone. Is this known issue or have a made a silly mistake? I have SA rights on both instances.

    If I try to create them again in the restored DB, I'm told that they already exist, but I cannot find them anywhere.

    Cheers

  • October 4, 2011 at 3:34 am

    #1390019

    How are you checking that they're gone? If creating them fails, they must be there.

    SELECT * from sys.views

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 4, 2011 at 3:46 am

    #1390028

    Hi

    When I use the SELECT * from sys.views command, it shows a list of what I can visually see using the Management Studio. However, when I try to recreate one of the views, using Create View, I am seeing

    There is already an object named 'viewname' in the database, but it is not showing in the results of SELECT * from sys.views

  • October 4, 2011 at 3:59 am

    #1390040

    Are you querying the right database?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 4, 2011 at 4:48 am

    #1390057

    🙂 Yes, I was checking the right DB.

    I think I've found the problem... After the restore, I ran some third-party 'upgrade' scripts on the DB, and it's only after this process that I've noticed that the views have gone. I've restored the DB and checked it prior to running the scripts, and the views are there, so it appears that I'll need to speak with our thrid-party supplier and find out why they've broken our database.

    Thanks Anyway

  • October 4, 2011 at 6:06 am

    #1390079

    Good idea.

    A restore recreates the database exactly as it was at the time of backup. There can't be objects missing after a restore.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 6 (of 6 total)

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