question

jctronicsshiva avatar image
jctronicsshiva asked

Database backup,archive

I am implementing Database backup technique. Due to budget constraints i will be using SQL express as the DB. So i would like to a take periodic DB backup(say once in a month).Since SQL jobs are not provided with express edition, i think i am left with 2 options 1. SMO 2. Stored proc Can you suggest me when should i use SMO and when should i use Stored proc. Do you suggest any other mechanism? P.S: i completely understand that there is a limited space in DB with express edition. Also it doesnt have the advanced performance improvement techniques!
sql-server-2008stored-proceduresdatabasebackupsmo
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
As long as you're using some mechanism to issue the command BACKUP DATABASE, how it gets called doesn't really matter. You can create a stored procedure that runs the backup command. That will work just fine called from your code or from a scheduling application. You can use SMO through the Backup class. It's just a different way to issue the same command. The code is different, but the actions within SQL Server are the same. You can also use PowerShell to issue a backup to the database. They're all the same command. Just different resources. If you want to schedule and set it up for manual actions, I'd suggest creating a procedure within the database. This can then be called in different ways, but using the same code.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Grant for the solution.
0 Likes 0 ·
Not a problem. Remember, mark all useful answers by clicking on the thumbs up and mark the answer that best solved the problem by clicking on the check box next to it.
0 Likes 0 ·
raadee avatar image
raadee answered
So you just want to take a backup of all your databases. Use task scheduler on the server to create a task that executes sqlcmd which fires off a backup script. Old thread, same problem: http://www.sqlservercentral.com/Forums/Topic1014439-24-1.aspx Have a look at for step by step: http://www.sqldbatips.com/showarticle.asp?ID=27 SMO and Powershell: http://www.sqlmusings.com/2009/05/31/how-to-backup-sql-server-databases-using-smo-and-powershell/
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the reply. But out of the three which one to choose ? i mean i have a UI tool which uses the DB. I want to provide backup button in that too so that user can also take backup whenever its required(along with scheduled backup strategy). So which of the mechanism do i need to use. When should i use SMO's?
0 Likes 0 ·
Oh I see, you want to integrate it in your app. Did not get that at first. Are you using a central database or does the client use sql express locally on their client? If you have a server then do the scheduled task backup in sqldbatips link. Use a task and let it run daily. Regarding your in app integration, I am not the best person to suggest an solution.
0 Likes 0 ·
yeah .. i want to integrate with the App too. Its basically a server running in a PC which connects to the DB. All clients(in the same LAN) connect to the server..
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.