| Author |
 Topic  |
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
 Posted - 23 September 2011 : 06:21:40
|
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
|
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 |
 |
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
Posted - 23 September 2011 : 07:21:19
|
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 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 23 September 2011 : 07:27:30
|
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 |
|
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
Posted - 23 September 2011 : 07:54:33
|
| 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 |
|
|
|
balexandre
Junior Member
Denmark
418 Posts |
Posted - 23 September 2011 : 17:37:58
|
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"
|
|
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
Posted - 26 September 2011 : 05:30:43
|
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 |
|
|
| |
Topic |
|
|
|
