Copying database files

Hi all,
I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
that a developer must be able to upload/dowload via FTP the mdf and ldf
files.
This isn't a production database, so there is no active connection to it
except for the said developer.
The system doesn't even allow us to copy the files, saying that they are in
use.
I've fiddled with the "Auto Close" parameter for the db, but to no avail.
The only way I've found is to stop the SQL Server, but obviously, this isn't
a viable solution.
How can this be done?
TIA
Paul Dussault, MCP
Hi Paul,
The programmer mentioned below could detach the database remotely then copy
the files and last but not least; attach the database. You can do this
without stopping the service.
Yours sincerely,
Jo Segers.
"Paul Dussault" <paulduss@.hotmail.com> schreef in bericht
news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
> that a developer must be able to upload/dowload via FTP the mdf and ldf
> files.
> This isn't a production database, so there is no active connection to it
> except for the said developer.
> The system doesn't even allow us to copy the files, saying that they are
in
> use.
> I've fiddled with the "Auto Close" parameter for the db, but to no avail.
> The only way I've found is to stop the SQL Server, but obviously, this
isn't
> a viable solution.
> How can this be done?
> TIA
> Paul Dussault, MCP
>
|||1. detach the database - sp_detach_db
2. copy the files
3. re-attach the database - sp_attach_db
If you do this on a regular basis, you can create a script to detach your
database, copy the files to an alternate location, then re-attach the
database on a regular basis.
Regards
Shane Brodie
"Paul Dussault" <paulduss@.hotmail.com> wrote in message
news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
> that a developer must be able to upload/dowload via FTP the mdf and ldf
> files.
> This isn't a production database, so there is no active connection to it
> except for the said developer.
> The system doesn't even allow us to copy the files, saying that they are
in
> use.
> I've fiddled with the "Auto Close" parameter for the db, but to no avail.
> The only way I've found is to stop the SQL Server, but obviously, this
isn't
> a viable solution.
> How can this be done?
> TIA
> Paul Dussault, MCP
>
|||Thank you both for your quick replies.
I was aware of the sprocs, but the developper won't have any other access
than HTTP and FTP (ports 80 and 21).
Should I create an ASP script to execute these sprocs and then let the
developper execute to script as needed?
Thanks again!
Paul Dussault, MCP
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
> If you do this on a regular basis, you can create a script to detach your
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
> Regards
> Shane Brodie
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
> in
avail.
> isn't
>
|||A much better option is to use the BACKUP command to backup your =
database(s) to a file. You can use these files to restore on your =
machine. The benefit with this approach is that you do not have to take =
the database offline at any point.
More information about Backup and Restore can be found within Books =
Online
http://www.microsoft.com/sql/techinf...2000/books.asp
--=20
Keith
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message =
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
>=20
> If you do this on a regular basis, you can create a script to detach =
your[vbcol=seagreen]
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
>=20
> Regards
>=20
> Shane Brodie
>=20
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
ldf[vbcol=seagreen]
to it[vbcol=seagreen]
are[vbcol=seagreen]
> in
avail.[vbcol=seagreen]
this
> isn't
>=20
>
|||In 6 years of SQL server experience, I've never found a case where using
backup/restore was better than using detach/attach when wholesale
replacement of the database is needed. Additionally, in order to restore
from a backup, the restorer still needs to have access to the SQL server
enterprise manager and the database has to be void of any users or pending
transactions.
Paul's situation indicates only HTTP and FTP access is available. So, his
idea of exposing a script to attach and detach the relevant database files
should work OK. My question is, "What is the developer doing that requires
him/her to make regular wholesale replacements of the entire database?"
-Steve
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eqZb2V9IEHA.700@.TK2MSFTNGP09.phx.gbl...
A much better option is to use the BACKUP command to backup your database(s)
to a file. You can use these files to restore on your machine. The benefit
with this approach is that you do not have to take the database offline at
any point.
More information about Backup and Restore can be found within Books Online
http://www.microsoft.com/sql/techinf...2000/books.asp
Keith
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
> If you do this on a regular basis, you can create a script to detach your
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
> Regards
> Shane Brodie
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
> in
avail.
> isn't
>
|||sp_detach_db takes the database offline.=20
This is unacceptable in a production environment.
BACKUP does not bring the database offline.
The developer/DBA has many options when restoring the database (WITH =
MOVE, for example)
SQL Server Enterprise Manager is not required to issue the RESTORE =
command. Anything that can execute the appropriate Transact-SQL RESTORE =
command will do. This includes osql.exe (a command line utility), Query =
Analyzer, or even a query window within a web page.
I agree with your question "What is the developer doing that requires =
him/her to make regular wholesale replacements of the entire database?"
Perhaps they are not tracking their table, data, stored procedure =
changes and it is simply "easier" to replace the whole database when =
they want to move their code to production. Most of us would agree that =
this is not the best method of code promotion. It is better to apply =
table changes as necessary, insert/update/delete any data that needs to =
be modified, and create the stored procedures that have changed since =
the latest build and promote to production.
--=20
Keith
"Steve Lupton" <nospam@.nowhere.com> wrote in message =
news:XqZfc.18538$_I3.13377@.twister.socal.rr.com...
> In 6 years of SQL server experience, I've never found a case where =
using
> backup/restore was better than using detach/attach when wholesale
> replacement of the database is needed. Additionally, in order to =
restore
> from a backup, the restorer still needs to have access to the SQL =
server
> enterprise manager and the database has to be void of any users or =
pending
> transactions.
>=20
> Paul's situation indicates only HTTP and FTP access is available. So, =
his
> idea of exposing a script to attach and detach the relevant database =
files
> should work OK. My question is, "What is the developer doing that =
requires
> him/her to make regular wholesale replacements of the entire =
database?"
>=20
> -Steve
>=20
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eqZb2V9IEHA.700@.TK2MSFTNGP09.phx.gbl...
> A much better option is to use the BACKUP command to backup your =
database(s)
> to a file. You can use these files to restore on your machine. The =
benefit
> with this approach is that you do not have to take the database =
offline at
> any point.
>=20
> More information about Backup and Restore can be found within Books =
Online[vbcol=seagreen]
> http://www.microsoft.com/sql/techinf...2000/books.asp
>=20
> --=20
> Keith
>=20
>=20
> "Shane Brodie" <sbrodie@.decorkit.com> wrote in message
> news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...
your[vbcol=seagreen]
the[vbcol=seagreen]