On 31 Aug 2004 13:53:44 -0700, Jerry Penna <jerrypenna@msn.com> wrote:

> I've corrected the comma, thanks, but now there is a syntax error at
> line 23:
>
> Server: Msg 170, Level 15, State 1, Procedure BACKUP_SP, Line 21
> Line 21: Incorrect syntax near '@DBNAME'.
>
> "Glenn Adams" <glenn@tibercreek.com.nospam> wrote in message
> news:<opsdlnonryfcuvf7@saruman>...
>> The comma at the end of line 4 shouldn't be there...
>>
>>
>> On 30 Aug 2004 13:51:50 -0700, Jerry Penna <jerrypenna@msn.com> wrote:
>>
>> > Thanks Hari ~
>> >
>> > A couple of questions. Can you explain your Prerequisites
>> > 1. & 2. I know little about SQL Server and XP - I'm an Oracle DBA
>> > familiar with UNIX.
>> >
>> > Also, when I ran the script you provided, but I received the followng
>> > error:
>> >
>> > Server: Msg 156, Level 15, State 1, Procedure BACKUP_SP, Line 5
>> > Incorrect syntax near the keyword 'DECLARE'.
>> >
>> > Thanks again.
>> >
>> > "Hari Prasad" <hari_prasad_k@hotmail.com> wrote in message
>> > news:<uSLxD5ajEHA.1040@TK2MSFTNGP09.phx.gbl>...
>> >> Hi,
>> >>
>> >> Provide UNC path along with the BACKUP DATABASE command.
>> >>
>> >> Eg:-
>> >>
>> >> Backup database gb_production TO DISK =
>> >> '\\grab-01\share_name\dbname.bak'
>> >>
>> >> Please go thu the below script will take the UNC path as the
>> parameter
>> >> and
>> >> will Backup Master, MSDB and all the User databases to the remote
>> >> machine.
>> >> This
>> >> script will create the unique Backup files names, this will ensure
>> that
>> >> old
>> >> backup sets were not overwritten.
>> >>
>> >>
>> >> Prerequisites
>> >>
>> >> 1. SQL server and SQL Server Agent should be configured to start in
>> >> Domain
>> >> Account
>> >> 2.. This Domain account should have change privileges to add files
>> to
>> >> the
>> >> Remote machine
>> >>
>> >> Script
>> >>
>> >>
>> >> CREATE PROCEDURE BACKUP_SP @UNCPATH VARCHAR(200) AS
>> >> BEGIN
>> >> SET NOCOUNT ON
>> >> DECLARE @NAME VARCHAR(100),
>> >> DECLARE @DBNAME VARCHAR(100)
>> >> DECLARE BACKUP_CUR CURSOR FOR
>> >> SELECT name FROM master..Sysdatabases where name not in
>> >> ('model','pubs','tempdb','northwind')
>> >>
>> >> OPEN BACKUP_CUR
>> >> FETCH NEXT FROM BACKUP_CUR INTO @DBNAME
>> >>
>> >> WHILE @@FETCH_STATUS=0
>> >>
>> >> BEGIN
>> >>
>> >> SELECT
>> >>
>> >> NAME=@UNCPATH+@DBNAME+'_'+ltrim (rtrim (convert (char,
>> >> getdate(),105)))+'Dump.bak'
>> >> BACKUP DATABASE @DBNAME TO DISK = @NAME WITH INIT ,
>> NOUNLOAD ,
>> >> NAME = @DBNAME, NOSKIP, STATS = 10, NOFORMAT
>> >> FETCH NEXT FROM BACKUP_CUR INTO @DBNAME
>> >>
>> >> END
>> >>
>> >> CLOSE BACKUP_CUR
>> >> DEALLOCATE BACKUP_CUR
>> >> END
>> >>
>> >> How to Execute:
>> >> -----------------
>> >> This procedure will take @UNCPATH as the input parameter, Say you
>> have
>> >> to
>> >> backup the database to machine BACKUPSERVER in to share SQLBACKUP
>> then
>> >> the
>> >> execution will be
>> >>
>> >> EXEC BACKUP_SP '\\BACKUPSERVER\SQLBACKUP\'
>> >>
>> >> This will backup all the databases to the SQLBACKUP folder in
>> >> BACKUPSERVER.
>> >>
>> >>
>> >>
>> >> Thanks
>> >> Hari
>> >> MCDBA
>> >>
>> >>
>> >> "Jerry Penna" <jerrypenna@msn.com> wrote in message
>> >> news:3ac97809.0408271340.382f4981@posting.google.com...
>> >> > Hi all ~
>> >> >
>> >> > I've been able to use the command line listed below to automate a
>> >> > backup. One snag - I'm trying to backup to an alternate server.
>> In
>> >> > my case, my servers are grab-02 (database node) and grab-01
>> (location
>> >> > of desired backup).
>> >> >
>> >> > I do have shared folders for both folders.
>> >> >
>> >> > How can I use this command to backup from grab-02 to grab-01 using
>> the
>> >> > following command?
>> >> > What would be the fully quanlified path for the shared docs folder
>> on
>> >> > grab-01?
>> >> >
>> >> > C:\Documents and Settings\GB>osql -U sa -P password -S grab-02 -Q
>> >> > "BACKUP
>> >> > DATABASE gb_production TO DISK = 'c:\database_backup'"