Avatar of joe_echavarria
joe_echavarria
Flag 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.
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005Storage Software

Avatar of undefined
Last Comment
joe_echavarria

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
YZlat

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Éric Moreau

I am using http://expressmaint.codeplex.com/. It is a set of commands that you schedule through Windows Task Scheduler
SOLUTION
YZlat

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

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.
Peter Chan

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Peter Chan

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.
Éric Moreau

HuaMinChen, the asker is using Express which doesn't come with the scheduler.
joe_echavarria

ASKER
Thanks guys for your support.

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

Thanks.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.