Restoring a SQL 2008 R2 .bak file to SQL 2008

Discussion in 'Databases' started by Pyronaught, Apr 11, 2014.

  1. I made the mistake of creating my dev site using SQL 2008 R2, not knowing that the target database on the live site is SQL 2008. Thus the backup made from the R2 version does not restore to the older 2008 database. I can't delete the 2008 database and recreate it as an R2 because the connection string will change and I have a lot of apps in the field with the connection string hard coded into them. So how can I restore an 2008 R2 database into the older 2008 version? Is there a way to convert to the older version, or would attaching the database rather than using restore work?
     
  2. I'm trying to generate a script from the 2008 R2 version that is targeted to 2008, but I'm getting the following error when running the script generator:

    Microsoft.SqlServer.Management.Smo.SmoException: Could not read metadata, possibly due to insufficient access rights. at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
     
  3. It looks like this error occurs when the DB user is not granted the "view any definition" right in the server permissions settings. I've tried setting this from MSM Studio but the setting does not stick.
     
  4. Elshadriel

    Elshadriel Winhost Staff

    Try scripting it out as normal (i.e. 2008 R2) and then running it. Most of the SQL commands should work, and if they don't, you should be able to comment them out by editing the script file.
     
  5. That's what I'm trying to do. I was able to resolve the above permissions problem by not using the “Script Entire Database and Database Objects” option and using “Select specific database options” instead, then just selecting all options in the list.
     
    Elshadriel likes this.
  6. What I did was script out all tables and data as a 2008 DB, then created a new temporary 2008 database and ran the script to load it and test with. Once I verified everything was still functional, I did a backup from there and restored the backup to the original 2008 DB. This was a DNN site with several big modules, so there were a lot of tables and stored procs-- but the script method still worked without any problems.
     
    Elshadriel likes this.
  7. Elshadriel

    Elshadriel Winhost Staff

    Thanks for posting the solution. I'm sure it will help someone else out.
     

Share This Page