Converting problem: easiest

Showing posts with label easiest. Show all posts

Sunday, March 25, 2012

copy DB from 2005 to 2000?

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.
================================================== ====

copy DB from 2005 to 2000?

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/...19b2-1e23-49c8-
8f0c-63fa18f26d3a&DisplayLang=en
Microsoft SQL Server 2000 Service Pack 4
http://www.microsoft.com/downloads/...n&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/subscript...ault.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/subscript...t/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 Busines
s
> Intelligence Development Studio;
> 3. On the Control Flow tab, drag a Transfer SQL Server Objects, and set th
e
> 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
> [url]http://www.microsoft.com/downloads/details.aspx?FamilyID=d07219b2-1e23-49c8-[/ur
l]
> 8f0c-63fa18f26d3a&DisplayLang=en
> Microsoft SQL Server 2000 Service Pack 4
> [url]http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=8E2D[/ur
l]
> 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:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> 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/subscript...t/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/subscript...ault.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/subscript...t/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.
========================================
==============

copy DB from 2005 to 2000?

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.htmHi
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.
======================================================sql sql

copy datasets between reports

i have a lil query that gets the values for my parameters..
what is the easiest way to copy this whole dataset into another report?
I wish that there were 'shared datasets' so that we could reuse business
logici just ended up opening the XML and cutting and pasting..
it didn't work correctly.. said that the 'aaron' datasource wasn't available
(it is a shared datasource, so that confused me)
I just ended up doing it by hand.. it would just be a LOT nicer to be able
to copy datasets without going into the source
-aaron
<aaron_kempf@.hotmail.com> wrote in message
news:%2378CAEv5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> i have a lil query that gets the values for my parameters..
> what is the easiest way to copy this whole dataset into another report?
> I wish that there were 'shared datasets' so that we could reuse business
> logic
>
>|||Thanks for the suggestion.
The new report controls in the upcoming VS2005 Beta 2 will just bind to
ADO.NET datasets, which would provide a client side solution to "sharing"
datasets. Shared datasets on the server side are on the wishlist for a
future release.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<aaron_kempf@.hotmail.com> wrote in message
news:ekPLfWv5EHA.3416@.TK2MSFTNGP09.phx.gbl...
> i just ended up opening the XML and cutting and pasting..
> it didn't work correctly.. said that the 'aaron' datasource wasn't
available
> (it is a shared datasource, so that confused me)
> I just ended up doing it by hand.. it would just be a LOT nicer to be able
> to copy datasets without going into the source
> -aaron
>
> <aaron_kempf@.hotmail.com> wrote in message
> news:%2378CAEv5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > i have a lil query that gets the values for my parameters..
> >
> > what is the easiest way to copy this whole dataset into another report?
> >
> > I wish that there were 'shared datasets' so that we could reuse business
> > logic
> >
> >
> >
>

Tuesday, March 20, 2012

Copy Database Wizard .... Missing!

Background:

Running SQL 7, have to move a database to someone else's server. They are running 2000.

Looks like the easiest way to do this is with the Copy Database Wizard!

Stupid Question 1: My SQL 7 Enterprise Manager doesn't have Copy Database Wizard listed in it's tasks for the server, per the instructions. Any idea why it's not?

Stupid Question 2: Are there any issues with using this procedure (assuming I can get the wizard to show itself) to transfer a 7 database over to 2000?

Many thanks!

Jerrekcopy database wizard is a fancy gui for detaching the db, copying mdf/ndf/ldf over, attaching db on both servers. check sp_detach/attach_db in bol.sql sql

Sunday, March 11, 2012

copy database easiest way!

