Copying SQL Server DB using script

Question

Hi I have to copy a db from a remote server to my local dev machine. What is the best way to do it.

Last night i made a script for the remote db using xSQL Object and ran it on the local server. the script gave a bunch of errors. Below is the select @@version output for the 2 servers. is there a reason the script is not working

The remote server is using SQL server authentication, the local one is using windows authnetication

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) 
Jul  9 2008 14:17:44 
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
Jul  9 2008 14:43:34 
Copyright (c) 1988-2008 Microsoft Corporation
Express Edition with Advanced Services on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

Answer 1

It would be helpful if your provided some of the errors your script produced.

I notice you're using Enterprise and Express editions - these two editions have differing features, described here: http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx. Perhaps you're using a feature that Express edition doesn't support, or your database is too big.

Answer 2

Why don't you just do a backup and restore?

Answer 3

Not sure if the functionality is exactly the same in 2008, but in 2005, you can open up SQL Management Studio, right-click on the dbase you'd like to copy, and then click on Export Data.

This will guide you through a wizard, allowing you to export the data to another SQL dbase, including one that runs on your local machine. Used it many times - works great.