Link to home
Start Free TrialLog in
Avatar of joe_echavarria
joe_echavarriaFlag for Dominican Republic

asked on

Automate Backups in SQL Express

Hi,

  I need to automate backups for a SQL Express installation.     Some sort of  daily backup maintenance plan.    I been told that it's not as simple as setting up a job using SQL management studio in the full version.

  Please any tips, links, help , tutorial that will orient me on how to this.

  Thanks.
ASKER CERTIFIED SOLUTION
Avatar of YZlat
YZlat
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am using http://expressmaint.codeplex.com/. It is a set of commands that you schedule through Windows Task Scheduler
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think any solution will work but my preference is too save the backup commands to a .sql file and schedule it to run in the task scheduler.
Microsoft even wrote an article on how to do that. If you're interested you have the full process and code here.
Hi,
You can run Stored proc to do backup, using the codes like
DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'C:\Backup\'  

 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

 
       FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

Open in new window


and you can can have scheduled job to call such Stored proc, periodically.
In SQL Management Studio, navigate to the server, then expand the SQL Server Agent item, and finally the Jobs folder to view, edit, add scheduled jobs.
HuaMinChen, the asker is using Express which doesn't come with the scheduler.
Avatar of joe_echavarria

ASKER

Thanks guys for your support.

I have used an Power Shell scripts  to perform the backups.

Thanks.