January 21, 2009 at 7:58 am
Hi guyz! I want to create scripts that can do a backup on a database on regular basis. How can i do that? Please help. The database is in sql server 2005 express edition.
January 21, 2009 at 8:12 am
How about creating a job on SQL agent and schedule it to run on a regular basics
January 21, 2009 at 8:29 am
Thanks Crazy Man, the problem in this case is, i want to do it with express edition. In this case, there is no sql agent. So how do i create the scripts to do the manual back up, then i can get alerts from the agent on when back ups have been done adn when they have not. Please help.
January 21, 2009 at 1:14 pm
You can use the windows scheduler and sqlcmd to do scheduled backups. It's not as easy as SQLAgent, but it does work. For sqlcmd, you'll want to use the -i switch to specify an input file. That query file would then contain all the backup scripts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2009 at 1:47 am
Hi. Thanks for your reply, but please explain to me how the scheduler and sqlcmd work. I have gotten an understanding of how the scheduler works, but in this case i need to have the script execute like at a given time on daily basis. So how do i do it in the scheduler using scheduler?
May 8, 2013 at 1:21 am
Hi,
in your environment having test servers ? then create one linked server for that 2005 server(express edition) and create job for taking backup in your test servers.
note: in backup command you should have mention the path like '\\your 2005express edition location path'
script: create one job by using the below script in your test server and make sure your mentioned path like '\\ D:\ yoursql server2005 express edition\backup\......'
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
subahan munthamadugu
May 8, 2013 at 8:11 am
Please note: 4 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy