If there was have to be compelled to restore a SQL Server information backup to associate older version of SQL Server there was “greeting” with error messages on the lines of
Msg 3169, Level 16, State 1, Line 1
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally. OR
Msg 3241, Level 16, State 7, Line
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
And the tried backup restore would fail.
The reason for this behavior is that by default SQL Server doesn’t permit restoring a information backup to associate older version of SQL Server, despite the compatibility level of the information the backup was taken from.
However, there’s some way to revive a information to a more recent SQL Server version.
If there’s access to the live information backup was taken from:
- Start SQL Server Management Studio and connect with the instance wherever the information resides
- In the information context menu, navigate to Tasks | Generate Scripts…. this may invoke the Generate and Publish Scripts wizard
- In the Introduction step of the wizard click subsequent button
- In the opt for Objects step of the wizard certify that the Script entire information and every one information objects possibility is chosen and click on subsequent button
- In the Set Scripting choices step of the wizard:
- Select the Save scripts to a selected location possibility
- Specify whether or not the information objects and knowledge are written to one file, multiple files, in addition because the path and secret writing of the generated scripts
- Click Advanced
- In the Advanced Scripting choices window which will appear:
Specify the version of the SQL Server you wish the information improved to within the Script for Server Version possibility - In the Script for Server Version possibility, specify Schema and knowledge
- Set the subsequent choices to True:
- Script Logins
- Script Full-Text Indexes
- Script Triggers
- Click the OK button
- Click subsequent button
- In the outline step of the wizard, click subsequent button
- In the Save or Publish Scripts step of the wizard click the end button
- Execute the generated SQL script(s) against the older SQL instance
Although the procedure listed on top of ought to work:
It doesn’t migrate all of the information objects (e.g. sequences or queues).
It might fail if your information contains dependencies that SQL Server fails to acknowledge (e.g. dependencies to databases residing on connected servers)
To prime it all, if there’s no access to the initial information for any reason (e.g. migrating the information to associate off-site location with no network connectivity), the on top of procedure is unsuitable in this state of affairs.
Does that mean a backup cannot be improved to associate earlier SQL Server version? No, aloof from it – this can be wherever ApexSQL Diff and ApexSQL knowledge Diff acquire play.
ApexSQL Diff could be a SQL Server information schema comparison and synchronization tool that detects variations between information objects and resolves them while not errors. It generates comprehensive reports on the found variations and may automatize the synchronization method between live and versioned databases, backups, snapshots, script folders and supply management comes.
ApexSQL knowledge Diff could be a SQL Server knowledge comparison and synchronization tool that detects knowledge variations and resolves them while not errors. It will compare and synchronize live information and native or natively compressed database backups, script folders and supply management comes, and it generates comprehensive reports on the detected variations.
To restore the backup to and older SQL Server version.
On the target SQL instance, produce associate empty information to carry the information and objects improved from the backup
Start ApexSQL Diff
Click the New button within the Project management window:
In the supply panel:
Select Backup from the supply drop-down list.
Click the Add button and navigate to the folder wherever the computer file resides.
Select the backup and click on the Open button.
In the Destination panel:
Select information from the Destination drop-down list.
Specify the SQL instance wherever the information to that to recover the objects resides from the Server drop-down list.
Specify the authentication methodology for that SQL instance (and a sound set of credentials if you selected SQL Server authentication).
Specify the name of the information to that to recover the objects within the information drop-down list.
Click the Compare button within the bottom-right corner of the New project window
Select all the objects listed within the Main grid:
Click the Synchronize button within the Actions region of the ribbon’s Home tab:
Click subsequent button within the Synchronization direction step of the Synchronization wizard
Click subsequent button within the Dependencies step of the Synchronization wizard, since all objects are synchronal all depended objects area unit enclosed and this list are empty
In the Output choices step of the Synchronization wizard, opt for the Synchronize currently action and click on subsequent button:
Review the synchronization outline and warnings, if any, and click on the Synchronize button:
The Results window are shown upon finished synchronization process:
Start ApexSQL knowledge Diff
Click the New button within the Project management window:
In the supply panel:
Select Backup from the supply drop-down list.
Click the Add button and navigate to the folder wherever the computer file resides.
Select the backup and click on the Open button.
In the Destination panel:
Select information from the Destination drop-down list
Specify the SQL instance wherever {the knowledge |the info| the information}base to that to recover the data reside from the Server drop-down list
Specify the authentication methodology for that SQL instance (and a sound set of credentials if you selected SQL Server authentication)
Specify the name of {the knowledge |the info |the information}base you would like to recover the data to within the information drop-down list
Click the Compare button within the bottom-right corner of the New project window
In the Main grid, choose {the knowledge| the info |the information}base tables the data is to be recovered to:
Select the rows to be recovered from within the knowledge distinction read panel:
Click the Synchronize button within the Actions region of the ribbon’s Home tab:
Click subsequent button within the Synchronization direction step of the Synchronization wizard
In the Output choices step, of the Synchronization wizard, opt for the Synchronize currently action and click on subsequent button:
Review the synchronization outline and warnings if any and click on the Synchronize button:
The Results window are shown upon finished synchronization process:
And that’s virtually all that’s to that.
Want to know more about SQL Server Restore?