Hi I have a database with tables, views, stored procedures and want to put a
copy of it on another server. Just wondering an easy way to do this? I have
Enterprise manager, SQL query analyzer as well.
--
Paul G
Software engineer.I prefer backup and restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> Hi I have a database with tables, views, stored procedures and want to put a
> copy of it on another server. Just wondering an easy way to do this? I have
> Enterprise manager, SQL query analyzer as well.
> --
> Paul G
> Software engineer.|||ok can I do this from Enterprise manager on the source machine?
thanks.
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> I prefer backup and restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> > Hi I have a database with tables, views, stored procedures and want to put a
> > copy of it on another server. Just wondering an easy way to do this? I have
> > Enterprise manager, SQL query analyzer as well.
> > --
> > Paul G
> > Software engineer.
>|||Do the backup on the source machine. Copy the backup file to the target machine. Then do the restore
on that (which you can do remotely in EM , connected to the target machine..).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> ok can I do this from Enterprise manager on the source machine?
> thanks.
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> I prefer backup and restore.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> > Hi I have a database with tables, views, stored procedures and want to put a
>> > copy of it on another server. Just wondering an easy way to do this? I have
>> > Enterprise manager, SQL query analyzer as well.
>> > --
>> > Paul G
>> > Software engineer.
>>|||Hi thanks for the additional information. Just had a question on the copy, I
found the back and restore as part of the tools of EM. I could not find the
copy, can you copy the dbase just like a standard file from explorer?
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> Do the backup on the source machine. Copy the backup file to the target machine. Then do the restore
> on that (which you can do remotely in EM , connected to the target machine..).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> > ok can I do this from Enterprise manager on the source machine?
> > thanks.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> I prefer backup and restore.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> >> > Hi I have a database with tables, views, stored procedures and want to put a
> >> > copy of it on another server. Just wondering an easy way to do this? I have
> >> > Enterprise manager, SQL query analyzer as well.
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >>
>|||Only if you detach it from the SQL Server or stop the SQL Server service
first...detach/attach is preferred
look up sp_detach_db in Books Online
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the
> copy, I
> found the back and restore as part of the tools of EM. I could not find
> the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the target
>> machine. Then do the restore
>> on that (which you can do remotely in EM , connected to the target
>> machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> > ok can I do this from Enterprise manager on the source machine?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I prefer backup and restore.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> >> > Hi I have a database with tables, views, stored procedures and want
>> >> > to put a
>> >> > copy of it on another server. Just wondering an easy way to do
>> >> > this? I have
>> >> > Enterprise manager, SQL query analyzer as well.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>>|||Yes, a regular file copy operation (explorer)...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the copy, I
> found the back and restore as part of the tools of EM. I could not find the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the target machine. Then do the
>> restore
>> on that (which you can do remotely in EM , connected to the target machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> > ok can I do this from Enterprise manager on the source machine?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I prefer backup and restore.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> >> > Hi I have a database with tables, views, stored procedures and want to put a
>> >> > copy of it on another server. Just wondering an easy way to do this? I have
>> >> > Enterprise manager, SQL query analyzer as well.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>>|||Hi Paul,
After the backup, Just copy the .BAK file to destination server. After that
restore the database in destination server using
Enterprise Manager. Otherwise use the RESTORE DATABASE from Query analyzer.
Thanks
Hari
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the
> copy, I
> found the back and restore as part of the tools of EM. I could not find
> the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the target
>> machine. Then do the restore
>> on that (which you can do remotely in EM , connected to the target
>> machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> > ok can I do this from Enterprise manager on the source machine?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I prefer backup and restore.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> >> > Hi I have a database with tables, views, stored procedures and want
>> >> > to put a
>> >> > copy of it on another server. Just wondering an easy way to do
>> >> > this? I have
>> >> > Enterprise manager, SQL query analyzer as well.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>>|||Here's a link that might help you:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the
copy, I
> found the back and restore as part of the tools of EM. I could not find
the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
> > Do the backup on the source machine. Copy the backup file to the target
machine. Then do the restore
> > on that (which you can do remotely in EM , connected to the target
machine..).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> > > ok can I do this from Enterprise manager on the source machine?
> > > thanks.
> > > --
> > > Paul G
> > > Software engineer.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> I prefer backup and restore.
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > >>
> > >>
> > >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> > >> > Hi I have a database with tables, views, stored procedures and want
to put a
> > >> > copy of it on another server. Just wondering an easy way to do
this? I have
> > >> > Enterprise manager, SQL query analyzer as well.
> > >> > --
> > >> > Paul G
> > >> > Software engineer.
> > >>
> > >>
> >
> >|||Hi,
In this approach the source database will be unavailable until you attach
the database back. This approach is not recommended in
a production server.
Thanks
Hari
SQL Server MVP
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23jtPRLOfFHA.1248@.TK2MSFTNGP12.phx.gbl...
> Only if you detach it from the SQL Server or stop the SQL Server service
> first...detach/attach is preferred
> look up sp_detach_db in Books Online
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
>> Hi thanks for the additional information. Just had a question on the
>> copy, I
>> found the back and restore as part of the tools of EM. I could not find
>> the
>> copy, can you copy the dbase just like a standard file from explorer?
>> --
>> Paul G
>> Software engineer.
>>
>> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the target
>> machine. Then do the restore
>> on that (which you can do remotely in EM , connected to the target
>> machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> > ok can I do this from Enterprise manager on the source machine?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I prefer backup and restore.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> >> > Hi I have a database with tables, views, stored procedures and want
>> >> > to put a
>> >> > copy of it on another server. Just wondering an easy way to do
>> >> > this? I have
>> >> > Enterprise manager, SQL query analyzer as well.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>>
>|||Hi thanks for all the replies. I created a sample database and then backed
it up. I then detatched it but could not find it to re-attatch it. I tried
to re-attatch the backup but it said this is not a correct file format. Also
when I actually copy it over do I use the database wizard?
--
Paul G
Software engineer.
"Ron Hinds" wrote:
> Here's a link that might help you:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> > Hi thanks for the additional information. Just had a question on the
> copy, I
> > found the back and restore as part of the tools of EM. I could not find
> the
> > copy, can you copy the dbase just like a standard file from explorer?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > Do the backup on the source machine. Copy the backup file to the target
> machine. Then do the restore
> > > on that (which you can do remotely in EM , connected to the target
> machine..).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > > Blog: http://solidqualitylearning.com/blogs/tibor/
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> > > > ok can I do this from Enterprise manager on the source machine?
> > > > thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > > >
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > >> I prefer backup and restore.
> > > >>
> > > >> --
> > > >> Tibor Karaszi, SQL Server MVP
> > > >> http://www.karaszi.com/sqlserver/default.asp
> > > >> http://www.solidqualitylearning.com/
> > > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > > >>
> > > >>
> > > >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> > > >> > Hi I have a database with tables, views, stored procedures and want
> to put a
> > > >> > copy of it on another server. Just wondering an easy way to do
> this? I have
> > > >> > Enterprise manager, SQL query analyzer as well.
> > > >> > --
> > > >> > Paul G
> > > >> > Software engineer.
> > > >>
> > > >>
> > >
> > >
>
>|||I ended up creating an empty database at the desintation machine and then
using export data wizard from the source dbase (EM). Seemed to work ok.
--
Paul G
Software engineer.
"Paul" wrote:
> Hi thanks for all the replies. I created a sample database and then backed
> it up. I then detatched it but could not find it to re-attatch it. I tried
> to re-attatch the backup but it said this is not a correct file format. Also
> when I actually copy it over do I use the database wizard?
> --
> Paul G
> Software engineer.
>
> "Ron Hinds" wrote:
> > Here's a link that might help you:
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> > > Hi thanks for the additional information. Just had a question on the
> > copy, I
> > > found the back and restore as part of the tools of EM. I could not find
> > the
> > > copy, can you copy the dbase just like a standard file from explorer?
> > > --
> > > Paul G
> > > Software engineer.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Do the backup on the source machine. Copy the backup file to the target
> > machine. Then do the restore
> > > > on that (which you can do remotely in EM , connected to the target
> > machine..).
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > > Blog: http://solidqualitylearning.com/blogs/tibor/
> > > >
> > > >
> > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> > > > > ok can I do this from Enterprise manager on the source machine?
> > > > > thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> > > > >
> > > > >
> > > > > "Tibor Karaszi" wrote:
> > > > >
> > > > >> I prefer backup and restore.
> > > > >>
> > > > >> --
> > > > >> Tibor Karaszi, SQL Server MVP
> > > > >> http://www.karaszi.com/sqlserver/default.asp
> > > > >> http://www.solidqualitylearning.com/
> > > > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > > > >>
> > > > >>
> > > > >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> > > > >> > Hi I have a database with tables, views, stored procedures and want
> > to put a
> > > > >> > copy of it on another server. Just wondering an easy way to do
> > this? I have
> > > > >> > Enterprise manager, SQL query analyzer as well.
> > > > >> > --
> > > > >> > Paul G
> > > > >> > Software engineer.
> > > > >>
> > > > >>
> > > >
> > > >
> >
> >
> >|||It seems like you've mixed things up....
When you backup the database, you get a file with the extention .BAK
(..actually it can have what ever extension you give it, but .BAK it the
"standard"). This .BAK file is just a normal file that you can copy as any
other file. If you copy this file to a location on your destination server,
you can restore this file on the destination server, and then you'll have an
exact copy of your database.
When you use sp_detach_db/sp_attach_db it's the actual files belonging to
the database you are working with (Database and logfiles). If you run
sp_detach_db, you'll have to copy the .mdf and .ldf file from the source
server to the destination server. When you've copied the files, you'll have
to run sp_attach_db BOTH on the source server (to get the database attach
again) and on the destination server (to get the database created).
Both the BACKUP/RESTORE and sp_detach_db/sp_attach_db syntaxes can be found
in Books On Line.
Regards
Steen
Paul wrote:
> I ended up creating an empty database at the desintation machine and
> then using export data wizard from the source dbase (EM). Seemed to
> work ok.
>> Hi thanks for all the replies. I created a sample database and then
>> backed it up. I then detatched it but could not find it to
>> re-attatch it. I tried to re-attatch the backup but it said this is
>> not a correct file format. Also when I actually copy it over do I
>> use the database wizard?
>> --
>> Paul G
>> Software engineer.
>>
>> "Ron Hinds" wrote:
>> Here's a link that might help you:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
>> Hi thanks for the additional information. Just had a question on
>> the copy, I found the back and restore as part of the tools of EM.
>> I could not find the copy, can you copy the dbase just like a
>> standard file from explorer? --
>> Paul G
>> Software engineer.
>>
>> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the
>> target machine. Then do the restore on that (which you can do
>> remotely in EM , connected to the target machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> ok can I do this from Enterprise manager on the source machine?
>> thanks.
>> --
>> Paul G
>> Software engineer.
>>
>> "Tibor Karaszi" wrote:
>>> I prefer backup and restore.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>>
>>>
>>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>>> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>>> Hi I have a database with tables, views, stored procedures and
>>> want to put a copy of it on another server. Just wondering an
>>> easy way to do this? I have Enterprise manager, SQL query
>>> analyzer as well. --
>>> Paul G
>>> Software engineer.|||thanks for the additional information.--
Paul G
Software engineer.
"Steen Persson (DK)" wrote:
> It seems like you've mixed things up....
> When you backup the database, you get a file with the extention .BAK
> (..actually it can have what ever extension you give it, but .BAK it the
> "standard"). This .BAK file is just a normal file that you can copy as any
> other file. If you copy this file to a location on your destination server,
> you can restore this file on the destination server, and then you'll have an
> exact copy of your database.
> When you use sp_detach_db/sp_attach_db it's the actual files belonging to
> the database you are working with (Database and logfiles). If you run
> sp_detach_db, you'll have to copy the .mdf and .ldf file from the source
> server to the destination server. When you've copied the files, you'll have
> to run sp_attach_db BOTH on the source server (to get the database attach
> again) and on the destination server (to get the database created).
> Both the BACKUP/RESTORE and sp_detach_db/sp_attach_db syntaxes can be found
> in Books On Line.
> Regards
> Steen
> Paul wrote:
> > I ended up creating an empty database at the desintation machine and
> > then using export data wizard from the source dbase (EM). Seemed to
> > work ok.
> >
> >> Hi thanks for all the replies. I created a sample database and then
> >> backed it up. I then detatched it but could not find it to
> >> re-attatch it. I tried to re-attatch the backup but it said this is
> >> not a correct file format. Also when I actually copy it over do I
> >> use the database wizard?
> >>
> >> --
> >> Paul G
> >> Software engineer.
> >>
> >>
> >> "Ron Hinds" wrote:
> >>
> >> Here's a link that might help you:
> >>
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> >> Hi thanks for the additional information. Just had a question on
> >> the copy, I found the back and restore as part of the tools of EM.
> >> I could not find the copy, can you copy the dbase just like a
> >> standard file from explorer? --
> >> Paul G
> >> Software engineer.
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> Do the backup on the source machine. Copy the backup file to the
> >> target machine. Then do the restore on that (which you can do
> >> remotely in EM , connected to the target machine..).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> >> ok can I do this from Enterprise manager on the source machine?
> >> thanks.
> >> --
> >> Paul G
> >> Software engineer.
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >>> I prefer backup and restore.
> >>>
> >>> --
> >>> Tibor Karaszi, SQL Server MVP
> >>> http://www.karaszi.com/sqlserver/default.asp
> >>> http://www.solidqualitylearning.com/
> >>> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>>
> >>>
> >>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >>> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> >>> Hi I have a database with tables, views, stored procedures and
> >>> want to put a copy of it on another server. Just wondering an
> >>> easy way to do this? I have Enterprise manager, SQL query
> >>> analyzer as well. --
> >>> Paul G
> >>> Software engineer.
>
>

Saturday, February 25, 2012

Copy a Table

In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?
Copy is in the popup menu if you right click on a table but there's no PasteOriginally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

Copy is in the popup menu if you right click on a table but there's no Paste|||[QUOTE][SIZE=1]Originally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

You can do this:

SELECT *
INTO B
FROM A

Notes:
B = TABLE NAME of COPY
A = TABLE NAME of ORIGINAL|||Originally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

Copy is in the popup menu if you right click on a table but there's no Paste

You can do paste in QA (shift+Insert or Cntrl+V)|||To copy only the structure with no data (minimally logged)

select * into newtable from oldtable where 0=1

To copy only the structure with data (minimally logged)

select * into newtable from oldtable

To copy data into an existing table (Fully Logged)

insert into newtable select * from oldtable

To Copy data into an existing table where it doesn't already exist (logged)

insert into newtable select * from oldtable where PK not in (select * from newtable)

HTH|||rhigdon: don't do NOT IN, you'll kill everybody else and possibly the server as well. do left outer join on key_field where key_field is null|||I've heard that before, I'll have to test the IO difference. Have you tested it?|||Right click the database then alltasks->export data
A wizard will appear...
Select the table, put the correct source and destination and at the end choose to drop the existing destination object and not append the data.

It's easy and simple. You can also select to mantain permissions, indexes, etc

Paulo

Originally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

Copy is in the popup menu if you right click on a table but there's no Paste|||yup, many times, plus evidence presented by others, plus ms white papers.|||I'm pretty sure you are right, going to do some testing but if you could share any of those white papers I would appreciate it.|||Well, you can't use:

where PK not in (select * from newtable)

anyway.

I'm sure you meant either:
where PK not in (select PK from newtable)
or:
where not exists (select * from oldtable where old.PK = new.PK)

...both of which generate the same optimizer plan, and oddly enough they ran slightly (insignificantly even) faster than the left outer join method, which threw in an extra step for filtering. Maybe because my test table only had about 20,000 rows.

I've use all three methods and never killed a database with them, but I would be interested in reading any MS white papers as well.

blindman|||I believe you are right, Blindman. MS has been tinkering with this part of the query optimizer, and I think the query plans are largely the same. Imagine my surprise when I was about to storm into a developer's cube to have him re-write such a query, when my own re-write did nothing to help.|||Well, the left join method did use a slightly different plan than the other two, so it could potentially be more (or less) efficient for larger tables. If anybody feels the urge to experiment please post the results.

blindman|||Yes BM, I meant

where PK not in (select PK from newtable)

Well, I am a little confused as I rewrote a query that did a NOT IN query to a 1.4 million row table from a 1000 row table and it cost me a total of 3014 logical reads, when I rewrote it in a left outer join it cost me 3126 logical reads so either I am doing something wrong or the difference is insignificant.

I have searched pretty hard but have been unable to find any MS whitepapers discussing this (although I did find an interesting book in my search that I have now ordered!)|||white papers, articles...this issue goes all the way to 6.0 times where non-clustered field was challenged with IN/NOT IN and the optimizer would revert to table scan in both outer and inner queries. it is possible that in the case of the PK, unless it is non-clustered, not in and let outer join would yield the same result, or close to be the same. this is not the point. the point is that IN clause is less preferrable than exists or left outer join. any more requests for articles?|||I agree with Mcrowley in that I beleive this used to be the case but is no longer true. Not sure what you mean by "any more requests for articles" but I take it to mean you don't want to continue this discussion so guess we'll leave it at that...|||I agree with Mcrowley in that I beleive this used to be the case but is no longer true. Not sure what you mean by "any more requests for articles" but I take it to mean you don't want to continue this discussion so guess we'll leave it at that...

Sunday, March 25, 2012

copy DB from 2005 to 2000?

copy DB from 2005 to 2000?

copy DB from 2005 to 2000?

copy datasets between reports

Tuesday, March 20, 2012

Copy Database Wizard .... Missing!

Sunday, March 11, 2012

copy database easiest way!

copy database easiest way!

copy database easiest way!

Saturday, February 25, 2012

Copy a Table

Converting problem

Blog Archive

About Me