Automated Backup to Remote Server

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'"
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.c om...
> 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'"
|||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>...[vbcol=seagreen]
> 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.c om...
|||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:
[vbcol=seagreen]
> 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>...
Glenn Adams
Tiber Creek Consulting
http://www.tibercreek.com
glenn@.tibercreek.com
Please DO NOT respond to me directly but post all responses here in the
newsgroup so that all can share the information
|||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>...[vbcol=seagreen]
> 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:
|||"NAME=@.UNCPATH+@.DBNAME"
should be
"@.NAME=@.UNCPATH+@.DBNAME"
On 31 Aug 2004 13:53:44 -0700, Jerry Penna <jerrypenna@.msn.com> wrote:
[vbcol=seagreen]
> 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>...
Glenn Adams
Tiber Creek Consulting
http://www.tibercreek.com
glenn@.tibercreek.com
Please DO NOT respond to me directly but post all responses here in the
newsgroup so that all can share the information