I found a lot of similar questions in the archives, but none quite answered this.
I need to copy a database from one server (SQL Server 2008) to another. Each server is in its own system, and no computer can connect to both servers.
My original plan was to backup the database, copy it to an external hard drive, move it to the new server, and restore. Unfortunately, the server with the database is entirely out of storage, preventing me from making the backup file. Is there a way to backup a database from a remote server directly onto an external hard drive, or onto the local computer? When I use the backup wizard, it only shows me local paths.
Is there another method I should be thinking of using?
Thanks very much.
Best Answer
You can detach the database, copy the data (.mdf) and log (.ldf) files to external storage, then reattach those files on the new server. When you detach a database, SQL Server closes the data and log files and the database is removed from the server. You can then work with the files safely. If you want to retain the database on the original server you just reattach the database.
For instructions on how to do this via SQL Server Managment Studio see this article.
For instructions on how to do this via T-SQL see this article.