Backup of SQl

[Jagjit]

Hi

How we can take a Backup of SQL Express Database using Visual Basic as Front-End . I want to give user an option to take Backup.

Thanks

[SamOscarBrown]

Can you use filecopy?

[Zvoni]

Can you use filecopy?

On an SQL Express Database?
I'd rather do an SQL-Dump into a Textfile (csv) per Table and be done with it.

[SamOscarBrown]

Probably......that is why I said "Can?" "-) Just downloaded SQL Express yesterday, and plan to start learning to use it with VB6....didn't realize the database was like ORACLE's, where a dump IS the best option. Lo Siento.

[IanRyder]

Hi,

Have a look at a previous post of mine on another forum. This is in .NET but I am sure you can accommodate this example in VB6:-

http://www.vb dot net forums .com/mysql/53335-code-backup-restore.html

NB. Remove the spaces in the link since this forum does not allow linking to the forum I am trying to direct you to??????

Cheers,

Ian

[si_the_geek]

Thread moved to the 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

NB. Remove the spaces in the link since this forum does not allow linking to the forum I am trying to direct you to??????

A site with a very similar name (without the S) spammed us quite a bit at some point, so their name was added to our filtering system - which unfortunately causes this one to be partially masked too.

[SeanGrebey]

WHy not just write a Stored Proc to backup your database (tons of references on the web on how to do this), and then call the procedure from you VB application?

[IanRyder]

Thanks si_the_geek - that explains a lot.

Cheers,

Ian

[techgnome]

Can you use filecopy?

Eww! No... would not recommend that... 1) takes up a lot of space, 2) you would have to copy both the mdf and the ldf files... see #1, 3) The files are live and you run the risk of corrupting the database... in the amount of time it takes to copy the mdf, the ldf will (may) be getting updated... now the two are out of sync.

On an SQL Express Database?
I'd rather do an SQL-Dump into a Textfile (csv) per Table and be done with it.

That's almost worse than doing a filecopy. I know it's the way MySQL works, but I don't like it and it takes an exorbitant amount of time to run, and don't forget point 1 & 3 above...

WHy not just write a Stored Proc to backup your database (tons of references on the web on how to do this), and then call the procedure from you VB application?

That's the sanest thing I've heard... although I wouldn't do it in a stored proc... just issue the commands directly from the app... it's easiest and fasted way to ensure that the backup is complete and proper.

-tg

[Jagjit]

Hi

Can u tell me some links

Thanks

[szlamany]

This is the T-SQL command used in MS SQL to backup a database

BACKUP DATABASE DocTest TO DISK = 'D:\some folder\DocTest_20130223.bak'

Note - while this is fine to execute against a MS SQL Express type of database - DO NOT DO this against a production SQL SERVER running a NON-EXPRESS version. If that SERVER is already running maintenance plans for doing backup you should use the MAINTENANCE PLAN to run a ONE-OFF backup.

[geek648]

You could shell out to a command line to export the required tables, using sqlcmd or bcp -
http://stackoverflow.com/questions/4...r-using-sqlcmd

[szlamany]

Why are we talking about EXPORTING tables and making TEXT FILES here?

MS SQL SERVER - a "multi" physical file database system (has both MDF and also LDF files - data and log portions) - should only EVER be backed up with the T-SQL BACKUP statement.

This is executed just like any other SQL statement - using a regular command object.

It can be done regardless of whether the client is on the same "workstation" as the DB itself - so it's network appropriate.

[geek648]

There's "ideal and proper", and then there's "what's good enough and what he wants to do for his own reasons"

[szlamany]

It is really important to realize that TG mentioned a point that is key here. Let me elaborate...

Changes made to a MS SQL SERVER database are first PLACED in the LOG file.

The LOG does NOT ONLY contain UNCOMMITTED data - but also COMMITTED data that the lazy-time algorithms have not yet written back to the MDF file.

That means when you run a select from the DATABASE there is a chance that data comes from both the MDF and LDF physical files.

So a FILE COPY of the MDF or LDF is kind of unsafe - as you could imagine the timing of these physical disk copies will never correlate with the state of the MDF and LDF files as it is managed by the DATABASE ENGINE.

The BACKUP command I show is a command handed off to the DATABASE ENGINE to do a CLEAN BACKUP of both the MDF and LDF files. These two files are placed into a single .BAK file - usually much, much smaller then the actual MDF and LDF files which have tons of allocated disk spaced used for database reasons. Single file that can be RESTORED and guaranteed to be a real copy of the DB at the POINT IN TIME it was run.

If this is a single user SQL SERVER EXPRESS type of setup then a file copy could be done - but that's a bad habit to get into and doesn't actually, in my opinion, take less time or code to accomplish.

[FunkyDexter]

I agree with SzLamany and Techgnome.

1. A backup is the correct mechanism provided by the DB engine. It's guarenteed to give the expected resut.
2. It's trivial to carry out. Considerably easier than a filecopy, for example, which will require the additional actions to take the database off line before the OS will allow you access. And MUCH easier than trying to copy the data table by table (which will lose all your metadata).

Trying to do a filecopy in these circumstances is like going to a restaurant and insisting they serve your soup in the collander you brought with you instead of the bowl they were happy to provide.