Sunday, March 2, 2008

Scheduling database backup in SQL 2005 Express Edition

I have been recently visited client who uses SQL Server 2005 Express Edition for his business. As you know that edition is supplied without SQL Server Agent (which was in MSDE). So,the question is how to schedule backup of the database in that case? I would like to thank to Jasper Smith, who wrote these greate articles

Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27

Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29


CREATE PROCEDURE usp_BackupDatabases
@dbname SYSNAME,
@backup_path VARCHAR(100)
AS
DECLARE @dt VARCHAR(20) ---date of db backup
DECLARE @FileName VARCHAR(50) ---build path and dbname
DECLARE @sql VARCHAR(100) ---build final sql statement

SET @dt =CONVERT(VARCHAR(15),GETDATE(),112)
SELECT @FileName = @backup_path+@dbname + @dt+'.bak'

SET @sql = N'BACKUP DATABASE [' + @dbname + '] TO DISK = ''' + @FileName + ''''
EXEC(@sql)


The above is simple stored procedure that accepts two parameters and performs BACKUP of the database.Now that, we have created stored procedure lets put the call of the sp into a text file ( I use userdbfullbackup.sql) and place the file on filesystem.
usp_BackupDatabases @dbname='$(DB)',@backup_path='$(BACKUPFOLDER)'


SQLCMD utility 'knows' to accept parameters (-v),so we can easily provide parameters.

--Usage
EXEC master..xp_cmdshell 'SQLCMD -S URID\SQLSERVERDEV2005 -i"c:\userdbfullbackup.sql" -v DB="Northwind" -v BACKUPFOLDER="c:\"'


But wait , till now we just written a stored procedure that backups our databases and tested it , so what is about scheduling?

Windows Servers/XP have Scheduled Tasks Wizard
You can refernce to below KB
http://support.microsoft.com/default.aspx?scid=kb;en-us;308569&sd=tech

Please see Jasper's explanation ( do not want repetition).
http://www.sqldbatips.com/showarticle.asp?ID=27

6 comments:

xiaoqiu said...

Delivering a new dimension for hardcore Maple Story players with the addition of a fourth job and some free maple mesos, this is launched today. Maple Story players will have reached a high enough level to achieve the 4th Job or some necessary maplestory mesos, which begins this week. Many people can cost their own cheap mesos to gain themed dessert and costumes among their many posts. The addition of the 4th Job provides further depth to an already intricate and complex maple story mesos in game. At Leafre, players can find new upgrades for some of the games more powerful weapons can receive from mesos and also party with other elite players.

vedro said...

Hello

I am a student at Faculty of Electrical Engineering and Computer Science in Maribor (Slovenia).
I am studying Automation
I am working on a project based on SQL 2005 Express Edition with SCADA as foreground control system.
I am new at SQL programing so I would ask for your help. I found your blog post for Scheduling database backup in SQL 2005 Express Edition and helped me a little to understand the procedure.
But I am still quite "in the dark" with my issues.
What I have to do is:
I have one database in which are 2 tables: Data and Alarms. Now, another database (backup) has to be created and table Data needs to be copied (data) into it at the first day of the every month.
For example, at May 01, at 0:00 table Data will be copied into the backup database and will be named Data_april_09. So this would be a monthly backup of the table Data.
Table Data from the original database has to be deleted after the backup proces.
The same procedure has to be with the table Alarms but every new year (so, once yearly). So the backuped Alarms database would be called Alarms_09.
Here also table Alarms from the original database has to be deleted after the backup proces.
Now, since those two pocedures will be runned from SCADA (actual schedule will be maintained) I need a script (maybe 2 - one for monthly and one for a yearly backup), that will be runned from it.

I have found SQL commads (not all) that I think are apropriate for my problem, but I do not not know how to put them into one usefull piece of code:

To copy a table from one database to another I need the command:

SELECT * INTO backUP_database_name.dbo.BackUP_table_name FROM Database.dbo.Original_table_name

Then to delete data from the original table when the data is copied to backup:

TRUNCATE table Original_table_name

The backup database has to be created manualy or it can be created sheduled every year?

I would be very grateful if you would help me with my issue. I have found SQL as very usefull tool in my future business and career.

Have a nice day,
Vedran Budinski

products said...

China Wholesale has been described as the world’s factory. This phenomenom is typified by the rise ofbusiness. Incredible range of products available with China Wholesalers “Low Price and High Quality” not only reaches directly to their target clients worldwide but also ensures that wholesale from china from China means margins you cannot find elsewhere and buy products wholesaleChina Wholesale will skyroket your profits.wedding dressescheap naruto cosplayanime cosplay

products said...

Women’s nike tn Shox Rivalry est le modèle féminin le plus tendance de baskets pour le sport. tn chaussuresConcernant la semelle :spyder jacketsCheap Brand Jeans Shop - True Religion Jeans cheap nike shox & Puma Shoes Online- tn nike,Diesel Jeans le caoutchouc extérieur, l’EVA intermédiaire Levis Jeanset le textile intérieur s’associent pour attribuer à la.ed hardy shirts pretty fitCharlestoncheap columbia jackets. turned a pair of double plays to do the trick.Lacoste Polo Shirts, puma basket, Burberry Polo Shirts.wholesale Lacoste polo shirts and cheap polo shirtswith great price.Thank you so much!!cheap polo shirts men'ssweate,gillette mach3 razor bladesfor men.As for Cheap Evisu JeansCheap Armani Jeanspolo shirtsPuma shoes

Replica Watches said...

29047126483369175 I play dofus Replica Watches for one year, I Replica Rolex Watches want to get some Replica Watch kamas to buy Replica Chanel Watches item for my character. So, I search "Replica Swiss Watches" on google and found many website. As Exact Replica Graham Watch the tips from the forum, I just review the Swiss Replica Watches websites and choose some Replica Montblanc Watches quality sites to Replica Cartier Watches compare the price, and go to their Replica Breguet Watches online support to make Replica Breitling Watches the test. And Last Chaos Gold I decide to use Replica BRM Watch at the end. And Tag Heuer Replica Watch that is the Replica IWC Watch beginning..

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!