February 27, 2009 at 6:52 am
I have a backup script that I would like to schedule to run nightly. However, I don't know how to setup the run line within a scheduled task to execute this backup statement using sqlcmd. This is an Express DB so I can't use SQL Agent.
Here is the backup script I am using
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
SET @path = 'E:\SQL_BACKUPS\'
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')
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
Thanks.
February 27, 2009 at 11:26 am
February 27, 2009 at 12:57 pm
Keep in mind you also need to backup master and msdb !
just put the script in a file e.g. fullsystembackup.sql
Then create a SQLExpress_fullsystembackup.cmd file containing :
cd /d yourdrive:\thescriptpath
sqlcmd -S ComputerName\InstanceName -i fullsystembackup.sql -o fullsystembackupOutput.rpt
If you have a default sqlexpress installation, your instancename will be ComputerName\SQLExpress.
Check out "sqlcmd Utility" in books online !
Then schedule that SQLExpress_fullsystembackup.cmd in windows scheduled tasks.
Make sure the windows account you use to run the job, actually has the
needed sqlserver authority to run the backup statements and that your
sqlserver service account is able to write to the backup file you specify.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 4, 2009 at 7:50 am
You can also try to adjust this example:
http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express
It's based on service broker...
March 4, 2009 at 8:12 am
This one ended up helping the most.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply