StoredProc to Back-up SQL Server

[dee-u]

Using ado I use this:

strExecute = "BACKUP DATABASE ['database name"] "
    strExecute = strExecute & "TO DISK = 'path" ' "
    strExecute = strExecute & "WITH INIT" ', STATS "
    Dim AdoCmd As New ADODB.Command
    With AdoCmd
        .ActiveConnection = 'active connection
        .CommandType = adCmdText
        .CommandTimeout = 0
        .CommandText = "use master"
        .Execute
        .CommandText = strExecute
        .Execute
        Set .ActiveConnection = Nothing
    End With
    Set AdoCmd = Nothing

Can it be turned to T-SQL so I could just execute it asynchronously and dont wait till it is finished backing-up the sql server database?

[RhinoBull]

Regardless of any solution you may get - this is not the way databases such SQL Server are backed up. Use SQL Server's backup functionality if you want to prevent your business from having problems in the future..

[szlamany]

dee-u - RhinoBull is very correct in the statement he made.

MS SQL Server has maintenance plan features that you should take advantage of - such as full backups nightly, transaction log backups every hour (or whatever you business requirements may be).

These features, available in Enterprise manager, are reasonably robust - although I'm sure they are being enhanced in SQL 2005...

[dee-u]

RhinoBull: What should I use then, the SQLDmo? I want to back-up the server through code so as not to burden the user of this task.....

szlamany: Is SQL Server capable of knowing when no user is already connected to it? Then back-up the database if it determines that no user is already connected? I could time it but maybe at the specified time there are still users who are adding to the database so the back-up is not that updated.....

For stored proc backing-up I found this....

-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1', 
   DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1

Do you think the above code is a better option to back-up sql server than my previous method? By the way, why is it that you are against my method of backing-up the sql server database? What are its weaknesses?

[szlamany]

dee-u - SQL Server does not need a "user free" environment for a backup to be successful.

The way that SQL uses transaction file "write-ahead" logic means that a backup can occur with hundreds of users connected and be a perfectly good backup.

The transaction log design of the system allows for point-in-time recovery to any minute you want.

I believe that when the backup starts the transactions are held in the log, until the backup of the "disk data" is complete. With this you get a perfect image of data on disk and then the log is also included in the backup so that all "open" transactions are also in the backup.

I've got one customer that does two data backups a day - one at 2:00 AM and one at 2:00 PM. Every hour a transaction log backup is taken as well - all day long.

To summarize, SQL Server does not need a user-free moment for backup.

[dee-u]

You got me wrong, I want to perform the back-up procedure when I'm sure no user is already connected to the sql server so the back-up would be up-to-date...... Anyway to do this automatically in sql server?

And what can you say about the my posted code in my previous post? Will you discourage me from using it or its better than my first code?

[szlamany]

You got me wrong, I want to perform the back-up procedure when I'm sure no user is already connected to the sql server so the back-up would be up-to-date

This is not needed in MS SQL Server - backups are not up-to-date because a user is not connected.

SQL is much stronger than that - if you came from ACCESS you will have to forget what you did in that realm and realize how strong a DB SQL SERVER is...

[dee-u]

I'm getting confused.....

Here is the scenario:

At the end of the day I want to perform a back-up of a certain database in sql server, I want to do it when no user is already using the app that accesses that database to be 'sure' that there wouldnt be new insertions in that database after the back-up procedure is done.....

Maybe I'm missing something else, if yes pls. guide me to the correct path....

[szlamany]

I'm getting confused.....

Here is the scenario:

At the end of the day I want to perform a back-up of a certain database in sql server, I want to do it when no user is already using the app that accesses that database to be 'sure' that there wouldnt be new insertions in that database after the back-up procedure is done.....

Maybe I'm missing something else, if yes pls. guide me to the correct path....

That sounds like you are saying you want to make sure no one inserts records after the backup is done.

Isn't that based on when people leave the system for the day? Isn't that the same time - let's say 8:00 PM - every night - that you can safely say no one will be adding more rows?

Rows inserted after a backup become part of the next days backup - right?

Rows inserted during a backup are in the transaction log - get committed to the database after the backup and become part of the next days backup - this is good.

Give more details of your "real-life" time schedule - please...

[dee-u]

You got me right but the problem is the indefinite time of when the users closes the system so I can not schedule a specific time.... Only when they are closing the system that I am sure they are already leaving.....

I'm thinking if there is a way that when the system is closing it would trigger a back-up schedule/job in the sql server and that's why I have thought of stored procedure but there might be a better way.....

[szlamany]

You got me right but the problem is the indefinite time of when the users closes the system so I can not schedule a specific time.... Only when they are closing the system that I am sure they are already leaving.....

I'm thinking if there is a way that when the system is closing it would trigger a back-up schedule/job in the sql server and that's why I have thought of stored procedure but there might be a better way.....

Look into SQLDMO - I've not used it, but I believe it will allow you to talk to the SERVER agent and schedule a job to run immediately. That job being the backup task...

But, if you are truly tied to this concept, you can use a SPROC to run a BACKUP - if a "timestamped-via-user-activity" backup is that important to you.

We produce about 1000 health claim checks at one of our customer sites every day - at around 1:30 PM. They have a maintenance plan scheduled backup run at 2:30 everyday to "incorporate" that activity.

[dee-u]

So will this sample code fair enough as compared to my previous code?

-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1', 
   DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1

[szlamany]

Here's a backup post I put up a month or so ago with the syntax we use.

We do not do this in a SPROC - we do this in query analyzer...

http://www.vbforums.com/showthread.p...t=backup+funds

[dee-u]

Isnt that code very similar to the code I posted in my first post? Is the path could be passed as a parameter to that stored proc?

[szlamany]

Isnt that code very similar to the code I posted in my first post? Is the path could be passed as a parameter to that stored proc?

First - USE is not needed, unless you are trying to "not be connected" to the database you are backing up. But you must realize that you being connected is not an issue.

And yes you can pass the "path" as a parameter to the SPROC. I believe I've been in threads here that talked about that method.

I'm pretty sure you can even parameterize the DB NAME - even though it's not in quotes!

Declare @DBName varchar(50)
Declare @Path varchar(100)

Set @DBName = 'SomeDB'
Set @Path = 'd:\asdf\asdf\'

Backup @DBName to @Path

This should work - although I do not have SQL on me right now to test it with!

[szlamany]

Here's another thread from a while back that has more examples of backup action like you are trying to do...

http://www.vbforums.com/showthread.php?t=327401

[dee-u]

Thanks, I made it work already.... Now problem is, I cannot seem to execute the Stored Procedure using a Command object (asynchronously), any idea why?

Hmmmmnnn..... Perhaps this causes the error? While asynchrounously executing the stored procedure I closed the connection.... I just thought it this time while I'm typing! (It's not the exact code but similar one....

adoCommand.ActiveConnection=adoConnection
adoCommand.Execute , , adAsynExecute
adoConnection.Close

This may really be a problem since I would like to execute the back-up procedure when my app is exiting, and when my app is exiting I tend to close the connections I have and set them to nothing.... I'll try to figure this out, perhaps some experts would help me.....