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 sql table to computer hard drive

Hi,

I use sql server management express. I have created a table on my hosts remote database and i want to copy the table (or the data) in some format or other to my hard drive. does anyone have any good ideas how i may do this either through management express or other means.

thanks a lot

nick

http://www.cryer.co.uk/brian/sqlserver/howtoexportcsv.htm
http://www.codeproject.com/aspnet/ImportExportCSV.asp

|||

thanks mike, i shall give this a whirl

nick

Copying my SQL Database to another server

Hi guys,

I have created a new SQL database (named “DB”) on my personal pc at home.

I want to copy “DB” to my job’s computer, which works on a local net with SQL server.

What should I do?

Thanks a lot,

Aldo.

you need a connection between the machines first of. If you have VPN to your work's LAN you can use it or create a backup of the database or copy the database files in the sql server directories. If you take the offline/copy approach simply bring the file(s) via removable media into work and restore/attach the database file(s). If you can get a valid remote connection then you could use maintenance plans/SSIS to copy over the database between sql servers assuming you have valid login credentiuals to the remote "work" server.|||

Hi Derek, Which / where is the file I should copy in order to get an offline copy?

I've tried copying my ".mdf" file from this path: "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\DB.mdf" but getting something strange...

Thanks in advance,

Aldo.

|||sorry, you need to detach the database first and then reattach it.|||Where can I see how to "detach" and "reatach" a dababase?|||

Hi ajliks,

I would suggest , first you take a back up of your Database (Db) from your personal pc . Copy the back up file to a CD or some portable storage device. Copy it to your Job's computer. And the restore the database from there on to your SQL Server directly. This would be the easiest way to do it.

Thanks & Regards

Mathew

|||

Hi Mathew, thanks for anwering. I want to know how to make the SQL database copy. I've tried copying the mdf file but getting some strange stuff. (I want to copy the table and views configuration).

Thanks,

Aldo.

|||

1. Open your SQL Server Enterprise Manager

2. Expand and Point to your database.

3. Right Click on your database (Db), and navigate to "All Task " -- "Backup Database" -- "Add" -- "File Name"

4. Give a proper file name and note the location where the backup file is being created.

5. Copy This file to your Job Computer

6. For Restoring on your Job computer check the following steps

7. Open Enterprise Manager on your job computer.

8. Point to Database Node and Right Click on "Restore Database"

9 . Type your Database Name "Db" on the drop down box "Restore Database as"

10 Navigate through "From device" -- "Select devices" -- "Add" - "File Name"

11 Select the backup file and click ok.

You would get ur database created on your machine

Does this makes sense to you now

|||Thanks a lot man!

copying my database to another offsite computer

My need: I want to copy my databse (the file itself) in a mem card so that I can use it in another comuter offsite.
How do I do that. it s like a back up I think, except that I ll need to import the DB in another computer at home.
When I try using Backup the DB of Entreprise Manager, I get violation error, can t copy the file being used or something like that. Am I doing it the right way or is there another better way to do this.

Thanks

One way you can do this:

First you detach your database from EM , and find where your database's data file is, by default installation, the database file is in this folder: C:\Program Files\Microsoft SQL Server\MSSQL\Data.(attach the database after you copy it)

Then, copy the .mdf file of your database to your memory card.

From another computer, you can simply attach it through EM. Good to go.

|||thank u

Copying MSDE database to disk - how?

I have my little MSDE on my computer. I can add and delete stuff from it through Web Matrix. My question is, where exactly is the database stored in Windows?? Also, say I wanted to copy it to a disk so I could show it to someone else, what file(s) would I need to copy?

The reason why I ask is because I have been doing a website as a project and we have to hand in all files on a disk. Getting all the .aspx & .ascx and the .config file is easy, but the database & stored procedures is proving tricky to find.

ThanksTry using this tool - http://www.webattack.com/get/dbamgr2.html its a MSDE Manager. Install it then connect to your server. right click the database you want to backup then select 'Backup Database' and bobs your uncle:)|||Actually you shold use that tool, right click your DB then click 'Generate SQL Script'. This will be easier to install on a remote server

Copying a database - not on a domain

