How To transfer tables made in sql server 2008 from one machine to another machine!!

[triple s]

Hi all, I am having a very small(may be a foolish) question, I have made tables in a database using SQL Server 2008. It was done on my friend's PC. I want to transfer those tables (using Pendrive, cd etc) to my PC.. So, anybody can tell me where exactly i can find those database and tables too??

Actually I had specified a path for DB and tables(it's D:/sql). And got there 2 file with extensions .mdb and likewise. But while transferring those files to Pendrive if gives me an error like "this file is used by sql server". please let me know if any other methos is there.. Please reply ASAP.. thanking you in advance!!

 

[Sei]

Re: How To transfer tables made in sql server 2008 from one machine to another machin

Firstly, you cannot simply copy the database files(mdf and ldf) without stopping the SQL server service.

For you to be able to use it on your own PC, there are 2 ways. Either you extract the tables to an excel/CSV file or you transfer the database(mdf) and log(ldf) files to your PC and attach them to SQL server.

If you want to do the first, then you can do it either via SSIS or via the query mode -

SSIS - How to: Run the SQL Server Import and Export Wizard

Query - Export data from SQL Server to Excel without using SSIS or DTS « Dayananthan’s Weblog

If you want to copy the database (data file + log file) to your own PC to run it in by attaching it in SQL server (you cannot open mdf and ldf in notepad/wordpad if that's what you were thinking):

Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps - PersianAdmins

How to: Detach a Database (SQL Server Management Studio)

Do you have SQL server installed on your local pc??

 

[triple s]

Re: How To transfer tables made in sql server 2008 from one machine to another machin

Yes.. And Can anyone tell me hoe to stop this SQL server service??

 

[asingh]

Re: How To transfer tables made in sql server 2008 from one machine to another machin

Take a backup of the whole DB, and restore it on the other system.

 

[triple s]

Re: How To transfer tables made in sql server 2008 from one machine to another machin

Take a backup of the whole DB, and restore it on the other system.

Will you please elaborate??

 

[2kewl]

Re: How To transfer tables made in sql server 2008 from one machine to another machin

If you have already created the tables in your new server, I guess you could write a simple program to BCP OUT the data from the old server and BCP IN the same to the new server.

A DB refresh will probably work out better though. Have no idea how that works. Some one who is a DBA should be able to explain better.

 

[asingh]

Re: How To transfer tables made in sql server 2008 from one machine to another machin

On Source DB:

1. Right click on the DB, choose tasks, choose backup.

2. Give it a easy to find location, and in the name append .BAK at the end. Now create the backup file.

3. Copy the file where you want too (pen drive / online portal).

On Destination:

1. Create a blank DB with the same name.

2. Right click, tasks, restore.

3. Show it the backed up DB.

4. There is a setting in there, "override current DB". (Since your destination DB is a blank DB, makes no difference).

Else let me know. Sorry cannot give screen shots, am home now.

 

[skumar9988]

Re: How To transfer tables made in sql server 2008 from one machine to another machin

You can stop SQL server either from Surface area configuration or from services(open service.msc from command prompt)

then u can copy both files .mdf and .ldf from data folder and take it to another PC(attach there)...

Other and better option is take backup and restore on other PC as suggested by asingh

 

[Sei]

Re: How To transfer tables made in sql server 2008 from one machine to another machin

Like asingh and skumar9988 suggested, you can do either of the things. Both will work the same way. Hope you went through the last 2 links I provided. If you still have any issues, I would strongly suggest using google to search for it. There are hundreds of easy tutorials available. Here are some -

Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps | Packt Publishing Technical & IT Book Store

SQL Server 2005 - Database Backup

How Do I... Restore a SQL Server database to a new server? | Microsoft Windows | TechRepublic.com