Is Online Full Backup possible?

[TroyMcClure]

Hi,
I tried to setup a full backup and it keeps failing, I think because it needs to be offline. So, 2 questions:

1. Is there a way to automatically take it offline via the scheduler, then do the full backup, then go back online? I would have thought that would be built into the scheduler if it's mandatory to be offline--or *at least* warn me that it can't backup unless it's offline.

2.Is online backup possible? If so, how do I set that up?

--james

 

[JayKusch]

Backups can be, and in most cases are, done with activity on the database occuring. What are the error messages you are receiving when your backup fails.

Thanks

J. Kusch

 

[TroyMcClure]

I was getting 'File in Use' when I tried to backup via Windows Task Scheduler. So I went via EM's backup, and I get the following in the Event Log:

3041 :
BACKUP failed to complete the command BACKUP DATABASE [YAP] TO DISK = N'C:\Backup_YAP\Yap_Full_Backup' WITH INIT , NOUNLOAD , NAME = N'YAP backup', NOSKIP , STATS = 10, NOFORMAT , MEDIANAME = N'Kange'

There is plenty of space on the disk and there is nothing else running on the machine. It's Win2k Adv. Server, Sql Server 2000.
Thanks for any info,
--james

 

[JayKusch]

Try it w/out the media name ...

BACKUP failed to complete the command BACKUP DATABASE [YAP] TO DISK = N'C:\Backup_YAP\Yap_Full_Backup' WITH INIT , NOUNLOAD , NAME = N'YAP backup', NOSKIP , STATS = 10, NOFORMAT



Thanks

J. Kusch

 

[TroyMcClure]

Jay,
I think I found what it was...the physical file existed already. But here again--isn't there an option to Append/Overwrite?? In MS's normal Backup utility, there is, and I'd swear I came across that option in sql server's backup. I don't have access to EM from here now so I can't check, but to me, that seems like a pretty basic option that one should have, seeing that it's a 'automatic' backup and all.

So I think that problem is solved, but I have another related one: To get around that issue, I tried to edit the statement (Which claims to be TSQL) and Convert()/concat a formatted GetDate() as part of the filename, so it would have a different name every time. It said the Convert was not valid. The identical Convert() syntax was used in a test stored proc. and it worked fine. Why won't this supposed tsql script let me use that function? Thanks,
--james

 

[JayKusch]

Lets see the code

Thanks

J. Kusch

 

[TroyMcClure]

The below gives syntax errors a '+'.
If I remove the whole convert() thing it of course runs fine.

I lifted the line from 'c:\backup_auto....bkf' and made a stored proc. and assigned that to variable and it worked fine.

BACKUP DATABASE [SalesInf] TO DISK = N'C:\Backup_Auto\SQL_SalesInf_' + Convert(varchar(12),getdate(),110) + '.bkp' WITH NOINIT , NOUNLOAD , NAME = N'SalesInf backup', NOSKIP , STATS = 10, NOFORMAT

Thanks for any help,
james

 

[JayKusch]

Try this ...

declare @SQL_Command VarChar(400)

SET @SQL_Command = 

'BACKUP DATABASE [SalesInf] TO  DISK = N' + '''' + 'C:\Backup_Auto\SQL_SalesInf_' + Convert(varchar(12),getdate(),110) + '''' + '.bkp' + '''' + ' WITH  NOINIT ,  NOUNLOAD ,  NAME = N + '''' + 'SalesInf backup' + '''' + ',  NOSKIP ,  STATS = 10,  NOFORMAT'

Print @SQL_Command

EXEC (@SQL_Command)

Check the print statement out if the query fails. I tried to get it as close as I could w/out the use of Query Analyzer to view the created string.

Hope this helps!

Thanks

J. Kusch