Re: backing up SQL server

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 03/16/04 

Date: Tue, 16 Mar 2004 11:41:09 +0530

Hi,

Please go thu the 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

"Mike" <mhebner@getnet.net> wrote in message
news:9CB58890-10EF-477E-84E1-C953D7EE6534@microsoft.com...
> Hi, I'm new to SQL server and hope someone can help me. I have SQL 7 and
am wanting to back it up remotely. I'm currently using a batch script that
shuts down the service and then uses an xcopy command to copy the SQL
directory to a temporary location. The script then uses a net start command
to restart the services on a schedule. I then backup the temporary files
that the xcopy command created. My question is, in the event of a System
Crash, can I reinstall and configure SQL server and then restore the
temporary files and have them work? If not, is there another way to
succesfully back up SQL Server? Any help is very much appreciated. Thanks
in advance.

Relevant Pages

  • Feedback on backup script
    ... The one problem I have is that the script keeps stopping at lines 633,635 and or 637 with the error "Path Not Found" I have emailed the original author for help but haven't heard back yet. ... I was just wondering if the problem could be that the account running the script doesn't have correct permissions to a share/directory/file that it's trying to backup? ... ' always one less than the actual number of folders. ... ' Show the user free space info on all available disk drives ...
    (microsoft.public.scripting.vbscript)
  • Re: Script to stop a SQL service
    ... > Would the syntax be the same if I ran the command line script from within ... > I'm planning on running this batch file from within the BackUp Exec ... backup SQL Server databases online. ...
    (comp.databases.ms-sqlserver)
  • Re: Enterprise Manager Newbie Question
    ... a SQL backup is not a simple copy of the database files. ... Is it possible to write a script that one could run from a workstation and ...
    (microsoft.public.sqlserver.tools)
  • Re: xcopy and vbscript
    ... It takes about 5-6 minutes to backup approximately ... A copy of the script is below for reference. ... ObjShell.Popup strMessage, 5, "Backup Complete", OK_Button ... Dim sSrc, sDest ' as string ...
    (microsoft.public.scripting.vbscript)
  • Re: Backup into another machine.
    ... but there are Few Pre requisites to do backup ... You Should start SQL server using Domain user who got access to remote ... > Backup to remote machine will not work if you start SQL server using Local ...
    (microsoft.public.sqlserver.clients)