How to Restore Database in SQL Server 2011 (Denali) from an older SQL Server version?

Leave a comment (0) Go to comments

How to restore Database in SQL Server Denali from a backup file, which was created in older version?

Restoring a database from an older version to Denali is pretty Simple, and same as of previous versions. Backup’s can be restored with RESTORE DATABASE TSQL Statement.

I restored databases on Denali CTP1, from backup files, which were created in

  • SQL Server 2008 R2
  • SQL Server 2005

Restoring Database "AdventureWorksLT2008R2" from SQL Server 2008 R2 backup file

Used following restore statement to restore a AdventureWorksLT2008R2 database in SQL Server Denali.

-- Restoring Database named AdventureWorksLT2008R2 from a backup file, which was taken in SQL Server 2008 R2
RESTORE DATABASE [AdventureWorksLT2008R2] 
FROM  DISK = N'C:\share\AdventureWorksLT2008R2.bak' 
WITH  FILE = 1,  MOVE N'AdventureWorksLT2008R2_Data' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT2008R2.mdf',  
MOVE N'AdventureWorksLT2008R2_Log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT2008R2_1.ldf', 
NOUNLOAD,  STATS = 10
GO

OUTPUT :


Database was successfully restored and version of the database upgraded from 668 to 684 (internal version numbers). There was no change in the compatibility level. it was remain 100 only.

How to Restore Database in SQL Server 2011 (Denali) from an older SQL Server version? restore SQL Server 2008 backup to sql server 2011 restore SQL Server 2005 backup to sql server 2011 restore Database in SQL Server Denali restore Database in SQL Server 2011 Restore database backups in SQL Server 2005/2008/2008 R2 which were backed up in SQL Server Denali

Restoring Database "Northwind" from SQL Server 2005 backup file

RESTORE DATABASE [Northwind] FROM  DISK = N'C:\share\Northwind.bak' 
WITH  FILE = 1,  
MOVE N'Northwind' TO N'C:\MSSQL\DATA\Northwind.mdf', 
 MOVE N'Northwind_log' TO N'C:\MSSQL\DATA\Northwind_1.ldf',
  NOUNLOAD,  STATS = 10
GO

OUTPUT :

Database was successfully restored and version of the database upgraded from 611 to 684 (internal version numbers). There was no change in the compatibility level. it was remain 90 only.

100 percent processed.
Processed 400 pages for database 'Northwind', file 'Northwind' on file 1.
Processed 1 pages for database 'Northwind', file 'Northwind_log' on file 1.
Converting database 'Northwind' from version 611 to the current version 684.
Database 'Northwind' running the upgrade step from version 611 to version 621.
Database 'Northwind' running the upgrade step from version 621 to version 622.
Database 'Northwind' running the upgrade step from version 622 to version 625.
Database 'Northwind' running the upgrade step from version 625 to version 626.
Database 'Northwind' running the upgrade step from version 626 to version 627.
Database 'Northwind' running the upgrade step from version 627 to version 628.
Database 'Northwind' running the upgrade step from version 628 to version 629.
Database 'Northwind' running the upgrade step from version 629 to version 630.
Database 'Northwind' running the upgrade step from version 630 to version 631.
Database 'Northwind' running the upgrade step from version 631 to version 632.
Database 'Northwind' running the upgrade step from version 632 to version 633.
Database 'Northwind' running the upgrade step from version 633 to version 634.
Database 'Northwind' running the upgrade step from version 634 to version 635.
Database 'Northwind' running the upgrade step from version 635 to version 636.
Database 'Northwind' running the upgrade step from version 636 to version 638.
Database 'Northwind' running the upgrade step from version 638 to version 639.
Database 'Northwind' running the upgrade step from version 639 to version 640.
Database 'Northwind' running the upgrade step from version 640 to version 641.
Database 'Northwind' running the upgrade step from version 641 to version 642.
Database 'Northwind' running the upgrade step from version 642 to version 643.
Database 'Northwind' running the upgrade step from version 643 to version 644.
Database 'Northwind' running the upgrade step from version 644 to version 645.
Database 'Northwind' running the upgrade step from version 645 to version 646.
Database 'Northwind' running the upgrade step from version 646 to version 647.
Database 'Northwind' running the upgrade step from version 647 to version 648.
Database 'Northwind' running the upgrade step from version 648 to version 651.
Database 'Northwind' running the upgrade step from version 651 to version 652.
Database 'Northwind' running the upgrade step from version 652 to version 653.
Database 'Northwind' running the upgrade step from version 653 to version 654.
Database 'Northwind' running the upgrade step from version 654 to version 668.
Database 'Northwind' running the upgrade step from version 668 to version 669.
Database 'Northwind' running the upgrade step from version 669 to version 670.
Database 'Northwind' running the upgrade step from version 670 to version 671.
Database 'Northwind' running the upgrade step from version 671 to version 672.
Database 'Northwind' running the upgrade step from version 672 to version 673.
Database 'Northwind' running the upgrade step from version 673 to version 674.
Database 'Northwind' running the upgrade step from version 674 to version 675.
Database 'Northwind' running the upgrade step from version 675 to version 676.
Database 'Northwind' running the upgrade step from version 676 to version 677.
Database 'Northwind' running the upgrade step from version 677 to version 678.
Database 'Northwind' running the upgrade step from version 678 to version 679.
Database 'Northwind' running the upgrade step from version 679 to version 680.
Database 'Northwind' running the upgrade step from version 680 to version 681.
Database 'Northwind' running the upgrade step from version 681 to version 682.
Database 'Northwind' running the upgrade step from version 682 to version 683.
Database 'Northwind' running the upgrade step from version 683 to version 684.
RESTORE DATABASE successfully processed 401 pages in 0.173 seconds (18.108 MB/sec).

Can we Restore database backups in SQL Server 2005/2008/2008 R2 which were backed up in SQL Server Denali ?

NO, we can not restore higher version of databases to a lower version. It is not possible to restore a database from a backup of a newer version because SQL Server 2011 (Denali) database backups are not backward compatible with SQL Server 2008/2005.

If you try to do so, you will end up getting the following error message.

How to Restore Database in SQL Server 2011 (Denali) from an older SQL Server version? restore SQL Server 2008 backup to sql server 2011 restore SQL Server 2005 backup to sql server 2011 restore Database in SQL Server Denali restore Database in SQL Server 2011 Restore database backups in SQL Server 2005/2008/2008 R2 which were backed up in SQL Server Denali

If you liked this post then,

Subscribe to this Blog via Email:

Click here to Subscribe to FREE email updates from "DBATAG ", so that you do not miss out anything on SQL Server !!!

Tags : Restore database backups in SQL Server 2005/2008/2008 R2 which were backed up in SQL Server Denali ,restore Database in SQL Server Denali ,restore Database in SQL Server 2011

EOF - How to Restore Database in SQL Server 2011 (Denali) from an older SQL Server version?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.