how can i copy a SQL server from one machine to another

Question

i am accessing a SQL server db over a slow network. I have another machine with SQL server on it on a machine with a faster connection. Is there anyway i can easily get this database copied from one machine to another (both schema and data)

Answer 1

Do you have access to the backups from the slow SQL Server? If you do, you could get the most recent backups & transaction logs and restore them onto the faster server. Then once that's done, you could possibly setup an SSIS package to sync the data between the two (if that's something that you need to do).

Answer 2

Yes. Copy the .MDF and .LDF for the database and re-attach it on your SQL server. You might need to (temporarily) detach it on the source server.

Answer 3

If your using SSMS and MS SQL you can:

• right click on the database and go to Tasks > Backup...
• save it somewhere on disk, giving it a name like databasename.bak
• transfer the .bak file to the new machine
• in SSMS on the new machine, right click on DataBases and then Restore Database...
• specify a name for your database, click the From device radio button and browse to your databasename.bak file (make sure and select the checkbox after choosing your file)
• in the left navigation you can click on Options to find more settings (usually optional)

Hope this was helpful...

~ Dan

Answer 4

A couple ways come to mind. In order of preference and likelihood of success:

1. Backup the existing database, copy the backup file to your new server, perform a restore. Then fix any user accounts that may need to be corrected. Ask another question for details on this.

2. Use a tool like SQL Compare and/or SQL Data Compare to make a copy on a new server.

3. Perform a detach on the original server, copy files, pray, and perform attach on new server.. Be sure to reattach them to the original server if necessary. Although this will usually work, it fails enough that I tend to stay away.

4. Use the Copy Database command. <- Note that I have never seen this work, and I give it a shot about once a quarter when I start working in a new environment.

5. Build an SSIS package to transfer schema and data.

6. Manually by scripting the database, running those scripts on the new server. Then add a linked server reference and run things like INSERT INTO newdb.dbname.dbo.tablename SELECT * FROM olddb.dbname.dbo.tablename..

Incidentally before doing options 1, 3, 4, or 5 make sure that both servers have the exact same service pack and patching levels. That will increase the likelihood of everything working out.