Hi;
I am trying to copy a database from one computer to another. Neither
computer is on a database.
Everything I have tried has failed. And everything I find under google says
that the databases have to be in the same domain.
How can I do this if neither computer is in a domain?
--
thanks - daveHi Dave,
Thanks for your post.
From your descriptions, I understood you would like to copy database via
two computer that are not in the same domain. If I have misunderstood your
concern, please feel free to point it out.
Here are two Knowledge Base articles describing how to move database
between computers. You could BACKUP the database and then use WITH MOVE
parameter to RESTORE it in the new computer.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/kb/314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/kb/221465
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi;
First off, no domains are involved - both Sql Server installations are
running on machines in a workgroup.
1) The backup/restore approach, if the drive letter changes (it does),
requires changing the system tables and has warnings that this can totally
hose up the Sql Server system.
2) Sp_detach/attach is for moving a DB and I want to copy it.
3) Export/Import seems to work ok - but it does not copy PK:FK constraints,
descriptions or default values across across.
So, is there a safe way to copy everything?
thanks - dave
--
thanks - dave
"Michael Cheng [MSFT]" wrote:
> Hi Dave,
> Thanks for your post.
> From your descriptions, I understood you would like to copy database via
> two computer that are not in the same domain. If I have misunderstood your
> concern, please feel free to point it out.
> Here are two Knowledge Base articles describing how to move database
> between computers. You could BACKUP the database and then use WITH MOVE
> parameter to RESTORE it in the new computer.
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/kb/314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/kb/221465
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||David,
Have you tried exporting the schema, and then using bcp to export the
data? I used to do this all the time on databases that were shipped half way
around the world )and definatley not on the same domain). Basically you
would be doing what dts does under the covers.
Bob
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
> So, is there a safe way to copy everything?
> thanks - dave
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>> Hi Dave,
>> Thanks for your post.
>> From your descriptions, I understood you would like to copy database via
>> two computer that are not in the same domain. If I have misunderstood
>> your
>> concern, please feel free to point it out.
>> Here are two Knowledge Base articles describing how to move database
>> between computers. You could BACKUP the database and then use WITH MOVE
>> parameter to RESTORE it in the new computer.
>> HOW TO: Move Databases Between Computers That Are Running SQL Server
>> http://support.microsoft.com/kb/314546
>> INF: Using the WITH MOVE Option with the RESTORE Statement
>> http://support.microsoft.com/kb/221465
>> Thank you for your patience and cooperation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Hi
See inline:
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
What do you mean by this?
You can use the MOVE option on the restore command if the files are to be
located in a different drive/directory. This is not that different to
specifying the new locations when you use sp_attach.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
No. Coping the data file and the re-attaching the original does not move the
original database. Attaching the copied data files onto the second server
would accomplish what you require!
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
This is usually considerable longer as well!
> So, is there a safe way to copy everything?
All three are safe well established ways to move/copy a database. Re-read
the link Michael posted.
> thanks - dave
>
John
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>> Hi Dave,
>> Thanks for your post.
>> From your descriptions, I understood you would like to copy database via
>> two computer that are not in the same domain. If I have misunderstood
>> your
>> concern, please feel free to point it out.
>> Here are two Knowledge Base articles describing how to move database
>> between computers. You could BACKUP the database and then use WITH MOVE
>> parameter to RESTORE it in the new computer.
>> HOW TO: Move Databases Between Computers That Are Running SQL Server
>> http://support.microsoft.com/kb/314546
>> INF: Using the WITH MOVE Option with the RESTORE Statement
>> http://support.microsoft.com/kb/221465
>> Thank you for your patience and cooperation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Hi Dave,
Thanks for your questions.
All three are safe way moving the database files. If you have any questions
about that KB articles, please feel free let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Copying a database - not on a domain

Hi;
I am trying to copy a database from one computer to another. Neither
computer is on a database.
Everything I have tried has failed. And everything I find under google says
that the databases have to be in the same domain.
How can I do this if neither computer is in a domain?
thanks - daveHi Dave,
Thanks for your post.
From your descriptions, I understood you would like to copy database via
two computer that are not in the same domain. If I have misunderstood your
concern, please feel free to point it out.
Here are two Knowledge Base articles describing how to move database
between computers. You could BACKUP the database and then use WITH MOVE
parameter to RESTORE it in the new computer.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/kb/314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/kb/221465
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi;
First off, no domains are involved - both Sql Server installations are
running on machines in a workgroup.
1) The backup/restore approach, if the drive letter changes (it does),
requires changing the system tables and has warnings that this can totally
hose up the Sql Server system.
2) Sp_detach/attach is for moving a DB and I want to copy it.
3) Export/Import seems to work ok - but it does not copy PK:FK constraints,
descriptions or default values across across.
So, is there a safe way to copy everything?
thanks - dave
thanks - dave
"Michael Cheng [MSFT]" wrote:

