Copying SQL, mdf file / Please help

I have a SQL db on my server in internet
the server and my DB is online
I want to copy mdf file to my computer via FTP but I get an error
that file is in use and I can`t copy it

I wanted to know is there any way that i copy information in that MDF fle
to another DB on my computer

Its very important please help me

thanks allI don`t have permission to remote access or change any thing in Enterprise Manager
:(|||I'd do a SQL Server Backup (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp), and then FTP the resulting backup (aka DUMP) file. You can use that to rebuild a copy of the database on another machine.

-PatP|||You can do sp_detach+ftp_sp_attach to do the same, except a lot faster.|||You can do sp_detach+ftp_sp_attach to do the same, except a lot faster.

Hi buddy
thanks but can you tell me how can i use that code
and where I can use it

thanks alot|||I just have permission to use FTP nowsql

Copying Database in same SqlServer with different name

hii
follow this

1)stop sql server
2) take a copy of the two MDF and LDF files and NDF files if any.
3) Change the Name of the files
eg db2_data.mdf and db2_log.mdf
4)copy these in the data folderof MSSQL
5) start the SQL Server
6) from your query analyser
run sp_attach_db (please find the parameters in the Books online) procedure (if u have sql2000 u can do this from the enterprise manager)
7) in query anlayser go to db2 database , query the sysusers and drop all the users using sp_dropuser
8) recreate the users using Enterprise manage or other wise

other way
is detaching and attaching in a new name.Why are you even considering that *unsafe* route. A simple "backup database <dbname> to disk='c:\tmp.bak'" will do the backup. Then a simple "restore database <newdbname> from disk='c:\tmp.bak' with move 'newlogicalname' to 'c:\xyz.mdf',
move 'newlogicallogname' to 'c:\xyz.ldf'" will restore it.|||Perhaps you should get the Syntax correct:

restore database MyNewDB from disk='d:\MyDb.bak' with recovery, move 'MyDb_Data' to 'c:\MyNewDB.mdf', move 'MyDb_Log' to 'c:\MyNewDB.ldf'

Note: Use the following to discover files in backup.

RESTORE FILELISTONLY from disk='d:\MyDb.bak'

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]

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]

Copying a database

I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
the same server but with a different name. Of course, I
can't use the Copy Database Wizard. Will the following
work?
1. Detach the database.
2. Go into Win Explorer and make copies of the files, say
d:\aa1.mdf and d:\aa1.ldf.
3. Re-attach the original database using d:\aa.mdf and
d:\aa.ldf.
4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
with a different database name.
I'm just wondering if the server or system tables will get
confused for some obscure reason. Thanks for the help.
"GoX" <anonymous@.discussions.microsoft.com> wrote in message
news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
> the same server but with a different name. Of course, I
> can't use the Copy Database Wizard. Will the following
> work?
> 1. Detach the database.
> 2. Go into Win Explorer and make copies of the files, say
> d:\aa1.mdf and d:\aa1.ldf.
> 3. Re-attach the original database using d:\aa.mdf and
> d:\aa.ldf.
> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
> with a different database name.
> I'm just wondering if the server or system tables will get
> confused for some obscure reason. Thanks for the help.
As long as you use a different database name for step 4, that will work
fine.
Steve
|||Should work. If the db isn't too big, I suggest doing a backup and restore. The GUI work fine for just this,
as when you do restore, you type in the desired database name, and the GUI will change physical filenames
automatically.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"GoX" <anonymous@.discussions.microsoft.com> wrote in message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
> the same server but with a different name. Of course, I
> can't use the Copy Database Wizard. Will the following
> work?
> 1. Detach the database.
> 2. Go into Win Explorer and make copies of the files, say
> d:\aa1.mdf and d:\aa1.ldf.
> 3. Re-attach the original database using d:\aa.mdf and
> d:\aa.ldf.
> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
> with a different database name.
> I'm just wondering if the server or system tables will get
> confused for some obscure reason. Thanks for the help.
|||Should have thought of that. And then I don't have to
take the database off line!

>--Original Message--
>Should work. If the db isn't too big, I suggest doing a
backup and restore. The GUI work fine for just this,
>as when you do restore, you type in the desired database
name, and the GUI will change physical filenames
>automatically.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"GoX" <anonymous@.discussions.microsoft.com> wrote in
message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...[vbcol=seagreen]
to[vbcol=seagreen]
say[vbcol=seagreen]
get
>
>.
>
|||Correct. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:557b01c48a1b$3f0615d0$a301280a@.phx.gbl...[vbcol=seagreen]
> Should have thought of that. And then I don't have to
> take the database off line!
> backup and restore. The GUI work fine for just this,
> name, and the GUI will change physical filenames
> message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> to
> say
> get

Copying a database

I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
the same server but with a different name. Of course, I
can't use the Copy Database Wizard. Will the following
work?
1. Detach the database.
2. Go into Win Explorer and make copies of the files, say
d:\aa1.mdf and d:\aa1.ldf.
3. Re-attach the original database using d:\aa.mdf and
d:\aa.ldf.
4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
with a different database name.
I'm just wondering if the server or system tables will get
confused for some obscure reason. Thanks for the help."GoX" <anonymous@.discussions.microsoft.com> wrote in message
news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
> the same server but with a different name. Of course, I
> can't use the Copy Database Wizard. Will the following
> work?
> 1. Detach the database.
> 2. Go into Win Explorer and make copies of the files, say
> d:\aa1.mdf and d:\aa1.ldf.
> 3. Re-attach the original database using d:\aa.mdf and
> d:\aa.ldf.
> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
> with a different database name.
> I'm just wondering if the server or system tables will get
> confused for some obscure reason. Thanks for the help.
As long as you use a different database name for step 4, that will work
fine.
Steve|||Should work. If the db isn't too big, I suggest doing a backup and restore.
The GUI work fine for just this,
as when you do restore, you type in the desired database name, and the GUI w
ill change physical filenames
automatically.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"GoX" <anonymous@.discussions.microsoft.com> wrote in message news:c76f01c48a09$d840a330$a401
280a@.phx.gbl...
> I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
> the same server but with a different name. Of course, I
> can't use the Copy Database Wizard. Will the following
> work?
> 1. Detach the database.
> 2. Go into Win Explorer and make copies of the files, say
> d:\aa1.mdf and d:\aa1.ldf.
> 3. Re-attach the original database using d:\aa.mdf and
> d:\aa.ldf.
> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
> with a different database name.
> I'm just wondering if the server or system tables will get
> confused for some obscure reason. Thanks for the help.|||Should have thought of that. And then I don't have to
take the database off line!

>--Original Message--
>Should work. If the db isn't too big, I suggest doing a
backup and restore. The GUI work fine for just this,
>as when you do restore, you type in the desired database
name, and the GUI will change physical filenames
>automatically.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"GoX" <anonymous@.discussions.microsoft.com> wrote in
message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
to[vbcol=seagreen]
say[vbcol=seagreen]
get[vbcol=seagreen]
>
>.
>|||Correct. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:557b01c48a1b$3f0615d0$a301280a@.p
hx.gbl...[vbcol=seagreen]
> Should have thought of that. And then I don't have to
> take the database off line!
>
> backup and restore. The GUI work fine for just this,
> name, and the GUI will change physical filenames
> message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> to
> say
> get

Copying a database

