| Author |
Topic |
|
colgie
Starting Member
18 Posts |
 Posted - 2010-09-08 : 13:44:53
|
| I currently backup my MS SQL SERVER 2005 databases byconverting them to csv with an asp script and downloading the csvs.Another asp program restores them from the csvs. Can I download the actual databases so I don't have to worry about whether my programs convert to and restore from csv without error?I have a dedicated server and don't know if just downloading the actual databases and uploading them to restore would work. Are there other factors I would need to take into account, other files I would need to download etc? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
colgie
Starting Member
18 Posts |
Posted - 2010-09-08 : 14:21:16
|
| http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backupsI looked at this but I have to backup 10 databases automatically every day rather than specifying individual backups. My method works currently very quickly and restores very quickly but I am wooried that my asp programs may be flawed and may fail at some stage. I just need a quick way to backup all my databases so I thought if I could download them all at once it would be convenient.I may well be barking up the wrong tree. Basically I am new to this and am unsure of the procedures. Any help would be gratefully received.If backup and restore will do multiple databases on an automatic regular basis then perhaps you could point me in the right direction. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-08 : 14:29:45
|
If you export your database to CSV then all you have is the data. You don't have the database structure, indexes, foreign keys, constraints and so on.How do you cope with a column that itself includes a "," - or even a carriage-return + line-feed ? DO you lose precision on floating point numbers when you export/re-import?You also don't have differential backup ability, nor transaction log backups - so you cannot restore, for example, to 10:03 - when someone accidentally deleted half the customers  Much better to use SQL's backup IMHO as it takes care of all these problems. You can schedule the backups using SQL Agent - they will then just run at the appointed time and send you an EMail to tell you they worked OK (or that they failed ...) and will delete the backups that are older than N days ... and do maintenance like rebuilding indexes, updating statisticsSo if it were me I would not be wanting to reinvent the wheel! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
colgie
Starting Member
18 Posts |
Posted - 2010-09-08 : 17:16:21
|
| My databases have no floating point numbers and no line feeds etc. They have no foreign keys. When I encounter a comma I subsitute a pipe character etc etc. I am well aware however that my procedures are somewhat steam-driven and that is why I was concerned that they would ultimately find some way to fail. I am grateful for all the info and will act upon it immediately. I was not even aware that SQL Server Agent existed. I will look into it right now.Thanks once again for your time and expertise. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-08 : 20:17:36
|
| There is no advantage to exporting the data as you're doing, rather than simply backing up the databases.Backup/Restore is not only easy, but ask any DBA what their most important task is. You will get 100% agreement, it's having valid backups.Sounds like you have a hosted solution. If your host doesn't allow you to run native sql server backups, it's time for a new host. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-08 : 20:22:22
|
| by the way, Tara has made this easy for you -- she's shared her favorite backup script. There's no good reason to do otherwise. Though I would suggest toying with creating your own script -- for fun and to learn, and to customize it to your situation. And you can always fall back on hers. This is a win-win situaion for you. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-09 : 02:20:04
|
| I'll play devil's advocate here:"My databases have no floating point numbers"You may need to add a floating point column in the future"and no line feeds etc."Someone may cut & paste data from, say, Excel which has embedded characters such as line breaks"They have no foreign keys"You database probably should have! but my point was that you are not backing up the structure of your database. If you add another column today then that change is not backed up. If you lost your database tonight (the whole thing, not just the data) then you would have to recreate it, and you would have to remember that you added that column today - what you called it, what size you made it, and so on. A conventional SQL backup includes all the data, and the structure of the database too."When I encounter a comma I subsitute a pipe character etc etc."And if there is a Pipe character in the data? We had a client who moved to another provider. Their new provider asked for pipe-delimited export of the data. I asked what to do about Pipe characters in the data and never got a satisfactory answer (other than "Its a well know data exchange format that you should know about") ... out of curiosity I looked at the data to see if there were any pipe characters, or if I was making molehill-into-mountain; I found that one user had registered with the email address "||||||||@somedomain.com" - clearly hoping to muck up any attempt at spamming him!You may be in control of your data such that these are not real world worries for you, but in my experience future changes have a habit of messing up things like CSV data exchanges. |
|
|
|
|
Database Backup