I need to copy a database fomr Sql Server 2005 to Sql Server 2000. I tried
backup/restore but the restore did not work.
What is the easiest way to do this? I need to copy everything and the
database has the same name on both systems.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
Hi
I think only way is DTS, but ... try and you'll see
Mex
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C3719B-CB27-4455-89DB-D907CF9F2668@.microsoft.com...
> Hi;
> I need to copy a database fomr Sql Server 2005 to Sql Server 2000. I tried
> backup/restore but the restore did not work.
> What is the easiest way to do this? I need to copy everything and the
> database has the same name on both systems.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
|||Hi, David,
I understand that you would like to how to migrate your SQL Server 2005
database to SQL Server 2000.
If I have misunderstood, please let me know.
Due to the incompatibility issue, there is no very convenient way to do
this process. However you can try the following process:
1. Fully back up your SQL Server 2005 database;
2. Create a SQL Server Integration Services project via SQL Server Business
Intelligence Development Studio;
3. On the Control Flow tab, drag a Transfer SQL Server Objects, and set the
source connection, destination connection, SourceDatabase and
DestinationDatabase; if the DestinationDatabase does not exist, please
manually create an empty database in Enterprise Manager.
4. Execute the task with the following options:
DropObjectsFirst: True
IncludeExtendedProperties: True
CopyData: True
ExistingData: Replace
CopySchema: True
UseCollation: True
IncludeDependentObjects: True
CopyAllObjects: Flase
CopyAllTables: True
CopyAllViews: True
CopyAllStoredProcedures: True
CopyAllDefinedFunctions: True
CopyAllDefaults: True
CopyAllUserDefinedDataTypes: True
<other options>: False
CopyDatabaseUsers: False
CopyDatabaseRoles: False
CopySqlServerLogins: False
CopyObjectLevelPermissions: False
CopyIndexes: True
CopyTriggers: True
CopyFullTextIndexes: True
CopyPrimaryKeys: True
CopyForeignKeys: True
GenerateScriptsInUnicode: True
5. For the jobs, logins and passwords, please refer to the following KB
articles:
How to move databases between computers that are running SQL Server
http://support.microsoft.com/?id=314546
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/?id=246133
After transfer the logins and passwords, you may need to re-assign
server roles to the logins on the SQL Server 2000;
6. Manually create the related users and assign the permissions.
If you just want to transfer the data, the 5th and 6th steps can be
ignored. It is recommended that you install SQL Server 2005 SP2 and SQL
Server 2000 SP4 before the process.
Microsoft SQL Server 2005 Service Pack 2
http://www.microsoft.com/downloads/details.aspx?FamilyID=d07219b2-1e23-49c8-
8f0c-63fa18f26d3a&DisplayLang=en
Microsoft SQL Server 2000 Service Pack 4
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=8E2D
FC8D-C20E-4446-99A9-B7F0213F8BC5
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||They don't make this easy do they...
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Charles Wang[MSFT]" wrote:
> Hi, David,
> I understand that you would like to how to migrate your SQL Server 2005
> database to SQL Server 2000.
> If I have misunderstood, please let me know.
> Due to the incompatibility issue, there is no very convenient way to do
> this process. However you can try the following process:
> 1. Fully back up your SQL Server 2005 database;
> 2. Create a SQL Server Integration Services project via SQL Server Business
> Intelligence Development Studio;
> 3. On the Control Flow tab, drag a Transfer SQL Server Objects, and set the
> source connection, destination connection, SourceDatabase and
> DestinationDatabase; if the DestinationDatabase does not exist, please
> manually create an empty database in Enterprise Manager.
> 4. Execute the task with the following options:
> DropObjectsFirst: True
> IncludeExtendedProperties: True
> CopyData: True
> ExistingData: Replace
> CopySchema: True
> UseCollation: True
> IncludeDependentObjects: True
> CopyAllObjects: Flase
> CopyAllTables: True
> CopyAllViews: True
> CopyAllStoredProcedures: True
> CopyAllDefinedFunctions: True
> CopyAllDefaults: True
> CopyAllUserDefinedDataTypes: True
> <other options>: False
> CopyDatabaseUsers: False
> CopyDatabaseRoles: False
> CopySqlServerLogins: False
> CopyObjectLevelPermissions: False
> CopyIndexes: True
> CopyTriggers: True
> CopyFullTextIndexes: True
> CopyPrimaryKeys: True
> CopyForeignKeys: True
> GenerateScriptsInUnicode: True
> 5. For the jobs, logins and passwords, please refer to the following KB
> articles:
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/?id=314546
> How to transfer logins and passwords between instances of SQL Server
> http://support.microsoft.com/?id=246133
> After transfer the logins and passwords, you may need to re-assign
> server roles to the logins on the SQL Server 2000;
> 6. Manually create the related users and assign the permissions.
> If you just want to transfer the data, the 5th and 6th steps can be
> ignored. It is recommended that you install SQL Server 2005 SP2 and SQL
> Server 2000 SP4 before the process.
> Microsoft SQL Server 2005 Service Pack 2
> http://www.microsoft.com/downloads/details.aspx?FamilyID=d07219b2-1e23-49c8-
> 8f0c-63fa18f26d3a&DisplayLang=en
> Microsoft SQL Server 2000 Service Pack 4
> http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=8E2D
> FC8D-C20E-4446-99A9-B7F0213F8BC5
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
>
>
|||Hi, Dave,
No, the process is not as convenient as backup/restore database from SQL
2000 to SQL 2005. Actually, the process of moving all the database objects
from SQL 2000 to SQL 2005 is also not very simple. Please refer to the two
articles in the step 5 in my first reply for that process.
Please try the recommendations first. If you encounter any issues, please
feel free to post back.
Have a nice day!
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
No comments:
Post a Comment