I recommend Backup and Restore because it also move your permissions, there is a known issue of orphaned permissions, meaning the permissions are created in the Master database but not in your database. The quick solution delete the restore and create a new one. Please download and read the free backup and restore chapter from SQL Server 2005 a beginner’s guide.

If you use another method remember to check your permissions if they were not moved you need to follow the direction in the section dealing with moving your logins. Hope this helps.

http://support.microsoft.com/kb/314546/en-us

http://books.mcgraw-hill.com/getbook.php?isbn=0072260939&template

Detach and attach will also work. Using T-SQL or Wizards.

 

If you want simply transfer  just generate  script and transfer through DTS.

Right click on database and export the tables to new server/database.

I'm using attach and deattach in my work... and sometimes exporting scripts

There are many way to transfer running database.

1- Using backup restore.

2. Using Replication.

Detach and Attach  is not the right way because there is chance of data loss.

Avinash 

 

 Attaching/Deattaching works fine on first build at production or Q/A. But whenever I fix bug I create scripts to update the production database.

Deattach and Attach database from SQL Server Managment Studio...

If you're looking to copy the database, you don't have to use backup/restore or detach/attach.  You can use the copy database feature.  This will capture the data, logins, and stored procedures.  Try it out.  Good luck.

 just do this

Go to Run->Cmd->

write->Net Stop Mssqlserver (or just shut down ur sqlserver 4 a while from

SQL Server Configuration Manager)

(After ,it has Stopped)
Go Tothis Direcrtory:-----

 

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

here in data 2 files of ur database are stored(in my case Tradenbids)

1. Tradenbids.mdf

2.Tradenbids_log.ldf

Copy these to file 

and paste  whaere ever you want

Now 

Go to Run->Cmd->

write->Net Start Mssqlserver (it will start mssqlserver)

now u have the whole   db , just copy and paste these to file whenever u want a backup

 

 

 hi,

 am  srikanth.i have two databases one at source and other at target with same structure .i want to track the changes of dml operations of tables and convert this into Xml file and that should be sent through email and updated to database how to solve this problem please reply it to my mail kanth456@in.com

 Why are you not  using Import/Export if your system is connected in LAN, other wise you can use backup and restore option which take all your permisson, Identity, Defaultvalue as per your master database.

 

 Hi ,see

If  u system is in local area connection(& in both the master & slave contain same formated server with same OS configuration) i think u can easily transfer the data(sometimes back i was at same confusion) the i take my senior help & u cannot even imagin that i tranfer througn external hard drive(by copying from the source & moving to any other ).so  .And if u still facing prob. then u can go for some extra expertise.

Ok then  ...Bye

 

 

One can also use Copy database option to copy databases from source server to destination server. Otherwise the best way is to use ETL (Extract -Transform & Load) feature of SQL Server which is available by virtue of DTS in SQL Server 2000 or SSIS in SQL Server 2005. Here you can create Packages using BIDS (Business Intelligence Development Studio) IDE given by SQL Server 2005 and then run these packages by creating a job in SQL server.

 You can copy .mdf & .ldf file. But for that, you have to first stop the sql server service. And this is one of the bad idea, because noone will give permission to stop service in a live server where a number of applications are running. So you may follow one of the following solutions.

 

1) You may generate query which will contains structure of all tables & all stored procedures, functions etc. (Data will not be copied in this process)

2) Create back up & restore on your local machine where you want. (It'll copy strucure as well as data)

3) Export data from one system & import data on another system by using DTS export wizard.