SQL Server 2000General 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 .
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.
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.
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.
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.
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 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
declare @list varchar(100)
select @list=(select parlst from rr_aw_det where aid=1)
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
hope i am able to put my problem clearly. please reply, this is taking a lot of time.
thanks and regards
-- 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.
WHERE vchLATCIDict Like vchLATCSDict
-- This works
WHERE vchLATCIDict COLLATE Latin1_General_CS_AS Like vchLATCSDict