dcsimg
Results 1 to 10 of 10

Thread: [RESOLVED] Restore a previous versioned SQL DB to newer SQL Server

  1. #1

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,688

    Resolved [RESOLVED] Restore a previous versioned SQL DB to newer SQL Server

    so as the title states. I need to restore or move a SQL 14 DB on to SQL 12 server. Its already populated with some data and has complex constraints, keys and functions etc. So its not allowing me to restore on SQL 12 as an error comes up stating its version 14 and needs to be 12 or older.

    Google is an acquaintance and says I should script out the entire database and run the script on the older sql server but as many of us know with large complex databases it never runs correctly.

    Upgrading or installing the newer 14 version is not an option at this time as this is in a production environment.

    Any ideas?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,413

    Re: Restore a previous versioned SQL DB to newer SQL Server

    You can go up from 12 to 14 but you can't go down from 14 to 12... unless you script out the DDL ad the data and run that (very carefully of course)...

    Of course upgrading to 14 is an option, it just might not be a viable one.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,327

    Re: Restore a previous versioned SQL DB to newer SQL Server

    Yeah, I think you're stuck with scripting this out I'm afraid. And the chances are the script won't just run smoothly on the target. You're going to have to debug it and remove any syntax/features that were introduced after 12. You might get lucky and have a smooth ride but you should probably go into this expecting some pain.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,334

    Re: Restore a previous versioned SQL DB to newer SQL Server

    You can use SSIS to move the data but you can't just restore a newer to an older version... I would do the SSIS rather than scripting... You can add a conditional split to add new rows or update rows based on the key that way
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,327

    Re: Restore a previous versioned SQL DB to newer SQL Server

    I would do the SSIS rather than scripting
    I was thinking scripting to create the structure which would just be a case of RClick on the DB in SSMS and select Script Database as > Create.

    You could probably build a DACPAC by selecting Tasks>Extract Data Tier Application and then import it to the new server using Import Data Tier Application. I'm not sure whether that'll let you go back versions though.

    I hadn't actually considered the data but, yeah, a straightforward data pump built in SSIS would be ideal.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,688

    Re: Restore a previous versioned SQL DB to newer SQL Server

    Quote Originally Posted by FunkyDexter View Post
    I was thinking scripting to create the structure which would just be a case of RClick on the DB in SSMS and select Script Database as > Create.

    You could probably build a DACPAC by selecting Tasks>Extract Data Tier Application and then import it to the new server using Import Data Tier Application. I'm not sure whether that'll let you go back versions though.

    I hadn't actually considered the data but, yeah, a straightforward data pump built in SSIS would be ideal.
    Its shown that to use the scripting wizard which generates a more complete database script possibly?
    Right click on database > Tasks > Generate Scripts... Then you have to change some Advanced setting to get it to include the data, set the format and a few other things I read.


    Quote Originally Posted by techgnome View Post
    You can go up from 12 to 14 but you can't go down from 14 to 12... unless you script out the DDL ad the data and run that (very carefully of course)...

    Of course upgrading to 14 is an option, it just might not be a viable one.

    -tg
    Yea cant upgrade to 14 as then its a major risk and issue with all the other production db's on the server
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  7. #7

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,688

    Re: Restore a previous versioned SQL DB to newer SQL Server

    Quote Originally Posted by FunkyDexter View Post
    Yeah, I think you're stuck with scripting this out I'm afraid. And the chances are the script won't just run smoothly on the target. You're going to have to debug it and remove any syntax/features that were introduced after 12. You might get lucky and have a smooth ride but you should probably go into this expecting some pain.
    Im already dreading it as we needed to switch the website over from dev environment to production at the drop of a hat when the owner said the contract was approved and we need it up asap to accept th third party to start inputting orders (data)
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  8. #8

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,688

    Re: Restore a previous versioned SQL DB to newer SQL Server

    Quote Originally Posted by GaryMazzone View Post
    You can use SSIS to move the data but you can't just restore a newer to an older version... I would do the SSIS rather than scripting... You can add a conditional split to add new rows or update rows based on the key that way
    Havent used SSIS yet so with a semi time crunch I wont have the time to get up to speed on that. Its a limited amount of data in various tables so no worries exporting it out and inserting it back in
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  9. #9

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,688

    Re: Restore a previous versioned SQL DB to newer SQL Server

    almost have it recreated on the production server with data imported. Just need to re-sync from this mornings activity and change the website to connect to the new db copy on production sql server. So far so good
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  10. #10

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,688

    Re: [RESOLVED] Restore a previous versioned SQL DB to newer SQL Server

    So as of today I have the new copy of the database structure created and data ported over to the production database server.

    Seems using the Generate Scripts wizard along with the tweaking of Advanced settings generated a trouble free experience (so far as the final test will be when users start using it later today).

    Thanks for the help guys


    MS should really make something for backwards compatibility as even if its generating scripts for the entire db and then running it against your target server. Why do I have to do the scripts to maintain "compatibility"? Seems it should be so easy for them to do. If it finds an incompatible feature or function then warn the user it wont be ported etc.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width