can I pull a sql 2000 datafile into sql express ?

Can I do this with a sql 2000 database
http://www.cryer.co.uk/brian/sqlserver/howto_movedatabasefile.htm

then attach sql express to this mdf ?

hi,

BitShift wrote:

Can I do this with a sql 2000 database
http://www.cryer.co.uk/brian/sqlserver/howto_movedatabasefile.htm

then attach sql express to this mdf ?

no, but you can do the contrary, attach the mdf (+ ldf) to SQLExpress

regards

|||So whats the best way to export tables and data from a sql 2K db and import /recreate it in sqlexpress ? I have a sql2k database on a server and id like to pull it down and work against it locally.|||

hi,

just perform a backup->copy->restore or a sp_detach_db->copy->CREATE DATABASE xx FOR ATTACH (sp_attach_db has been deprecated in SQL Server 2005, but still usable), http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp

then you have to modify the compatibility level of the attached database to 9, http://msdn2.microsoft.com/en-us/library/ms178653.aspx, modify the database owner to a valid user, http://msdn2.microsoft.com/en-us/library/ms178630.aspx and finally regenerate database user's tables statistics with full scan, http://msdn2.microsoft.com/en-us/library/ms187348.aspx, as SQL Server 2005 implements different algorithm on them..

regards

|||i did the first step.... i tried to attach a database... but then poof!!! i had a message in my console the msg 601 ''could not continue scan with nolock due to data movement''

i did install the recommended fix exe, the 2000 sP4.. but it still appears.

please help me out, i can't attach an existing sql2k database to my dev express.. wat am i gonna do? im desperate.
|||

hi,

actually I've only seen this exception related to DML operations and not with RESTORE tasks..

but let's wait for Mike to jump in...

regards

|||i still dont get it... how do i restore a database that is backed up from sql2k to sql express?

the sql express that i am talking about is the one that is included in the Microsoft Visual Web Developer 2005...

i don't see an option that i can restore a certain backup database. is my only way to restore is to access the tui console > sqlcmd -S .\sqlexpress?

how, how?!?! im so noob.

tnx|||hey! i got it... ^_^

i used

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO


from: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp

thanx PO!!

God bless