Porting MSDE database to SQL server

hgberman · January 23rd, 2005, 06:06 PM

In [u]Beginning ASP.Net Databases Using VB.Net </u>(p. 26), the authors say that an MSDE database "can be ported to the commercial distribution SQL Server with next to no effort." Can anyone tell me how to do that?

I asked the Support folks at my web host, but they could not help. (They said to use Enterprise Manager to make a backup database, but MSDE does not have Enterprise Manager.)

I am a beginner trying to teach myself to work with databases (so I am learning from an idiot). If anyone can help me out, I would be very grateful.

Thanks.

jemacc · January 23rd, 2005, 07:44 PM

using SQL 7, 200 (Query Analyzer)

Code:

sp_detach_db [ @dbname = ] 'dbname' 

to attach use the stored proc 
sp_attach_db [ @dbname = ] 'dbname' 
    , [ @filename1 = ] 'filename_n' 

eg
EXEC sp_attach_db @dbname = N'pubs', 
   @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', 
   @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

using VB

Code:

Private Sub Attach_Database()

con.Execute "EXEC sp_attach_db 'pubs',  @filename1 = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',    @filename2 = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'"
End Sub

Private Sub Detach_Database()
    con.Execute "sp_detach_db 'pubs' "
End Sub

you should modify the functions and replace the names with the actual (file)names

Jaime E. Maccou

hgberman · January 23rd, 2005, 08:03 PM

Hello Jaime E. Maccou,

Thank you very much for your help!! I was hopelessly lost, and your quick, clear advice is sure to get me back on track.

Many thanks.

Harvey Berman