SQL SERVER – Restoring SQL Server 2017 to SQL Server 2005 Using Generate Scripts

Let me answer the first question first – There is no way to backup and restore SQL Server 2017 to SQL Server 2005 with the help of any tools. In this blog post, we will learn about how we can generate scripts to achieve this task.

However, there is a small workaround if you really want to restore SQL Server 2017 to SQL Server 2005. Recently during Comprehensive Database Performance Health Check asked me a question that if there is any way they can restore one of their databases from SQL Server 2017 to SQL Server 2005. Let us see how I was able to do help my customer.

SQL SERVER - Restoring SQL Server 2017 to SQL Server 2005 Using Generate Scripts genescript

The only way we can restore SQL Server 2017 to SQL Server 2005 is to generate a script for Schema as well as data using SSMS. Let us see with the help of images, how we can do that.

First go to SSMS >> Database >> Right Click on it and go to Tasks >> Go to Generate Scripts…

SQL SERVER - Restoring SQL Server 2017 to SQL Server 2005 Using Generate Scripts genscript1

Continue forward with the Generate and Publish Scripts Wizards.

Solarwinds

SQL SERVER - Restoring SQL Server 2017 to SQL Server 2005 Using Generate Scripts genscript2

Select the option to either select independent objects or select the entire database.

SQL SERVER - Restoring SQL Server 2017 to SQL Server 2005 Using Generate Scripts genscript3

This is one of the most critical steps. Here you can specify for which version of SQL Server you are generating your scripts for.

SQL SERVER - Restoring SQL Server 2017 to SQL Server 2005 Using Generate Scripts genscript4

Next, select the option of Schema and Data.

SQL SERVER - Restoring SQL Server 2017 to SQL Server 2005 Using Generate Scripts genscript5

Now once again, you need to select necessary options over here. I usually enable all the options as the default selection does not script every single object in the database.

SQL SERVER - Restoring SQL Server 2017 to SQL Server 2005 Using Generate Scripts genscript6

Once you hit OK. It will bring you to the summary screen. Over here click Next. Please note the location where it is going to generate the scripts.

SQL SERVER - Restoring SQL Server 2017 to SQL Server 2005 Using Generate Scripts genscript7

Now the next screen will start generating schema and data.

SQL SERVER - Restoring SQL Server 2017 to SQL Server 2005 Using Generate Scripts genscript8

That’s it. We are done!

SQL SERVER - Restoring SQL Server 2017 to SQL Server 2005 Using Generate Scripts genscript9

Once you generate the script go to the location where you have saved the script and run that in SSMS. You will be successfully able to restore your database from SQL Server 2017 to SQL Server 2005.

 If you are familiar with any other method do let me know and I will be happy to publish that on this blog with the due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , , ,
Previous Post
SQL SERVER – Simple Method to Find FIRST and LAST Day of Current Date
Next Post
SQL SERVER – Selecting Random n Rows from a Table

Related Posts

1 Comment. Leave new

  • Hi Pinal,

    What is the purpose of restoring to SQL Server 2005 from SQL Server 2017? Is there any specific reason they are doing it?

    Thanks,
    Srini

    Reply

Leave a Reply

Menu