Hi, not sure I have should ask this in here or in the VS forums - but here goes anyway!
My vb app is written using VS2005 with an SQL database. I'm now distributing this to users to use with SQL Express.
It's hard enough explaining to them how to attach the database without going into the backup routines etc!
So what I'd like to do is to have a "backup" button in my app, that backsup the database, trancates the log files & then shrinks the files.
Is this possible?
This shouldn't be too hard. You can just call the backup command from your application.
Regards,
Matt Hollingsworth
Program Manager
Microsoft SQL Server
|||
 Matt Hollingsworth wrote: |
|
| This shouldn't be too hard. You can just call the backup command from your application. Regards, Matt Hollingsworth Program Manager Microsoft SQL Server |
Could someone give me some pointers to a basic script pls
|||
Create a stored procedure that executes these:
BACKUP LOG databasename WITH TRUNCATE_ONLY - this backups the active portion of the log in your database file
BACKUP DATABASE databasename TO DISK = 'K:\yourpath\yourbackup.bak'
I don't recommend shrinking the database nor the transaction log files as this will affect your database performance
|||
Please avoid the use of TRUNCATE_ONLY or NO_LOG backups.
They discard portions of your log which may not have been backed up, and could break your log chain. If you are doing regular log backups, or are in simple recovery mode, there should never be a reason to use these commands.
The SP approach is a good one though. If your app has a connection to the database, you should be able to issue a BACKUP DATABASE command in exactly the same way as you issue a SELECT statement.
Kevin
|||I agree Kevin, I just answered the question, though, did not explain further
|||
Is it possible to do this through VS2005 without adding a sp to the database. I have a few copies of the database "out there" & if possible just want to update the vs application itself, without asking users to amend the database?
Sorry if I'm being a bit thick!
|||
Sure.
As I said, if you have a connection that can issue a SELECT, it should be able to issue a BACKUP DATABASE (provided you have sufficient privs, of course). Just look up the syntax for BACKUP DATABASE in Books Online to determine how to get the backups that you need.
|||
I guess I'm a bit thick here - I can find plenty info on doing it through SQL, but nothing on the syntax to do it via my VB application...
Help!
|||
OK.
I'll take a step back here.
If what you are trying to accomplish is to use your application to direct SQL to back itself up to a file, you would use the same mechanisms you are using in your app to access the SQL data (ODBC etc), but instead of issuing 'SELECT foo FROM bar WHERE x = y', you would issue 'BACKUP DATABASE bar TO DISK='Q:\Backups\FooBackup.bck'
If you want your app to receive the backup stream and do something with it, you should read this documentation:
http://www.microsoft.com/downloads/details.aspx?FamilyID=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&DisplayLang=en
|||
Sample command in VB
Dim query as String= "BACKUP DATABASE databasename TO DISK='Q:\backups\databasename.bak'"
Dim command As New SqlCommand(query, connection)
command.Connection.Open()
command.ExecuteNonQuery()
Just change the query to the queries specified above
|||
Hi, making progress!
I have the VS part working now, but a few questions regarding the backup commands.
1. If I use a new filename for the backup I get "path not found", is there a "new backup" command, or do I need to create the file first?
EDIT: A bit of stupidity here! I was using a directory on the client machine & of course it's the SQL machine that matters!
2. A few people have said don't truncate etc, I don't understand this as if I don't truncate the log file it will surely just grow and grow? Do I backup the database and the log seperately?
EDIT: I'm doing the following:
BACKUP database TO DISK = "D:\backups....."
BACKUP LOG database TO DISK = "D:\backups..."
Working fine except that (because I haven't backed up for a while), the log file was 1GB and I got: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
I have now backed up the log file & reduced the space - but how do I increse the timeout?
|||
Think of SQL log files as a circular buffer.
In normal operations, as soon as all of the records in a section of the log are unused, that section ("VLF") can be reused.
In Simple recovery mode as soon as there are no open transactions referencing the are of the log, they are free. In Full recovery mode, log records are free after they are backed up in a Log Backup (if there are no transactions using them).
So, in normal operations, the log file is never truncated, but it is internally reused so that it doesn't just grow without bounds.
Even if you manually shrink the file, it will just re-grow until it has the space it needs to satisfy the conditions above.
If the log file is constantly too big, you should look for:
Simple Recovery: Look for long-running transactions that could keep the log file from being reused, and try to break them into multiple smaller transactions.
Full Recovery: Schedule your log backups more frequently.
TRUNCATE_ONLY just removes all records not currently in use by a transaction, so that you cannot perform a point-in-time restore, as there will be a gap in the log backups. You then need to do a new Full backup in order to re-start the log chain.