> Hi Dave,
> Thanks for your post.
> From your descriptions, I understood you would like to copy database via
> two computer that are not in the same domain. If I have misunderstood your
> concern, please feel free to point it out.
> Here are two Knowledge Base articles describing how to move database
> between computers. You could BACKUP the database and then use WITH MOVE
> parameter to RESTORE it in the new computer.
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/kb/314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/kb/221465
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||David,
Have you tried exporting the schema, and then using bcp to export the
data? I used to do this all the time on databases that were shipped half way
around the world )and definatley not on the same domain). Basically you
would be doing what dts does under the covers.
Bob
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...[vbcol=seagreen]
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
> So, is there a safe way to copy everything?
> thanks - dave
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>|||Hi
See inline:
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
What do you mean by this?
You can use the MOVE option on the restore command if the files are to be
located in a different drive/directory. This is not that different to
specifying the new locations when you use sp_attach.

> 2) Sp_detach/attach is for moving a DB and I want to copy it.
No. Coping the data file and the re-attaching the original does not move the
original database. Attaching the copied data files onto the second server
would accomplish what you require!

> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
This is usually considerable longer as well!
> So, is there a safe way to copy everything?
All three are safe well established ways to move/copy a database. Re-read
the link Michael posted.

> thanks - dave
>
John
[vbcol=seagreen]
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>|||Hi Dave,
Thanks for your questions.
All three are safe way moving the database files. If you have any questions
about that KB articles, please feel free let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Copying a database - not on a domain

Hi;
I am trying to copy a database from one computer to another. Neither
computer is on a database.
Everything I have tried has failed. And everything I find under google says
that the databases have to be in the same domain.
How can I do this if neither computer is in a domain?
thanks - dave
Hi Dave,
Thanks for your post.
From your descriptions, I understood you would like to copy database via
two computer that are not in the same domain. If I have misunderstood your
concern, please feel free to point it out.
Here are two Knowledge Base articles describing how to move database
between computers. You could BACKUP the database and then use WITH MOVE
parameter to RESTORE it in the new computer.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/kb/314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/kb/221465
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi;
First off, no domains are involved - both Sql Server installations are
running on machines in a workgroup.
1) The backup/restore approach, if the drive letter changes (it does),
requires changing the system tables and has warnings that this can totally
hose up the Sql Server system.
2) Sp_detach/attach is for moving a DB and I want to copy it.
3) Export/Import seems to work ok - but it does not copy PK:FK constraints,
descriptions or default values across across.
So, is there a safe way to copy everything?
thanks - dave
thanks - dave
"Michael Cheng [MSFT]" wrote:

> Hi Dave,
> Thanks for your post.
> From your descriptions, I understood you would like to copy database via
> two computer that are not in the same domain. If I have misunderstood your
> concern, please feel free to point it out.
> Here are two Knowledge Base articles describing how to move database
> between computers. You could BACKUP the database and then use WITH MOVE
> parameter to RESTORE it in the new computer.
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/kb/314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/kb/221465
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||David,
Have you tried exporting the schema, and then using bcp to export the
data? I used to do this all the time on databases that were shipped half way
around the world )and definatley not on the same domain). Basically you
would be doing what dts does under the covers.
Bob
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...[vbcol=seagreen]
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
> So, is there a safe way to copy everything?
> thanks - dave
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
|||Hi
See inline:
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
What do you mean by this?
You can use the MOVE option on the restore command if the files are to be
located in a different drive/directory. This is not that different to
specifying the new locations when you use sp_attach.

> 2) Sp_detach/attach is for moving a DB and I want to copy it.
No. Coping the data file and the re-attaching the original does not move the
original database. Attaching the copied data files onto the second server
would accomplish what you require!

> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
This is usually considerable longer as well!
> So, is there a safe way to copy everything?
All three are safe well established ways to move/copy a database. Re-read
the link Michael posted.

> thanks - dave
>
John
[vbcol=seagreen]
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
|||Hi Dave,
Thanks for your questions.
All three are safe way moving the database files. If you have any questions
about that KB articles, please feel free let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.