Now I am going to discuss SQL server, as most people know this is the database offering from Microsoft. It is offered in many different forms from a cut down express edition right up to a full blown data centre enterprise edition.
I recommend the following great books for more on this topic.
SQL Server 2000 Backup and Recovery (Database Professional's Library)
Sql Server Backup and Recovery
Sams Teach Yourself SQL Server 2005 Express in 24 Hours (Sams Teach Yourself)
Microsoft SQL Server 2005 Express Edition for Dummies: Express Edition (For Dummies): Express Edition
If you want to know more about SQL Server please see the Microsoft website
Don't have the express edition or still using the MSDE (which is not longer supported by the way) - Go Here
(don't download the version with advanced features unless you plan on carrying out reporting on your DB, in most case you wont. So save yourself time and download the other one.)
As many people know that if you are working in a large organisation and developing against the full edition you get the benefits of having access to services such as full replication and DTS. No if you are not developing against this edition you don't have these features, as more often than not people are developing locally on either the MSDE / Express edition or they are developing locally with a full version buy host their solution somewhere which they either only have RDC access or they dont have enough access to install another on the server.
Now this causes problems with how to backup and keep a safe copy of you database, because if you are running mixed versions of SQL Server, such as full locally and express on your server, or express on both. Then you cannot setup a DTS schedule to carry out your backups.
The Ideal Solution
Ok here is the ideal solution and the one you will have if you are working for a company with the correct infrastructure. This solution is simple.
| Local | Deployment/Server | Backup Solution |
| SQL Server (any full edition) | SQL Server (any full edition) | Scheduled DTS packets |
This assumes that you are not running the development version on your local machine
Now with this solution providing you have access to the deployment both over a network connection, you can have a scheduled DTS package deployment to handle your backups.
Now the main problem is that alot of developers as actually developing against the express edition and only deploying to a server that is running the same version.
So the question here is why cannot I just do a DTS scheduled back up?
The answer is simple, the express version of SQL server is a cut down version and cannot support the broadcasting relationship necessary to have a scheduled DTS backup.
The art of using DTS fails into the area of replication, and in order to support full replication of a database you need both to be full versions.
The Most Common Situation
Now in most cases developers are working against the free express editions, and this means that they are deploying their solution on this version also. In this scenario most people are sending the files to their server by FTP, and may have RDC access.
| Local | Deployment/Server | Backup Solution |
| SQL Server Express / Development Edition | SQL Server Express | Scripted / Backup Scheduling |
Now in this case we cannot do a DTS replication to keep a nice copy of our database safe so there are two solutions.
- A direct DB backup or restore
- A Scripted SQL Backup
Now in this article I am only going to go into the Scripted backup, and the reason for this is it allows you to compile the SQL script on your server into a location that you have FTP access to them simply download the SQL file.
Ok onto the nitty gritty. How do you do it.
- Log on to your SQL Server database.
- Right click on your database and go into the script option.
- Inside here you normally get 2 option CREATE and DROP. These are simple one with just create your backup the other with drop your existing DB and then create your backup.
- Simply select the one of preference, my preference is DROP, and pick a location.
Once you have this file on your server in a location where you can FTP it, copy it to your local machine, once there. Run this script on your local DB, make sure you point it at the correct DB.
WARNING - IF YOU FTP THE FILE DOWN MAKE SURE YOU DELETE / MOVE THE FILE LOCATION ON YOUR SERVER FROM WHERE YOU HAVE FTP ACCESS TO IT. REMEMBER IF YOU HAVE FTP ACCESS SO DOES EVERYONE ELSE.
There you have it a full backup of your database nice and safe.
I have to say it is possible to have remote access to your SQL Server on your web-server, but this means opening up all sorts of security concerns, including but not limited to the default SQL server port. Oh by the way that is 1433 in case you are wondering.
Because of the security issues of opening up a public SQL Server box for access via remote network connections I will not go in to the solution of how to connect and copy data, all through your local SQL management express console.
Now there is a slight exception to this, if people want me to go into how to access your SQL server box directly from your local machine I can do. But you will have to ask me nicely.
So if you want to know how to gain access to your remote SQL Server box from your local machine, just say so in your comment.
Sean J Connolly
Visit AJAX Web Development Store
DMS - Document Management, Webmail
BuzzProperties.co.uk - Online Property Sales and Letting
No comments:
Post a Comment