Auto Backup with SP not working!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Auto Backup with SP not working!

Author

Topic

android.sm
Starting Member

36 Posts

Posted - 2010-04-26 : 18:49:59

I'm using sample code from the net to create auto backup for my database in express 2008 management studio. I'm also trying to auto schedule using task scheduling in windows 7. I have a folder in C:\Backup with a batch file that contains:
----------
sp_BackupDatabase 'master'
GO
QUIT
----------

My SP code is this:

USE [master]
GO

ALTER PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName sysname
AS
BEGIN

DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)

SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')

SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''C:\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'''

END

When I execute SP: I get this with return value = 0

DECLARE @return_value int

EXEC @return_value = sp_BackupDatabase
@databaseName = master

SELECT 'Return Value' = @return_value
GO

In task schedule I have in 'Action' tab the path to (SQLCMD.EXE) and in 'Add arguments' tab: sqlcmd -S serverName -E -i C:\Backup\sqlBackup.sql

However, I don't see any backups of my master database created in C:\Backup folder when I run scheduler. The net article uses xp scheduler while im using windows 7. Now sure how my batch file will called. Any ideas pleas?

Thanks
PS: article link:http://www.mydigitallife.info/2009/08/26/how-to-perform-scheduled-backups-for-sql-server-2005-express/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 18:52:46

Does it work if you EXEC the stored procedure in a query window?

EXEC dbName.dbo.sp_BackupDatabase 'master'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-26 : 19:09:29

Try this:

SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''C:\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'''

EXEC(@sqlCommand)

END

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 19:17:28

Ah yes, bad code. I didn't even look at the code as I assumed that the OP had downloaded working code.

android.sm, if you are looking for robust backup code, you should check out my custom code instead: http://weblogs.sqlteam.com/tarad/archive/2009/12/29/Backup-SQL-Server-2005-and-2008-DatabasesAgain.aspx

My code is being used around the world (based upon email/feedback I receive). I use it on Enteprise systems small and large.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

android.sm
Starting Member

36 Posts

Posted - 2010-04-27 : 05:24:09

Thanks for getting back guys. Got it to work. The arguments I was supplying in task scheduler was wrong. One slightly problem though. I was testing C:\Backup but I want this path:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
But no backup gets created in this path! What's wrong here?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-27 : 12:10:51

Try out my custom code instead. It doesn't require you to edit the stored procedure code at all, you just pass in the entire path (but not the file name as that's created for you). My code also creates a subdirectory with the database name under the path you input into it, that way you have all of the backups for the different databases in separate folders.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

android.sm
Starting Member

36 Posts

Posted - 2010-04-27 : 13:04:40

you code is huge and i don't know what's going on!

anyway - i've done something, not sure what. but now i can't run from task scheduler! the sqlcommand prompt comes very quick and goes - but it says something about too many arguments or wrng number of arugments supplied. in the task scheduler i am passing this:
sqlcmd -S DMServer -E -i "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Backup.sql"

while my backup.sql file has: sp_BackupDatabase 'SharedServices_DB', 'F'

and my sp is:

use master
GO
ALTER PROCEDURE sp_BackupDatabase
@databaseName sysname
AS
BEGIN
SET NOCOUNT ON;

DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)

SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')

SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'''

EXECUTE sp_executesql @sqlCommand
END

this works fine when i run exec sp_BackupDatabase but from task scheduler. please help me sort this annoying problem out!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-27 : 13:33:59

You don't need to look at the custom code at all. You just need to run the custom script to create the object and then EXEC it.

Here's an example of the EXEC, directly from one of my production jobs:


EXEC isp_Backup 
	@path = 'G:\Backup\', 
	@dbType = 'User',
	@bkpType = 'Full', 
	@retention = 2,
	@bkpSwType = 'NV'

To test out your Task Scheduler issue, put your sqlcmd command into a batch file and then double-click on it. Do you get the error there? If so, please post the exact error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources