copying files over workgroup

I want to copy the database backup files to another server as a part of logshipping implementation. tried using xcopy it doesn't work.
it says "invalid drive specification"
also tried using a dll, the dll does the copying i just had to call the dll through an sp, while trying this the QA hanged, tried killing the process but the process didn't kill, i had to restart the service.

the network in not a domain its workgroup.
with sql server 2000 on win2k server.

any inputs are welcome.
thanks in advance
regards,
harshal.Did you try running the Copy or the XCOPY command from a DOS prompt on the Destination Server. I use to have the same kind of issue with the Copy /Y command. Sometimes it liked the /Y and Sometimes din't.|||how did you use xcopy
did you map a network drive to the remote shared folder or did you use the unc \\servername\sharename?
i would check that you can connect with the unc from the run box on your start menu.

open run
type \\servername\sharename where servername is the remote pc and sharename is the shared folder you are trying to connect to
if you cannot do this
you may want to open your cmd prompt and check that you can ping the remote host
if you can ping but cannot connect through the run cmd then you are having upper layer issues (osi)
check permissions and the rights on the share.

if the user accounts didnt match it would bring up a user\password dialog box.

good luck..|||yeah tried mapping the folder also tried using unc path. Didn't work
Also tried connecting thru cmd prompt and "run" prompt
it connects well and copies the files too.
even the dll which was created for the same thing worked fine if called from vb, but if the same dll when used
through sql QA hanged the process without any results.|||you may be subject to a permissions problem

i would check that the MSSQLServer and SQL Server Agent services are running under local user accounts and not a local system account
and i would make sure that the same account exists on the destination server. (they should share the same password)

then give read permission to the folder to the user account

this goes against everything that i hold dear, and I feel dirty all over telling you how to do it...|||Originally posted by Ruprect

i would check that the MSSQLServer and SQL Server Agent services are running under local user accounts and not a local system account
and i would make sure that the same account exists on the destination server. (they should share the same password)

then give read permission to the folder to the user account


Been there done that already.
it was the first thing to check the accounts under which the services are running.
that is the reason me posting this here.:(
thanks for the help.
any other issues u can suggest?
thanks in advance..
regards,
Harshal.

Copying files from a sharepoint location to local machine using SSIS

I have to copy files from a sharepoint or extranet location (basically https://.....) location to my local server using SSIS.

Any kind of early help would be really great.

Search for Sharepoint Web Services. This is the recommended way (at least, what's been recommended to me in the past) to extract data from Sharepoint. You can use a Web Service task to access the Sharepoint web services.

http://www.developer.com/tech/article.php/3104621

Copying files between servers

I'm trying to copy files between 2 servers on a local network from within a
SQL Job (and Query Analyzer) using xp_cmdshell.xcopy but get an access
denied message returned.

I'm able to successfully do the copy from within a command window so think
the problem has something to do with using the default SQL Server account
but as yet I don't know how to resolve.

Any help/suggestions would be much appreciated.Am guessng that you are running MS-SQL using the local SYSTEM account.
System does not have access to network devices.

Your two options are to create another account and configure MS-SQL and
agent to use that account. You may beable to get away with just
configuring agent for that but depends on how you are doing the
command.

Or to go into policy editor and allowing the system account to have
netowrk priviledges. This is a major security hole and should not be
done.

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 .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, Delete Files, and Transfer via DTS

Hi,
I need to transfer files from another server to my database server (SQL
Server 7).
The job details is:
1. copy the files to backup folder ( d:\ftp\backup_data\ ), the backup
filename added with currentdate and time. I use xp_cmdshell.
2. load the file content to database. I use DTS from text file.
3. delete the source file. (xp_cmdshell).
The problem is xp_cmdshell is denied for accessing a mapping path, so copy
and deletion process will not run. I've try both sql server administrator
level or NT 4 administrator level login, but it still did'nt work.
Is there's anyway to do this?
Thanks in advance
TeguhThe access rights you will need to move, copy are not
yours, instead its the service account of you sql server.
Give the directory that rather than your own user id, and
that should do it.
Peter
"Do not awake the sleeping dragon for you are crunchy and
taste good with ketchup".
>--Original Message--
>Hi,
>I need to transfer files from another server to my
database server (SQL
>Server 7).
>The job details is:
>1. copy the files to backup folder (
d:\ftp\backup_data\ ), the backup
>filename added with currentdate and time. I use
xp_cmdshell.
>2. load the file content to database. I use DTS from text
file.
>3. delete the source file. (xp_cmdshell).
>The problem is xp_cmdshell is denied for accessing a
mapping path, so copy
>and deletion process will not run. I've try both sql
server administrator
>level or NT 4 administrator level login, but it still
did'nt work.
>Is there's anyway to do this?
>Thanks in advance
>Teguh
>
>.
>

Copy tables to a new database

Hi All,
If I put tables in 'Model' database and creating new database will automatic
ally copy the system files along with the additional files that I created.
I want to do the same thing, but getting all the tables from a database call
ed 'Template'. How could I
achieve this by writing a stored procedure? Any help or suggestions is appr
eciated.Steve,
Create a script of everything in Template (you could use RedGate, Enterprise
Manager,etc.) then run that script in the newly created database.
Alternatively, you create your Template database and put whatever you want
into it. Then back it up. Create your new database and use a RESTORE ...
REPLACE of your Template backup to load your template into the new database.
Russell Fields
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:9771EEDD-FF40-4509-9EDE-7A0E04233A61@.microsoft.com...

quote:

---

> Hi All,
> If I put tables in 'Model' database and creating new database will

---

automatically copy the system files along with the additional files that I
created. I want to do the same thing, but getting all the tables from a
database called 'Template'. How could I achieve this by writing a stored
procedure? Any help or suggestions is appreciated.

Copy tables to a new database

Hi All,
If I put tables in 'Model' database and creating new database will automatically copy the system files along with the additional files that I created. I want to do the same thing, but getting all the tables from a database called 'Template'. How could I achieve this by writing a stored procedure? Any help or suggestions is appreciated.Steve,
Create a script of everything in Template (you could use RedGate, Enterprise
Manager,etc.) then run that script in the newly created database.
Alternatively, you create your Template database and put whatever you want
into it. Then back it up. Create your new database and use a RESTORE ...
REPLACE of your Template backup to load your template into the new database.
Russell Fields
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:9771EEDD-FF40-4509-9EDE-7A0E04233A61@.microsoft.com...
> Hi All,
> If I put tables in 'Model' database and creating new database will
automatically copy the system files along with the additional files that I
created. I want to do the same thing, but getting all the tables from a
database called 'Template'. How could I achieve this by writing a stored
procedure? Any help or suggestions is appreciated.

Copy table between SQL servers without logging

Does anyone have a non-DTS method for copying large tables between SQL
servers without logging? bcp and BULK INSERT appear to be for files only.
Is there a way to use them with other SQL tables?Mark
DECLARE @.tablename AS SYSNAME
SET @.tablename = 'pubs..titles'
EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM ' +
@.tablename + ''') AS T')
Note: I assume I have created linked server , may I ask you why not using
DTS?
Also ,this appoach will fail if you have already test table ,what's more it
doesnt transfer pk to the new table.
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vtecr7qe5d1o7c@.corp.supernews.com...
> Does anyone have a non-DTS method for copying large tables between SQL
> servers without logging? bcp and BULK INSERT appear to be for files only.
> Is there a way to use them with other SQL tables?
>|||If the destination table doesn't have to already exist, you can use SELECT *
INTO new_table_name to minimize, but not entirely eliminate, logging.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vtecr7qe5d1o7c@.corp.supernews.com...
> Does anyone have a non-DTS method for copying large tables between SQL
> servers without logging? bcp and BULK INSERT appear to be for files only.
> Is there a way to use them with other SQL tables?
>|||Hi,
Make the recovery model for the database as "Simple". After that create a
linked server to connect to remote server.
1. Create the table strucute
2. Use insert into select * from server.db.dbo.tablename
Thanks
Hari
MCDBA
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vtecr7qe5d1o7c@.corp.supernews.com...
> Does anyone have a non-DTS method for copying large tables between SQL
> servers without logging? bcp and BULK INSERT appear to be for files only.
> Is there a way to use them with other SQL tables?
>|||Hi Aaron,
I feel we cannot use the command "select * into server.dbname.dbo.temptable
from table " between servers.
Thanks
Hari
MCDBA
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uW3NpDzvDHA.3140@.TK2MSFTNGP11.phx.gbl...
> If the destination table doesn't have to already exist, you can use SELECT
*
> INTO new_table_name to minimize, but not entirely eliminate, logging.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> news:vtecr7qe5d1o7c@.corp.supernews.com...
> > Does anyone have a non-DTS method for copying large tables between SQL
> > servers without logging? bcp and BULK INSERT appear to be for files
only.
> > Is there a way to use them with other SQL tables?
> >
> >
>|||Uri,
Thanks for your reply. I am not using DTS because the tables being copied
may change, I am using a cursor to loop through the tables to copy, then
executing code within the cursor to copy the data. I am running two
parallel cursors to maximize transfer speed. My issue is that the log file
is growing to 4+Gig during the copy operation on a 56Gig database. We can
not just backup and restore or copy the physical files in our case. As far
as keys, indexes, etc. they are all scripted and added at the appropriate
times. Database recovery model is Simple.
Previously, I have tried breaking up the copy into chunks and performing a
BACKUP LOG WITH TRUNCATE ONLY between chunks, but with all the overhead,
that was incredibly slower. However, it did use minimal log space.
Obviously, disk space concerns are driving this effort. It's unnecessary,
in our situation, to log the data, why do it? I feel like I am forced to do
so.
Mark
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#U0usDzvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> Mark
> DECLARE @.tablename AS SYSNAME
> SET @.tablename = 'pubs..titles'
> EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM ' +
> @.tablename + ''') AS T')
> Note: I assume I have created linked server , may I ask you why not using
> DTS?
> Also ,this appoach will fail if you have already test table ,what's more
it
> doesnt transfer pk to the new table.
>
>
> "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> news:vtecr7qe5d1o7c@.corp.supernews.com...
> > Does anyone have a non-DTS method for copying large tables between SQL
> > servers without logging? bcp and BULK INSERT appear to be for files
only.
> > Is there a way to use them with other SQL tables?
> >
> >
>|||Mark
Well ,perhaps you need to use Replications , did you think about it?
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vteg11mhsao054@.corp.supernews.com...
> Uri,
> Thanks for your reply. I am not using DTS because the tables being copied
> may change, I am using a cursor to loop through the tables to copy, then
> executing code within the cursor to copy the data. I am running two
> parallel cursors to maximize transfer speed. My issue is that the log
file
> is growing to 4+Gig during the copy operation on a 56Gig database. We can
> not just backup and restore or copy the physical files in our case. As
far
> as keys, indexes, etc. they are all scripted and added at the appropriate
> times. Database recovery model is Simple.
> Previously, I have tried breaking up the copy into chunks and performing a
> BACKUP LOG WITH TRUNCATE ONLY between chunks, but with all the overhead,
> that was incredibly slower. However, it did use minimal log space.
> Obviously, disk space concerns are driving this effort. It's unnecessary,
> in our situation, to log the data, why do it? I feel like I am forced to
do
> so.
> Mark
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#U0usDzvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > Mark
> > DECLARE @.tablename AS SYSNAME
> > SET @.tablename = 'pubs..titles'
> >
> > EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM '
+
> > @.tablename + ''') AS T')
> >
> > Note: I assume I have created linked server , may I ask you why not
using
> > DTS?
> > Also ,this appoach will fail if you have already test table ,what's more
> it
> > doesnt transfer pk to the new table.
> >
> >
> >
> >
> > "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> > news:vtecr7qe5d1o7c@.corp.supernews.com...
> > > Does anyone have a non-DTS method for copying large tables between SQL
> > > servers without logging? bcp and BULK INSERT appear to be for files
> only.
> > > Is there a way to use them with other SQL tables?
> > >
> > >
> >
> >
>|||Yes... and then again, a lot of overhead and additional database limitations
to perform a simple copy. I wish I just had the cash for larger drives.
Thanks for your comments.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#RjlOczvDHA.2712@.TK2MSFTNGP11.phx.gbl...
> Mark
> Well ,perhaps you need to use Replications , did you think about it?
>
> "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> news:vteg11mhsao054@.corp.supernews.com...
> > Uri,
> >
> > Thanks for your reply. I am not using DTS because the tables being
copied
> > may change, I am using a cursor to loop through the tables to copy, then
> > executing code within the cursor to copy the data. I am running two
> > parallel cursors to maximize transfer speed. My issue is that the log
> file
> > is growing to 4+Gig during the copy operation on a 56Gig database. We
can
> > not just backup and restore or copy the physical files in our case. As
> far
> > as keys, indexes, etc. they are all scripted and added at the
appropriate
> > times. Database recovery model is Simple.
> >
> > Previously, I have tried breaking up the copy into chunks and performing
a
> > BACKUP LOG WITH TRUNCATE ONLY between chunks, but with all the overhead,
> > that was incredibly slower. However, it did use minimal log space.
> >
> > Obviously, disk space concerns are driving this effort. It's
unnecessary,
> > in our situation, to log the data, why do it? I feel like I am forced
to
> do
> > so.
> >
> > Mark
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:#U0usDzvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > > Mark
> > > DECLARE @.tablename AS SYSNAME
> > > SET @.tablename = 'pubs..titles'
> > >
> > > EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM
'
> +
> > > @.tablename + ''') AS T')
> > >
> > > Note: I assume I have created linked server , may I ask you why not
> using
> > > DTS?
> > > Also ,this appoach will fail if you have already test table ,what's
more
> > it
> > > doesnt transfer pk to the new table.
> > >
> > >
> > >
> > >
> > > "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> > > news:vtecr7qe5d1o7c@.corp.supernews.com...
> > > > Does anyone have a non-DTS method for copying large tables between
SQL
> > > > servers without logging? bcp and BULK INSERT appear to be for files
> > only.
> > > > Is there a way to use them with other SQL tables?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Select * into [New Table name]
With the new name being fully qualified
Will
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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 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