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

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/...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 database to another server

Hi;
I asked this once before but can't find the info now. I need to copy a
database from one server and put it on a second server. It is Sql Server 2000
and both servers are running Windows 2003 in a workgroup (not a domain).
I want to get all of the data and meta-data across. How can I do this? (All
I remember is it is very complex because the systems are not in a domain.)
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scri...p?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <david@.bogus.windward.net> wrote in message
news:C8DE23AA-9CF6-46E8-8358-A998B00470EA@.microsoft.com...
> Hi;
> I asked this once before but can't find the info now. I need to copy a
> database from one server and put it on a second server. It is Sql Server 2000
> and both servers are running Windows 2003 in a workgroup (not a domain).
> I want to get all of the data and meta-data across. How can I do this? (All
> I remember is it is very complex because the systems are not in a domain.)
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
|||Hi;
The post http://www.support.microsoft.com/kb/304692/ is for Sql Server 7
only. Is there a post somewhere for Sql Server 2000?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
|||Also, this post says how to restore a database - but not how to back it up.
How do I back it up?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"David Thielen" wrote:

> Hi;
> The post http://www.support.microsoft.com/kb/304692/ is for Sql Server 7
> only. Is there a post somewhere for Sql Server 2000?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
|||A quick look suggests that this should also work for 2000, but test in a test environment first. As
for how to backup, read in Books Online about the BACKUP DATABASE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <david@.bogus.windward.net> wrote in message
news:949DC479-E52A-44BE-970F-AB64E05459AD@.microsoft.com...[vbcol=seagreen]
> Also, this post says how to restore a database - but not how to back it up.
> How do I back it up?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
> "David Thielen" wrote:
sqlsql

Copy database to another server

Hi;
I asked this once before but can't find the info now. I need to copy a
database from one server and put it on a second server. It is Sql Server 200
0
and both servers are running Windows 2003 in a workgroup (not a domain).
I want to get all of the data and meta-data across. How can I do this? (All
I remember is it is very complex because the systems are not in a domain.)
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.comHave a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scr...sp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <david@.bogus.windward.net> wrote in message
news:C8DE23AA-9CF6-46E8-8358-A998B00470EA@.microsoft.com...
> Hi;
> I asked this once before but can't find the info now. I need to copy a
> database from one server and put it on a second server. It is Sql Server 2
000
> and both servers are running Windows 2003 in a workgroup (not a domain).
> I want to get all of the data and meta-data across. How can I do this? (Al
l
> I remember is it is very complex because the systems are not in a domain.)
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>|||Hi;
The post http://www.support.microsoft.com/kb/304692/ is for Sql Server 7
only. Is there a post somewhere for Sql Server 2000?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com|||Also, this post says how to restore a database - but not how to back it up.
How do I back it up?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"David Thielen" wrote:

> Hi;
> The post http://www.support.microsoft.com/kb/304692/ is for Sql Server 7
> only. Is there a post somewhere for Sql Server 2000?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>|||A quick look suggests that this should also work for 2000, but test in a tes
t environment first. As
for how to backup, read in Books Online about the BACKUP DATABASE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <david@.bogus.windward.net> wrote in message
news:949DC479-E52A-44BE-970F-AB64E05459AD@.microsoft.com...[vbcol=seagreen]
> Also, this post says how to restore a database - but not how to back it up
.
> How do I back it up?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
> "David Thielen" wrote:
>

Copy database problem

Hi
I was wondering if anyone knows why this is happening.
* I detach or take a database offline
* Make a copy of that database files and copy to a different directory on
the same server
* Attach the original database
* Rename the copy and attach - when doing this it still points to the
original file location?
If you are doing tthis using T-SQL, post it here.
When doing this, the device name will be the same, but not the file and path
of the 2 databases.
"Jaco" wrote:

> Hi
> I was wondering if anyone knows why this is happening.
> * I detach or take a database offline
> * Make a copy of that database files and copy to a different directory on
> the same server
> * Attach the original database
> * Rename the copy and attach - when doing this it still points to the
> original file location?
|||Hi
I am doing this using EM not T-SQL.
Thanks
Jaco
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> If you are doing tthis using T-SQL, post it here.
> When doing this, the device name will be the same, but not the file and path
> of the 2 databases.
> "Jaco" wrote:

copy database from one server to another

Hi:

I have a MSSQL 2005 database in current server, since we are going to shift to another server, I need to copy the database to the new server for testing.

Can you please let me know what is the best way to do that? I have tried to use detach and attach, or "back up the database and then restore on the other server.", but seems not working for me. I want step by step instruction since I am pretty new for the DB migration.

Thanks.

Jt

Hello Jt,

Do you know why the detach/attach and backup/restore are not working properly? They are created to do these kind of actions.

Have a look at the following article to move a database with attach/detach:http://support.microsoft.com/kb/224071

or to do the restore:http://msdn2.microsoft.com/en-us/library/ms186390.aspx

|||

Have a look atthis post on Scott Gu's blog, detailing the use of the recently releasedDatabase Publishing Wizard. It explains how to transfer a database schema and/or it's data to another server. It basically outputs a script file containing all the commands, which you would then save and execute on the server you wish to transfer it to.

Hope that helps.

Copy database - same server

Hi;
I want to copy a database on my server, creating another database (different
name) on the same server copying everything - tables, data, diagrams, stored
procedures - everything. (I am making a test DB that I can use for my unit
tests.)
What's the easiest way to do this? I tried backup/restore but it wants the
database name to stay the same.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Backup and restore work just fine, I do this all the time. Just specify the desired database name in
the RESTORE command and use the MOVE option to specify the names of the database files to be created
for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:BF2CE48C-EB00-4BCC-B9E5-71D9491445C3@.microsoft.com...
> Hi;
> I want to copy a database on my server, creating another database (different
> name) on the same server copying everything - tables, data, diagrams, stored
> procedures - everything. (I am making a test DB that I can use for my unit
> tests.)
> What's the easiest way to do this? I tried backup/restore but it wants the
> database name to stay the same.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
|||Try the following:
Assume you have to copy pubs to pubs_2
'create database pubs_2' for the first time.
then,
create a job with following steps:
backup database pubs to disk = 'c:\pubs.bak'
go
restore database pubs_2 from disk ='c:\pubs.bak' with replace,
move 'pubs' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\pubs_2.mdf',
move 'pubs_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\pubs_2_log.ldf'
Thanks
Veera
Tibor Karaszi wrote:
[vbcol=seagreen]
> Backup and restore work just fine, I do this all the time. Just specify the desired database name in
> the RESTORE command and use the MOVE option to specify the names of the database files to be created
> for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:BF2CE48C-EB00-4BCC-B9E5-71D9491445C3@.microsoft.com...
|||Hi;
Tried that (thank you) but got this error message:
Processed 120 pages for database 'WindwardPortal', file
'WindwardPortal_Data' on file 5.
Processed 1 pages for database 'WindwardPortal', file 'WindwardPortal_Log'
on file 5.
BACKUP DATABASE successfully processed 121 pages in 0.283 seconds (3.477
MB/sec).
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'WindwardPortal' is not part of database 'PortalTest'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"Veera" wrote:

> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
>
|||ps - script is:
backup database WindwardPortal to disk = 'c:\temp\WindwardPortal.bak'
go
restore database PortalTest from disk ='c:\temp\WindwardPortal.bak' with
replace,
move 'WindwardPortal' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest.mdf',
move 'WindwardPortal_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest_log.ldf'
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"Veera" wrote:

> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
>
|||> Processed 120 pages for database 'WindwardPortal', file
> 'WindwardPortal_Data' on file 5.
> Processed 1 pages for database 'WindwardPortal', file 'WindwardPortal_Log'
> on file 5.
> BACKUP DATABASE successfully processed 121 pages in 0.283 seconds (3.477
> MB/sec).
> Server: Msg 3234, Level 16, State 2, Line 1
> Logical file 'WindwardPortal' is not part of database 'PortalTest'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
The error message included a suggestion. DId you try it? It will show you
the logical names that you need to move. Apparently, you are not using the
correct logical file name for the data file. You tried to move
'WindwardPortal' when it should have been 'WindwardPortal_Data'
|||> 'create database pubs_2' for the first time.
Above is only a waste of time. And that creation will probably not result in the newly created
database having the same file layout as the one to restore, so the restore need to be performed
using REPLACE which means that SQL Server will delete the database and then create it when executing
the restore command. If the database doesn't exist, don't create it before the restore. the restore
process will do this for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Veera" <veerabahu.c@.gmail.com> wrote in message
news:1142873234.396195.127690@.e56g2000cwe.googlegr oups.com...
> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
|||Hi,
Scott pointed out correctly :
the command should be, (as your logical file name is different),
......
.......
move 'WindwardPortal_data' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest.mdf'
......
......
Thanks Tibor for your valuable information, Many thanks.

Copy database - same server

Hi;
I want to copy a database on my server, creating another database (different
name) on the same server copying everything - tables, data, diagrams, stored
procedures - everything. (I am making a test DB that I can use for my unit
tests.)
What's the easiest way to do this? I tried backup/restore but it wants the
database name to stay the same.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.comBackup and restore work just fine, I do this all the time. Just specify the
desired database name in
the RESTORE command and use the MOVE option to specify the names of the data
base files to be created
for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:BF2CE48C-EB00-4BCC-B9E5-71D9491445C3@.microsoft.com...
> Hi;
> I want to copy a database on my server, creating another database (differe
nt
> name) on the same server copying everything - tables, data, diagrams, stor
ed
> procedures - everything. (I am making a test DB that I can use for my unit
> tests.)
> What's the easiest way to do this? I tried backup/restore but it wants the
> database name to stay the same.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>|||Try the following:
Assume you have to copy pubs to pubs_2
'create database pubs_2' for the first time.
then,
create a job with following steps:
backup database pubs to disk = 'c:\pubs.bak'
go
restore database pubs_2 from disk ='c:\pubs.bak' with replace,
move 'pubs' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\pubs_2.mdf',
move 'pubs_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\pubs_2_log.ldf'
Thanks
Veera
Tibor Karaszi wrote:
[vbcol=seagreen]
> Backup and restore work just fine, I do this all the time. Just specify th
e desired database name in
> the RESTORE command and use the MOVE option to specify the names of the da
tabase files to be created
> for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:BF2CE48C-EB00-4BCC-B9E5-71D9491445C3@.microsoft.com...|||Hi;
Tried that (thank you) but got this error message:
Processed 120 pages for database 'WindwardPortal', file
'WindwardPortal_Data' on file 5.
Processed 1 pages for database 'WindwardPortal', file 'WindwardPortal_Log'
on file 5.
BACKUP DATABASE successfully processed 121 pages in 0.283 seconds (3.477
MB/sec).
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'WindwardPortal' is not part of database 'PortalTest'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"Veera" wrote:

> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
>|||ps - script is:
backup database WindwardPortal to disk = 'c:\temp\WindwardPortal.bak'
go
restore database PortalTest from disk ='c:\temp\WindwardPortal.bak' with
replace,
move 'WindwardPortal' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest.mdf',
move 'WindwardPortal_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest_log.ldf'
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"Veera" wrote:

> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
>|||> Processed 120 pages for database 'WindwardPortal', file
> 'WindwardPortal_Data' on file 5.
> Processed 1 pages for database 'WindwardPortal', file 'WindwardPortal_Log'
> on file 5.
> BACKUP DATABASE successfully processed 121 pages in 0.283 seconds (3.477
> MB/sec).
> Server: Msg 3234, Level 16, State 2, Line 1
> Logical file 'WindwardPortal' is not part of database 'PortalTest'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
The error message included a suggestion. DId you try it? It will show you
the logical names that you need to move. Apparently, you are not using the
correct logical file name for the data file. You tried to move
'WindwardPortal' when it should have been 'WindwardPortal_Data'|||> 'create database pubs_2' for the first time.
Above is only a waste of time. And that creation will probably not result in
the newly created
database having the same file layout as the one to restore, so the restore n
eed to be performed
using REPLACE which means that SQL Server will delete the database and then
create it when executing
the restore command. If the database doesn't exist, don't create it before t
he restore. the restore
process will do this for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Veera" <veerabahu.c@.gmail.com> wrote in message
news:1142873234.396195.127690@.e56g2000cwe.googlegroups.com...
> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
>|||Hi,
Scott pointed out correctly :
the command should be, (as your logical file name is different),
......
......
move 'WindwardPortal_data' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest.mdf'
.....
.....
--
Thanks Tibor for your valuable information, Many thanks.

Copy comma separated text file into a Sql server table

Hi
I have a comma separated text file which I would like to import into a table. There are some columns which are enclosed in double quotes and others which are not. I've tried bcp - it didn't work.
Can anyone help ? An example would be helpful.
Thanks
YogeshUse the DTS import wizard.
Source: Text File, enter your text file location
text qualifier: Double Quote {"}

copy a db, identity problem

hi
i need to copy a db to another db every single table to another similar to
the first one but with some difference. When i try to do that i need to
remove some constraint and all the identity on the new db. i'd like do that
with a script, i tried:
CREATE TABLE (
id int IDENTITY (1,1) NOT NULL
)
ALTER TABLE prova ALTER COLUMN id int null
GO
it doesnt work.
how can i remove identity from a column of my table'
tx
carloYou can't directly remove the IDENTITY property. You would have to
create a new column or new version of the table.
However, you can use the SET IDENTITY_INSERT option to allow you to
populate the column without removing the IDENTITY property. See Books
Online for details.
David Portas
SQL Server MVP
--|||>> how can i remove identity from a column of my table'
You cannot do this directly. The alternative is to create another table,
copy the data & rename it. The EM interface can get this done with a few
mouse clicks and it may perform reasonably for small-medium sized tables.
Anith|||You don't need to. And, actually, you shouldn't. Look up SET IDENTITY_INSERT
in Books Online. And then use it.
It helps you achieve exactly what you need (judging from your post).
ML

Converting negative value

Hi!

I have a large table width values like this:

000024634300
000-37500000
002783868891
000009603857
000-60000000
000001672396
000000195200
000010315112
000017000000

I need to convert the numbers into an integer-type field. Is this
possible with just native sql-commands? I don't want to make an
active-x script because it will be very slow on the large table.

EspenSELECT col,
CASE WHEN col LIKE '%-%' THEN -1 ELSE 1 END*
CAST(REPLACE(col,'-','') AS NUMERIC(12))
FROM SomeTable

The value 2,783,868,891 exceeds the maximum for an INTEGER column so I've
used NUMERIC here. You could also use BIGINT.

--
David Portas
SQL Server MVP
--