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

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

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

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

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

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.

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

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

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

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