Copying SQL Server DB using script

Question

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 authentication

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)

Paul Suart · Accepted Answer · 2009-04-02 11:40:04Z

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.

answered Apr 2, 2009 at 11:40

Paul Suart

6,7418 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ducain · Accepted Answer · 2009-04-02 12:30:38Z

1

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.

answered Apr 2, 2009 at 12:30

Ducain

1,6013 gold badges19 silver badges28 bronze badges

Comments

Charles Graham · Accepted Answer · 2009-04-02 11:42:50Z

0

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

answered Apr 2, 2009 at 11:42

Charles Graham

24.9k14 gold badges48 silver badges56 bronze badges

2 Comments

Midhat Over a year ago

the backup will save to the server disk. How can i get it to save to my local disk.

Nick Kavadias Over a year ago

back it up to the server & share it. do the restore across the network

Collectives™ on Stack Overflow

Copying SQL Server DB using script

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related