How do you Copy Databases to SQL SERVER 2005 from various versions (6.0/6.5/7.0/2000) ?

 Posted by Pandians on 8/5/2009 | Category: Sql Server Interview questions | Views: 5067
Answer:

In SQL Server 2005, we can create a new database by restoring a database backup taken using SQL Server 7.0, 2000 or 2005.

SQL Server 6.5 or earlier are cannot be restored in SQL Server 2005.

But, backups of master, model and msdb that were created by using SQL Server 7.0 or 2000 cannot be restored to SQL Server 2005.

SQL Server 7.0 log backups that contain create-index operations cannot be restored to SQL Server 2000 or 2005.

SQL Server 2005 uses a different default path than earlier versions. To restore a database created in the default location of either SQL Server 7.0 or 2000 from backups, we must use the MOVE option.

When we install SQL Server 2005, any existing databases (SQL Server 7 or 2000) are automatically upgraded. If a database that was created by using SQL Server 6.5 or earlier has not been upgraded to SQL Server 2005, we have to convert it to SQL Server 2005 by using one of the following methods:

1. Use the Integration Services (SSIS) Import and Export Wizard.
2. Export the data into a data file using "BCP out" in Character mode and import the data into a SQL Server 2005 database using "BCP in" with the "-V".


Asked In: In General | Alert Moderator 

Comments or Responses

Login to post response