SQL Server 2005 Backup

[vbgladiator]

Hey,

I have an application in .NET 2005 which uses SQL Server 2005 express as the backend. I want to be able to allow the user to click a button and make a copy of the 2 database files and move them to a specific folder without having the user to stop the sql server services. Does anyone know how I can accomplish that?

Thanks,

[szlamany]

Look at this thread - it shows how to do a BACKUP in a STORED PROCEDURE.

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

Search the FORUM for BACKUP FUNDS - you will find many other threads - if you want to look around...

[vbgladiator]

thanks a lot.

[szlamany]

btw - just to elaborate

using BACKUP - which makes a hot-copy of the data and log - without getting users out is a good thing.

The .BAK file is much smaller then the data and log files - you can direct it to backup to anywhere (but it is local drives on the SQL box - do not do a backup to a network drive or share).

[vbgladiator]

ohh, so it doesn't just make a copy of the 2 files? It creates a bak file?

[szlamany]

Why do you want to make a copy of the data and log files?

[vbgladiator]

I thought that is what should happen. How would they restore the bak file?
I was under the impression that both files would be copied and then the user can back them up using whatever media they have available to them. I just want to protect myself and allow them to make a copy of the data files.

Thanks,

[szlamany]

Nope - the .BAK file is the way to go.

RESTORE will put it back into SQL if needed. Search the forum for BACKUP FUNDS and you will find RESTORE examples.

We often use BACKUP to make a copy of a customer DB...

Bring it to our office - and RESTORE to our servers here. You can even RESTORE to a new DB name - so we can have several copies of the same DB on the server.

Getting the data and log requires a detach of the DB - so that no users are in it. BACKUP is a hot-copy method - respecting uncommited transactions and all that jazz.