SQL Server 2005 Backup a Remote Database

Monday, 15 September 2008 23:17 by RanjanBanerji

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. :-)

 

 

Categories:   SQL Server
Actions:   E-mail | Permalink | Comments (11) | Comment RSSRSS comment feed

Comments

September 29. 2008 07:06

Sandra

Hi,
Thank you for your post - it was really helpful.
However, after following the steps above I've got a copy of my remote database without any indexes, keys or constraints.
Did it happen to you? Do you know how to solve this problem?

Sandra

September 29. 2008 13:33

RanjanBanerji

Sandra,

This will happen.  DTS/SSIS import export of data does just that, imports and exports data.  I do not believe any other database objects get transferred.  So its a good way to backup your data but thats about it.  But to be honest I had no need for the other objects.  I will take a look and see if there is a better way to configure this.

One painful way would be to first connect to your remote database using SQL Management Studio.  Right click on the database select Tasks then generate scripts.  You will get a wizard.  Go through the steps and generate teh scripts to create your DB.  Then create your database using these scripts.  Once you have the database you can run Import Data.

Thanks,

Ranjan

RanjanBanerji

September 30. 2008 03:33

Sandra

Ranjan,

Thank you for your answer.
So I think Import Data will not help me.

My problem: I need a tool that would allow me to make a backup/copy of a remote SQL Server database and save it to my local SQL Server, and also need to schedule it to happen four times a day.

On SQL 2000 I used to do this using a simple DTS job. But now that I have upgrade to SQL 2005 I cannot find a way to do this: Import/Export does not transfer the keys and constrains, Copy Database is not working and I don't have right permission to create a Backup job on the host server.

Maybe I can first create the database using a Script, as you suggested, then create a job to Import data and schedule it. The only problem is that every time I have modify any table structure I will have to recreate the Script and the Import job.
Do you know any other way to do this?

Thanks,
Sandra

Sandra

September 30. 2008 23:56

RanjanBanerji

Sandra,

Off hadnd I cant think of a more elegant way.  When you import data you can edit the create table scripts but then from that point any chnages will also require that the import scripts be changed.  So I am not sure how that is any better.  

I guess your best option is to implement a practice whereby all changes to your main database and implemented via scripts that are subsequently run on your backup database.  This way the data import will always keep working.

Thanks,

Ranjan

RanjanBanerji

October 1. 2008 04:17

Sandra

Ranjan,

I am now doing some tests using SQL 2005 Integrations Services to see if there is an easiest way of doing that.

Transfer SQL Server Objects Task is not working with more than 50 tables and Copy Database is returning some errors... (the good point is that I've learned a lot of new features through this process – any one of them works for my problem right now... maybe in the future will be helpful).

If none of this works, I am going to implement your suggestion.

Thank you again.

Sandra

October 1. 2008 07:13

RanjanBanerji

Sandra,

I know that with SSIS a lot of new features have been brought in.  Just that there is a lot of learning to do and I have never had to use it.  I hope you get results for what you are seeking.  Backing up a remote database to a local source seems like such a obvious feature that I am a little surprised that MS has not made it easier.

Thanks,

Ranjan

RanjanBanerji

October 31. 2008 06:24

Soksa Icy

Hi guys. There is an easy way, albeit not provided by Microsoft Smile Hey, don't get me wrong, I LOVE BILL GATES, but he has not been running things there for a long time. Anyway, the "free version" of SQL Manager for SQL Server (Lite) has an "Extract Database" task. Which does exactly what you need. Extract the database, with structure, and data to your local drive Smile I've been looking everywhere for that. You can download the tool from their website [www sqlmanager net]

Cheerios
-Icy

Soksa Icy

October 31. 2008 21:16

RanjanBanerji

Icy,

Thanks a lot for the information.  This will definitely make life easier.

Thanks,

Ranjan

RanjanBanerji

December 7. 2008 05:20

Durga

When i am using DTS it does not import any keys like Primary key....

Durga

December 19. 2008 14:57

Zuhaib

it was very frustrating for me to deal with this problem, until I found your post.

Thank you .. you saved me a lot of time.

Zuhaib

December 22. 2008 00:04

ranjanbanerji

Zuhaib,

You are most welcome.

Ranjan

ranjanbanerji

Add comment


(Will show your Gravatar icon)

  Country flag

biuquote
  • Comment
  • Preview
Loading