Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Database Backup

Author  Topic 

colgie
Starting Member

18 Posts

Posted - 2010-09-08 : 13:44:53
I currently backup my MS SQL SERVER 2005 databases by
converting 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

Posted - 2010-09-08 : 14:00:08
Why aren't you using BACKUP/RESTORE commands?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

colgie
Starting Member

18 Posts

Posted - 2010-09-08 : 14:21:16
http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups
I 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.
Go to Top of Page

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 statistics

So if it were me I would not be wanting to reinvent the wheel!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-08 : 14:48:29
colgie, my custom stored procedure meets your needs as do maintenance plans: http://weblogs.sqlteam.com/tarad/archive/2009/12/29/Backup-SQL-Server-2005-and-2008-DatabasesAgain.aspx

And here's how I refresh an environment: http://weblogs.sqlteam.com/tarad/archive/2009/02/25/How-to-refresh-a-SQL-Server-database-automatically.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-08 : 17:19:01
In case you are interested in seeing how my production environments are typicall configured, here you go: http://weblogs.sqlteam.com/tarad/archive/2008/06/30/SQL-Server-jobs-on-production-instances.aspx

All of those are configured as jobs through the SQL Agent.

What edition of SQL Server are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -