Convert SQL 2008 Database to SQL 2005 ( How to Use SQL 2008 Database to SQL 2005 )

In some times we need to back up Database from Microsoft SQL server 2008 and restore it in Microsoft SQL server 2005 , But it is not possible to back up a higher version Database and restore in lower version with normal way

So that we have to try for an alternative method, here we use SQL script for this purpose

To use Database script method  to take Backup

Login in to your SQL 2008 with super user privilege >>>>>>

Right click the Database which you want to back up>>>  click “Tasks”

Click on ” Generate Script “

Click “ Next “

Select   “entire Database and all database objects” option

Click “Next”

In Output Type

Select “save script to a specific location ”

Select “Save file ” and “Unicode text”

And then Click on “ Advanced “

Change following fields

Script for server version = SQL server 2005

Types of Data to script = Schema and Data

Then Click “Ok”  >>>  Select destination location to save script

Click “Next” >>> Click “Next”

Click “Finish”

Restore Script in SQL Server 2005

Open the Script file in SQL Management studio

Change the .Mdf and .LDF location in the script before execute

    CREATE DATABASE [StockTraderDB] ON  PRIMARY

( NAME = N’StockTraderDB’,

FILENAME = N’c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StockTraderDB.mdf ,

 SIZE = 4352KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N’StockTraderDB_log’,

FILENAME = N’c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StockTraderDB_log.LDF’,

 SIZE = 6272KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

 

Then Execute

Enjoy !!!!