So you have a remote SQL Server 2005 database running on a different network than your client machine and you want a backup copy of that database. Most common scenario, the database is on your web hosting server. Somehow I never thought this would be a big issue. I am so used to just using the SQL Management Studio, right clicking on a database, selecting Tasks, and then backup.
But when you backup SQL Server, it is to a local drive or a mapped network drive that the server itself has access to. Implying that if you are accessing SQL Server on a server on another network on a machine that you do not have access to, you cannot backup SQL Server to your drive. So how do you get a backup copy?
I found it quite frustrating that I can connect to the database using SQL Management Studio, run queries, make changes etc, but I cannot get a copy of the database to my machine. Under Tasks there is an option to Copy Database but I just could not get that to work. I kept getting a security Exception. One day I will have to look into why. Until then lets proceed to the so called right way to do it.
Remember DTS from all prior versions of SQL Server? We it kind of vanished with SQL Server 2005. Not really, it has a new name, SSIS and is installed when you install Integration Services. Once installed you will see "SQL Server Business Intelligence Development Studio" on your Start Menu under SQL Server 2005. Click on it and Visual Studio will launch. At first I thought this has got to be a mistake. But no, I was at the right spot. I created a new Integrated Services Project and under that project by right clicking on solution explorer I selected the import export wizard.
Now the familiar DTS Wizard pops up and you can do what you were used to doing in all prior versions of SQL Server 2005. Connect to the remote database and get all your data to a local database. Why couldn't this be a single link on Management Studio as a simple Copy Database? Because that would make it logical and simple.
By the way, my data transfer failed several times before succeeding, so be patient. :-)
No comments:
Post a Comment