Moving SQL Server application onto MSDE server

I'm pretty new to SQL Server so any help on this would be
appreciated.
I have SQL Server 2000 database on my local machine and
have developed a web application (ASP) which accesses this
database.
I now have a remote hosted server (Windows 2000) running
MSDE.
I've moved the ASP pages onto the remote server but how do
I move the MS SQL database to the server so it uses the
MSDE engine? Can I just copy the MDF and LDF files to the
data directory and is everything else the same or are
there other modifications I need to perform?
Thanks for your help?
ChoccyIt is relatively easy to move the database... One way is to use the copy
database wizard in SQL Enterprise Manager...
Make sure no one is using the source database.. Right click databases. ->
all tasks -> copy database wizard... The Login you are using must have write
access to the directories where you wish the new database to go... follow
the wizard and you are goo.
Doing the same thing manually involves using sp_detach_db, copy the mdf, and
ldf files to the new location... Then use sp_attach_db twice, First to
attach the database to the new server, then to attache the database back to
the original server.
Lastly you may use backup.restore to do the job..
The only thing which might be a problem is that the users in your database
map to SQL logins in master. The logins available in the new server may not
match the old server... Read about "Moving a database to a different
server" by searching on MSDN web site, or look for "Log Shipping" in books
on line... Both describe how to re-match users with logins. (It will not be
a huge problem, but one you should address.)
Good luck, and have great success!
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Choccy" <choccy@.chocolateteapot.com> wrote in message
news:4e5201c37c3b$941ae0d0$a601280a@.phx.gbl...
> I'm pretty new to SQL Server so any help on this would be
> appreciated.
> I have SQL Server 2000 database on my local machine and
> have developed a web application (ASP) which accesses this
> database.
> I now have a remote hosted server (Windows 2000) running
> MSDE.
> I've moved the ASP pages onto the remote server but how do
> I move the MS SQL database to the server so it uses the
> MSDE engine? Can I just copy the MDF and LDF files to the
> data directory and is everything else the same or are
> there other modifications I need to perform?
> Thanks for your help?
> Choccy|||If your SQL2k DB is < 2GB, sp_detachdb would work.
--
HTH
Ryan Waight, MCDBA, MCSE
"Choccy" <choccy@.chocolateteapot.com> wrote in message
news:4e5201c37c3b$941ae0d0$a601280a@.phx.gbl...
> I'm pretty new to SQL Server so any help on this would be
> appreciated.
> I have SQL Server 2000 database on my local machine and
> have developed a web application (ASP) which accesses this
> database.
> I now have a remote hosted server (Windows 2000) running
> MSDE.
> I've moved the ASP pages onto the remote server but how do
> I move the MS SQL database to the server so it uses the
> MSDE engine? Can I just copy the MDF and LDF files to the
> data directory and is everything else the same or are
> there other modifications I need to perform?
> Thanks for your help?
> Choccy|||Wayne,
thanks for the speedy reply.
I only have access to the remote server across the web, it
is not on a network of mine. I guess that rules out using
the Enterprise Manager?
Therefore I will try your other suggestions and hopefully
I will get it to work OK.
Cheers
Choccy
>--Original Message--
>It is relatively easy to move the database... One way is
to use the copy
>database wizard in SQL Enterprise Manager...
>Make sure no one is using the source database.. Right
click databases. ->
>all tasks -> copy database wizard... The Login you are
using must have write
>access to the directories where you wish the new database
to go... follow
>the wizard and you are goo.
>Doing the same thing manually involves using
sp_detach_db, copy the mdf, and
>ldf files to the new location... Then use sp_attach_db
twice, First to
>attach the database to the new server, then to attache
the database back to
>the original server.
>Lastly you may use backup.restore to do the job..
>The only thing which might be a problem is that the users
in your database
>map to SQL logins in master. The logins available in the
new server may not
>match the old server... Read about "Moving a database to
a different
>server" by searching on MSDN web site, or look for "Log
Shipping" in books
>on line... Both describe how to re-match users with
logins. (It will not be
>a huge problem, but one you should address.)
>Good luck, and have great success!
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Choccy" <choccy@.chocolateteapot.com> wrote in message
>news:4e5201c37c3b$941ae0d0$a601280a@.phx.gbl...
>> I'm pretty new to SQL Server so any help on this would
be
>> appreciated.
>> I have SQL Server 2000 database on my local machine and
>> have developed a web application (ASP) which accesses
this
>> database.
>> I now have a remote hosted server (Windows 2000) running
>> MSDE.
>> I've moved the ASP pages onto the remote server but how
do
>> I move the MS SQL database to the server so it uses the
>> MSDE engine? Can I just copy the MDF and LDF files to
the
>> data directory and is everything else the same or are
>> there other modifications I need to perform?
>> Thanks for your help?
>> Choccy
>
>.
>|||This might be of interest as well:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
--
Andrew J. Kelly
SQL Server MVP
"Choccy" <choccy@.chocolateteapot.com> wrote in message
news:041701c37c3f$fd9a5640$a001280a@.phx.gbl...
> Wayne,
> thanks for the speedy reply.
> I only have access to the remote server across the web, it
> is not on a network of mine. I guess that rules out using
> the Enterprise Manager?
> Therefore I will try your other suggestions and hopefully
> I will get it to work OK.
> Cheers
> Choccy
>
> >--Original Message--
> >It is relatively easy to move the database... One way is
> to use the copy
> >database wizard in SQL Enterprise Manager...
> >Make sure no one is using the source database.. Right
> click databases. ->
> >all tasks -> copy database wizard... The Login you are
> using must have write
> >access to the directories where you wish the new database
> to go... follow
> >the wizard and you are goo.
> >Doing the same thing manually involves using
> sp_detach_db, copy the mdf, and
> >ldf files to the new location... Then use sp_attach_db
> twice, First to
> >attach the database to the new server, then to attache
> the database back to
> >the original server.
> >
> >Lastly you may use backup.restore to do the job..
> >
> >The only thing which might be a problem is that the users
> in your database
> >map to SQL logins in master. The logins available in the
> new server may not
> >match the old server... Read about "Moving a database to
> a different
> >server" by searching on MSDN web site, or look for "Log
> Shipping" in books
> >on line... Both describe how to re-match users with
> logins. (It will not be
> >a huge problem, but one you should address.)
> >
> >Good luck, and have great success!
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >
> >"Choccy" <choccy@.chocolateteapot.com> wrote in message
> >news:4e5201c37c3b$941ae0d0$a601280a@.phx.gbl...
> >> I'm pretty new to SQL Server so any help on this would
> be
> >> appreciated.
> >>
> >> I have SQL Server 2000 database on my local machine and
> >> have developed a web application (ASP) which accesses
> this
> >> database.
> >> I now have a remote hosted server (Windows 2000) running
> >> MSDE.
> >> I've moved the ASP pages onto the remote server but how
> do
> >> I move the MS SQL database to the server so it uses the
> >> MSDE engine? Can I just copy the MDF and LDF files to
> the
> >> data directory and is everything else the same or are
> >> there other modifications I need to perform?
> >>
> >> Thanks for your help?
> >>
> >> Choccy
> >
> >
> >.
> >