Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 26th, 2005, 05:10 AM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to x_ray
Default Moving the database to another Server

hi all,
i want to move a database that i created on my local SQL Server 2000 set on a windows 2000, to another SQL Server set on windows XP. i am a beginner in SQL server so i would appreciate a step by step help.
10x

Reply With Quote
  #2 (permalink)  
Old November 26th, 2005, 04:29 PM
Wrox Author
Points: 180, Level: 3
Points: 180, Level: 3 Points: 180, Level: 3 Points: 180, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: Vancouver, Washington, USA.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The easiest wayt to move or copy a database is to detach the database and then copy the database and log files to the destination server, and then attach the database. In Enterprise Manager, right-click on the database and then choose All Tasks to detach. Right-click on the target server to attach.
Another method is to make a backup and then restore it. Just create an empty database on the target server with the same name. This is easiest when the source and destination database & log file paths are the same on both servers. If not, the paths can be modified on the Options tab in the Backup dialog.

Paul Turley, MCSD, MCDBA, MCT, MSF Practitioner
Reply With Quote
  #3 (permalink)  
Old November 28th, 2005, 12:32 PM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to x_ray
Default

thanks Paul,but i have a couple of questions to ask(excuse me i am just a beginner):
a-does the two methods that you specified work even if the server instances are on separate machines?
b-Can you give more details about the second one.
10x for the support

Reply With Quote
  #4 (permalink)  
Old November 29th, 2005, 02:30 AM
Wrox Author
Points: 180, Level: 3
Points: 180, Level: 3 Points: 180, Level: 3 Points: 180, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: Vancouver, Washington, USA.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Either method will allow you to transfer a database from one server instance to another.

To restore a database to another machine where the database doesn't exist:
Copy the backup file from the source machine to the target machine or make sure you can access it through a mapped network drive letter.
In Enterprise Manager, right-click the server and select All Tasks > Restore Database...
In the restore Database dialog, choose From Device, Click Select Devices (restore from Disk) and click Add.
Find the backup file and click OK.
On the Restore Database dialog, switch to the Options tab.
Verify that the database and log file paths are valid. If not, change them to a valid path.
Click OK.

If you need to restore over an existing database, first make sure there are no connections open to the database and follow the same steps by right-clicking the database. On the Option tab, choose Force restore over existing database.


Paul Turley, MCSD, MCDBA, MCT, MSF Practitioner
Reply With Quote
  #5 (permalink)  
Old November 29th, 2005, 02:42 AM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to x_ray
Default

10x a lot Paul, you surely solved my problem, i wish you good luck.:)

Reply With Quote
  #6 (permalink)  
Old January 6th, 2006, 03:24 PM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to x_ray
Default

Paul are you still there?
i have another question:
is it possible to move the database without the data in it, and where that data is saved(which file is it the log file?)
i welcome any reply.

Reply With Quote
  #7 (permalink)  
Old January 6th, 2006, 05:37 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , AZ, .
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

To move the database without the data -- you mean to just create the objects (tables, views, stored procedures, functions etc)?

If so then you can use SQL Enterprise Manager to do this. Right click on the Database and select All tasks, then select Generate scripts. Then select the objects you want to script. Save the file and then run that script file in Query Analyzer on the server where you want to create the database

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com
Reply With Quote
  #8 (permalink)  
Old January 13th, 2006, 03:08 PM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to x_ray
Default

thanks David once again,i forgot for a while about the Generate script, 10x again

Reply With Quote
  #9 (permalink)  
Old January 18th, 2006, 12:13 PM
Authorized User
 
Join Date: Jan 2006
Location: bangalore, karnataka, India.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

David i am facing a problem in similar situation. while trying to create the tables,stored procedures from script methods. i generated the scripts on source server but when i run these script, for a stored procedure it says
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for like operation.

the stored procedure is

create find1
declare @list varchar(100)
select @list='somename'
select @list=(select parlst from rr_aw_det where aid=1)
select pcod
from rr_par_des,fn_split(@list,',')
where pcod like value + '%'

the fn_split is the function to split the delimeted string. it returns
idx value as the column
for ex:--if string s=d,df,df,df,
then fn_split(s,',') will result into

idx value
0 d
1 df
2 df
3 df

hope i am able to put my problem clearly. please reply, this is taking a lot of time.
thanks and regards
rahul pokharna


regards
rahul pokharna
Reply With Quote
  #10 (permalink)  
Old January 21st, 2006, 10:37 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , AZ, .
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

Rahul,

I need to see your table definitions to be sure, but it sounds like your tables are defined with different collations. Or that is to say that your table and your function have different collations.

Here is an illustrative example of how to resolve collation conflicts:

USE Tempdb

SET NOCOUNT ON

CREATE TABLE dbo.DemoCollationSorting
(vchLATCIDict varchar(3) COLLATE Latin1_General_CI_AS
,vchLATCSDict varchar(3) COLLATE Latin1_General_CS_AS
,vchLATBIN varchar(3) COLLATE Latin1_General_BIN
)

INSERT dbo.DemoCollationSorting VALUES ('ZZZ','ZZZ','ZZZ')
INSERT dbo.DemoCollationSorting VALUES ('zzz','zzz','zzz')
INSERT dbo.DemoCollationSorting VALUES ('aaa','aaa','aaa')
INSERT dbo.DemoCollationSorting VALUES ('AAA','AAA','AAA')
INSERT dbo.DemoCollationSorting VALUES ('MMM','MMM','MMM')
INSERT dbo.DemoCollationSorting VALUES ('mmm','mmm','mmm')

-- Gives the
--Msg 468, Level 16, State 9, Line 2
--Cannot resolve the collation conflict between "Latin1_General_CS_AS" and "Latin1_General_CI_AS" in the like operation.
EXEC('
SELECT *
FROM dbo.DemoCollationSorting
WHERE vchLATCIDict Like vchLATCSDict
')

-- This works
SELECT *
FROM dbo.DemoCollationSorting
WHERE vchLATCIDict COLLATE Latin1_General_CS_AS Like vchLATCSDict


David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error after moving to new SQL server jackchua BOOK: ASP.NET Website Programming Problem-Design-Solution 0 July 5th, 2005 07:46 AM
Moving to SQL Server siptah Access VBA 4 January 27th, 2005 06:28 PM
Need help moving my database seanmayhew BOOK: ASP.NET Website Programming Problem-Design-Solution 1 December 30th, 2004 04:00 PM
Moving code to new server sporkman43 Crystal Reports 0 August 2nd, 2004 02:05 PM
Moving Access 2000 database server lmcharl Access 1 June 21st, 2003 09:58 PM



All times are GMT -4. The time now is 01:13 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.