I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
the same server but with a different name. Of course, I
can't use the Copy Database Wizard. Will the following
work?
1. Detach the database.
2. Go into Win Explorer and make copies of the files, say
d:\aa1.mdf and d:\aa1.ldf.
3. Re-attach the original database using d:\aa.mdf and
d:\aa.ldf.
4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
with a different database name.
I'm just wondering if the server or system tables will get
confused for some obscure reason. Thanks for the help."GoX" <anonymous@.discussions.microsoft.com> wrote in message
news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
> the same server but with a different name. Of course, I
> can't use the Copy Database Wizard. Will the following
> work?
> 1. Detach the database.
> 2. Go into Win Explorer and make copies of the files, say
> d:\aa1.mdf and d:\aa1.ldf.
> 3. Re-attach the original database using d:\aa.mdf and
> d:\aa.ldf.
> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
> with a different database name.
> I'm just wondering if the server or system tables will get
> confused for some obscure reason. Thanks for the help.
As long as you use a different database name for step 4, that will work
fine.
Steve|||Should work. If the db isn't too big, I suggest doing a backup and restore. The GUI work fine for just this,
as when you do restore, you type in the desired database name, and the GUI will change physical filenames
automatically.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"GoX" <anonymous@.discussions.microsoft.com> wrote in message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
> the same server but with a different name. Of course, I
> can't use the Copy Database Wizard. Will the following
> work?
> 1. Detach the database.
> 2. Go into Win Explorer and make copies of the files, say
> d:\aa1.mdf and d:\aa1.ldf.
> 3. Re-attach the original database using d:\aa.mdf and
> d:\aa.ldf.
> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
> with a different database name.
> I'm just wondering if the server or system tables will get
> confused for some obscure reason. Thanks for the help.|||Sounds like you have a plan. This should work just fine.
DeeJay
>--Original Message--
>I want to copy a database, say d:\aa.mdf and d:\aa.ldf,
to
>the same server but with a different name. Of course, I
>can't use the Copy Database Wizard. Will the following
>work?
>1. Detach the database.
>2. Go into Win Explorer and make copies of the files,
say
>d:\aa1.mdf and d:\aa1.ldf.
>3. Re-attach the original database using d:\aa.mdf and
>d:\aa.ldf.
>4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
>with a different database name.
>I'm just wondering if the server or system tables will
get
>confused for some obscure reason. Thanks for the help.
>.
>|||Should have thought of that. And then I don't have to
take the database off line!
>--Original Message--
>Should work. If the db isn't too big, I suggest doing a
backup and restore. The GUI work fine for just this,
>as when you do restore, you type in the desired database
name, and the GUI will change physical filenames
>automatically.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"GoX" <anonymous@.discussions.microsoft.com> wrote in
message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
>> I want to copy a database, say d:\aa.mdf and d:\aa.ldf,
to
>> the same server but with a different name. Of course, I
>> can't use the Copy Database Wizard. Will the following
>> work?
>> 1. Detach the database.
>> 2. Go into Win Explorer and make copies of the files,
say
>> d:\aa1.mdf and d:\aa1.ldf.
>> 3. Re-attach the original database using d:\aa.mdf and
>> d:\aa.ldf.
>> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
>> with a different database name.
>> I'm just wondering if the server or system tables will
get
>> confused for some obscure reason. Thanks for the help.
>
>.
>|||Correct. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:557b01c48a1b$3f0615d0$a301280a@.phx.gbl...
> Should have thought of that. And then I don't have to
> take the database off line!
> >--Original Message--
> >Should work. If the db isn't too big, I suggest doing a
> backup and restore. The GUI work fine for just this,
> >as when you do restore, you type in the desired database
> name, and the GUI will change physical filenames
> >automatically.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"GoX" <anonymous@.discussions.microsoft.com> wrote in
> message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> >> I want to copy a database, say d:\aa.mdf and d:\aa.ldf,
> to
> >> the same server but with a different name. Of course, I
> >> can't use the Copy Database Wizard. Will the following
> >> work?
> >>
> >> 1. Detach the database.
> >> 2. Go into Win Explorer and make copies of the files,
> say
> >> d:\aa1.mdf and d:\aa1.ldf.
> >> 3. Re-attach the original database using d:\aa.mdf and
> >> d:\aa.ldf.
> >> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
> >> with a different database name.
> >>
> >> I'm just wondering if the server or system tables will
> get
> >> confused for some obscure reason. Thanks for the help.
> >
> >
> >.
> >

Copying .mdf and .ldf files bewteen servers

We have a user at work at enjoys stopping SQL Server 7 and 2000 machines and
copying the .mdf and .ldf files from one server to another. He then simply
restarts the production server and re-attaches the database to the new
server.
I have asked this employee to either backp the DB to disk and copy this to
another server or use the sp_detach command and copy file then re-attach.
What are the ramifications of doing a simple .mdf and .ldf copy?
We have a number of MSDE databases as well. Is this acceptable for these?
Perhaps the reason I am getting the SQL-DMO the name 'dbo' was not found in
the users collection is because of these types of copies.
Thanks
--
...david
http://www.micro-mess.com
http://www.va-mustang.com
If you wish to reply to me personally, please remove
the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!I agree with you that backup/restore is probably a better approach. This
eliminates the need to stop the SQL Server service on the source server.
Although sp_attach_db often works without sp_detach_db, the documentation
clearly states that it should only be used with database files detached with
sp_detach_db.
After attaching or restoring databases from another server, you can run
sp_changedbowner to correct the login mapping for the 'dbo' user. This will
correct the DMO 'dbo' user problem. You may also need to run
sp_change_users_login to correct the login/user mapping for other users.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DavidM" <scandal_123@.cox.net> wrote in message
news:%23I8gmHdxDHA.1364@.tk2msftngp13.phx.gbl...
> We have a user at work at enjoys stopping SQL Server 7 and 2000 machines
and
> copying the .mdf and .ldf files from one server to another. He then simply
> restarts the production server and re-attaches the database to the new
> server.
> I have asked this employee to either backp the DB to disk and copy this to
> another server or use the sp_detach command and copy file then re-attach.
> What are the ramifications of doing a simple .mdf and .ldf copy?
> We have a number of MSDE databases as well. Is this acceptable for these?
> Perhaps the reason I am getting the SQL-DMO the name 'dbo' was not found
in
> the users collection is because of these types of copies.
> Thanks
>
> --
> ...david
> http://www.micro-mess.com
> http://www.va-mustang.com
> If you wish to reply to me personally, please remove
> the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!
>

Copying .mdf and .ldf files bewteen servers

We have a user at work at enjoys stopping SQL Server 7 and 2000 machines and
copying the .mdf and .ldf files from one server to another. He then simply
restarts the production server and re-attaches the database to the new
server.
I have asked this employee to either backp the DB to disk and copy this to
another server or use the sp_detach command and copy file then re-attach.
What are the ramifications of doing a simple .mdf and .ldf copy?
We have a number of MSDE databases as well. Is this acceptable for these?
Perhaps the reason I am getting the SQL-DMO the name 'dbo' was not found in
the users collection is because of these types of copies.
Thanks
...david
http://www.micro-mess.com
http://www.va-mustang.com
If you wish to reply to me personally, please remove
the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!I agree with you that backup/restore is probably a better approach. This
eliminates the need to stop the SQL Server service on the source server.
Although sp_attach_db often works without sp_detach_db, the documentation
clearly states that it should only be used with database files detached with
sp_detach_db.
After attaching or restoring databases from another server, you can run
sp_changedbowner to correct the login mapping for the 'dbo' user. This will
correct the DMO 'dbo' user problem. You may also need to run
sp_change_users_login to correct the login/user mapping for other users.
Hope this helps.
Dan Guzman
SQL Server MVP
"DavidM" <scandal_123@.cox.net> wrote in message
news:%23I8gmHdxDHA.1364@.tk2msftngp13.phx.gbl...

quote:

---

> We have a user at work at enjoys stopping SQL Server 7 and 2000 machines

---

and

quote:

---

> copying the .mdf and .ldf files from one server to another. He then simply
> restarts the production server and re-attaches the database to the new
> server.
> I have asked this employee to either backp the DB to disk and copy this to
> another server or use the sp_detach command and copy file then re-attach.
> What are the ramifications of doing a simple .mdf and .ldf copy?
> We have a number of MSDE databases as well. Is this acceptable for these?
> Perhaps the reason I am getting the SQL-DMO the name 'dbo' was not found

---

in

quote:

---

> the users collection is because of these types of copies.
> Thanks
>
> --
> ...david
> http://www.micro-mess.com
> http://www.va-mustang.com
> If you wish to reply to me personally, please remove
> the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!
>

---

Copying .MDF & .LDF files for new server

Hello - I am trying to copy a database file from our existing server into an FTP site so I can download and then upload to a new server. When using the file explorer and copy command I recieve an error that the states

" Cannot copy ***data file: It is being used by another person or program, close any programs that might be using the file and try again"

I have stopped MS SQL, stopped IIS etc - ANY SUGGESTIONS?

VinceDetach the database before trying to copy it.|||

Quote:

Originally Posted by seniorinsight

Hello - I am trying to copy a database file from our existing server into an FTP site so I can download and then upload to a new server. When using the file explorer and copy command I recieve an error that the states

" Cannot copy ***data file: It is being used by another person or program, close any programs that might be using the file and try again"

I have stopped MS SQL, stopped IIS etc - ANY SUGGESTIONS?

Vince

Use the Backup tool from Enterprise manager. Copy and load the backup on your new server. This is the proper way to transfer databases.

Copy SQL Server 2005 Express database to SQL Server 2000

Angus (nospam@.gmail.com) writes:
> I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
> files. If I copy to my machine and in the Enterprise manager try to
> attach the files I get error 602 - and apparently you cannot do this as
> the database structure has changed so much.
> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my
> end that would be preferable. Can I somehow convert the 2005 database
> to run on my SQL Server 2000? What would I need to install on my
> machine to achieve this? The SQL Server 2005 Express does not seem to
> provide much in the way of data export etc.
There is a fair chance that the database uses features that are not
available in SQL 2000, so a transfer to SQL 2000 is non-trivial, at least
a priori.
If you think that SQL Express does not cut it for you, I would suggest
that you cough up the 50 USD or similar for a license of Developer Edition
of SQL 2005.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
The solution is not to "cough up" money for SQL 2005. What if someone else
reads this and needs to deploy to 2000 on a customer's server?
Do this:
In Management Studio, right-click on the Database, goto Tasks->Back Up
Back up to Disk,
Add path where file should go.
In Enterprise Manager, add a new database.
Highlight the database.
go to Tools -> Restore Database
Restore From device
Click Select Devices
Restore from disk, Add .bak file from Management Studio
Make sure paths at Options -> Restore As are correct
"Erland Sommarskog" wrote:

> Angus (nospam@.gmail.com) writes:
> There is a fair chance that the database uses features that are not
> available in SQL 2000, so a transfer to SQL 2000 is non-trivial, at least
> a priori.
> If you think that SQL Express does not cut it for you, I would suggest
> that you cough up the 50 USD or similar for a license of Developer Edition
> of SQL 2005.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Shiggity (Shiggity@.discussions.microsoft.com) writes:
> The solution is not to "cough up" money for SQL 2005. What if someone
> else reads this and needs to deploy to 2000 on a customer's server?
> Do this:
> In Management Studio, right-click on the Database, goto Tasks->Back Up
> Back up to Disk,
> Add path where file should go.
> In Enterprise Manager, add a new database.
> Highlight the database.
> go to Tools -> Restore Database
> Restore From device
> Click Select Devices
> Restore from disk, Add .bak file from Management Studio
> Make sure paths at Options -> Restore As are correct
Not sure what you mean to say here, but if the intention is to describe
how to copy a database from SQL 2005 to SQL 2000, I'm afraid that you
method will not work. If you need to do that, you will have to script
the database (there is a SQL 2000 compatibility switch in the scripting
wizard) and bulk-copy data over. If database uses features that are not
supported on SQL 2000, you will have deal with that.
In the original post, Angus wanted to be able to work with a copy of
a customer database in SQL 2000. To that aim, he needs SQL 2005. And
if Express does not have what he needs, Developer Edition is his best bet.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Copy SQL Server 2005 Express database to SQL Server 2000

Hello
I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
files. If I copy to my machine and in the Enterprise manager try to attach
the files I get error 602 - and apparently you cannot do this as the
database structure has changed so much.
Bearing in mind SQL Server 2005 is customersw so don't really want to
install too much software on their machine. so if I can do this all my end
that would be preferable. Can I somehow convert the 2005 database to run on
my SQL Server 2000? What would I need to install on my machine to achieve
this? The SQL Server 2005 Express does not seem to provide much in the way
of data export etc.
Angus> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my
> end
> that would be preferable. Can I somehow convert the 2005 database to run
> on
> my SQL Server 2000? What would I need to install on my machine to achieve
> this? The SQL Server 2005 Express does not seem to provide much in the
> way
> of data export etc.
Well, you could create an empty database, script all the objects, create
them empty, and the populate them using DTS / SSIS. You would need a
management tool on one side (Enterprise Manager, Management Studio, not sure
if Management Studio Express has any of this).
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Why don't you just install SQL Express on your machine?
The two versions live together very well.
On May 10, 12:43 pm, "Angus" <nos...@.gmail.com> wrote:
> Hello
> I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
> files. If I copy to my machine and in the Enterprise manager try to attac
h
> the files I get error 602 - and apparently you cannot do this as the
> database structure has changed so much.
> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my en
d
> that would be preferable. Can I somehow convert the 2005 database to run
on
> my SQL Server 2000? What would I need to install on my machine to achieve
> this? The SQL Server 2005 Express does not seem to provide much in the wa
y
> of data export etc.
> Angus

Copy SQL Server 2005 Express database to SQL Server 2000

Hello
I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
files. If I copy to my machine and in the Enterprise manager try to attach
the files I get error 602 - and apparently you cannot do this as the
database structure has changed so much.
Bearing in mind SQL Server 2005 is customersw so don't really want to
install too much software on their machine. so if I can do this all my end
that would be preferable. Can I somehow convert the 2005 database to run on
my SQL Server 2000? What would I need to install on my machine to achieve
this? The SQL Server 2005 Express does not seem to provide much in the way
of data export etc.
Angus> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my
> end
> that would be preferable. Can I somehow convert the 2005 database to run
> on
> my SQL Server 2000? What would I need to install on my machine to achieve
> this? The SQL Server 2005 Express does not seem to provide much in the
> way
> of data export etc.
Well, you could create an empty database, script all the objects, create
them empty, and the populate them using DTS / SSIS. You would need a
management tool on one side (Enterprise Manager, Management Studio, not sure
if Management Studio Express has any of this).
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Why don't you just install SQL Express on your machine?
The two versions live together very well.
On May 10, 12:43 pm, "Angus" <nos...@.gmail.com> wrote:
> Hello
> I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
> files. If I copy to my machine and in the Enterprise manager try to attach
> the files I get error 602 - and apparently you cannot do this as the
> database structure has changed so much.
> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my end
> that would be preferable. Can I somehow convert the 2005 database to run on
> my SQL Server 2000? What would I need to install on my machine to achieve
> this? The SQL Server 2005 Express does not seem to provide much in the way
> of data export etc.
> Angus