Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Help Groups for Snitz Forums 2000 Users
 Help: Database: MS SQL Server
 Moving DB from 1 host to another + making backups
 New Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MarcelG
Retired Support Moderator

Netherlands
2625 Posts

Posted - 23 September 2011 :  06:21:40  Show Profile  Visit MarcelG's Homepage  Reply with Quote
Since a couple of years Oxle is running on SQL instead of MS Access.
It's all running fine, but there's two things that have been bugging me ever since.
Me being really not savvy with SQL server technology is most likely the biggest problem here.
That's why I want to ask the following questions.
Ultimate goal of this topic is to create two in-depth howto's for both questions to be used by me and my fellow SQL Snitz users.

One: how do I make a backup?
With MS Access, making a backup was easy ; just FTP to the host and download the MDB.
With SQL, making a normal backup can only be done to the SQL server's filesystem, where most hostingproviders will not give you access to. So, even though I have access to the SQL server using the SQL Server Management tools and even though I can create an ad hoc backup there, I cannot access that backup.
Now, the question is: how do I make an export of the SQL database that can serve as a backup to be used in disaster recovery.
Which tools are required, and how do you make sure that the database structure and proper indexes are there once you import that "backup".

Two: How do I migrate my SQL database from one host(ingprovider) to another, without "native access" to the SQL server.
This is so to speak step two of question one ; once I know how to export the database to a transportable format, how do I load that database on another SQL server, for instance when you switch hosting provders, or in case of a disaster recovery scenario.
Again, which tools are required, and how to make sure the database structure is correct once loaded.

I assume that several howto's have been created over the years, so I might be asking for something that's already there. I certainly hope so.

portfolio - linkshrinker - oxle - twitter

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 23 September 2011 :  07:04:41  Show Profile  Send ruirib a Yahoo! Message
I never saw that covered here.

Question 1: There are several ways to accomplish this.

1. I always thought this is one area (probably the single one) where SQL Server could benefit a lot if it supported the what MySQL has supported for a very long time - a completed, text based dump, of the SQL needed to build the database structure and the data stored. That took a lot of time to become reality - up to SQL Server 2008, the major client interface tool - SQL Server Management Studio (known as Enterprise Manager in versions previous to 2005), only allowed you to script the database structure. Starting with SQL Server 2008 R2 you can script the database structure and the database data to a text file, and that file can then be used to rebuild the database, should that become needed.
So this could be a way to do it, but you'd need the client tools from SQL Server 2008 R2 version. This is not a big deal, however, because if you need to manage important SQL Server data, you can (and maybe should) buy the Developer version of SQL Server, which costs around $50. This version includes all SQL Server features (feature wise it's the same as SQL Server Datacenter), but can only be used in a development environment. Nothing serious, because you wouldn't be running production dtabases from it, anyway. You get so much with it, and it's hard to beat the price.

2. With the client tools (included with the Developer Version, of course), a new set of possibilities is opened. Probably the easiest way to keep a backup is through a 2 step strategy:

a. Generate a script from your live database and use it to recreate a database with the same structure in your local installation of SQL Server (yes, the Developer version can be used for this)

b. Use SQL Server Integration Services to export the data from the production database (at the host) to your local database. This can all be started by running a wizard, that will guide you in doing this. The good thing is that once you complete the export instructions, you can saved them as a SSIS package, which you can then run manually, whenever needed or, even better, that you can schedule and run using the SQL Server Agent. This means that once setup, you can run your backup daily, weekly, or at any other interval you wish.

There may be a few hurdles along the way. Some SQL Server knowledge is required for each of these steps, but it is doable after some starting pains, for those less familiar with SQL Server.

There are other ways besides these, but these are the easiest to implement.

Question 2: This is achieved through the mechanisms explained in the answer to question 1. Just treat the destination server as the server where you will backup your database. SQL Server Management Studio can connect to different, non local, SQL Server servers. So the strategy would be to recreate the database structure through a script, as explained in 1. above and then use SSIS to export the data from the old database to the new database.

A detailed how-to would be rather long and I don't really have the time to create it, sorry.


Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

MarcelG
Retired Support Moderator

Netherlands
2625 Posts

Posted - 23 September 2011 :  07:21:19  Show Profile  Visit MarcelG's Homepage
Rui, this is an excellent starting point! Thank you for that!
Am I right to assume that even though you'd be using the client tools from SQL Server 2008 R2, your current SQL server does not need to be SQL Server 2008 R2 ?

portfolio - linkshrinker - oxle - twitter
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 23 September 2011 :  07:27:30  Show Profile  Send ruirib a Yahoo! Message
You're welcome, Marcel .

Yes, you are right in that assumption. The SQL Server 2008 R2 client tools will connect to any SQL Server in existence (and will to upcoming ones too, but new features in these upcoming ones likely won't be seen by them). So you can use it to manage even databases on SQL Server 2000 servers. The tools also allow you to specify the version for which the database script, for example, will be generated.

For a person with no SQL Server experience, I believe a full scripting based approach may be the easiest, but in all honestly I have only used it for very small databases and I also added some code of my own to make sure that annoying things like database constraints do not interfere with the process.

If you decide to implement this, Marcel, just post any questions you may have and I will try and help with them.


Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

MarcelG
Retired Support Moderator

Netherlands
2625 Posts

Posted - 23 September 2011 :  07:54:33  Show Profile  Visit MarcelG's Homepage
I will be picking this up in the upcoming weeks. As soon as I run into things I really do not understand, I'll shout. :-)

portfolio - linkshrinker - oxle - twitter
Go to Top of Page

balexandre
Junior Member

Denmark
418 Posts

Posted - 23 September 2011 :  17:37:58  Show Profile  Visit balexandre's Homepage  Send balexandre an ICQ Message
let me know if you need help Marcel, thoughout twitter as well, I did plenty of changes :)

Are you moving into the cloud now, or to that speedy host? Amazon EC2 seems lovely

Bruno Alexandre
(Strøby, DANMARK)

"a Portuguese in Danmark"


Go to Top of Page

MarcelG
Retired Support Moderator

Netherlands
2625 Posts

Posted - 26 September 2011 :  05:30:43  Show Profile  Visit MarcelG's Homepage
Bruno, I'll certainly ask you for help when needed. :-)
BTW, I'm not moving anywhere at the moment, however I'd like to have the plan available when I need it.
Always be prepared, that's the motto.

portfolio - linkshrinker - oxle - twitter
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000 Version 3.4.07