backing up SQL server

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 sh
uts down the service and then uses an xcopy command to copy the SQL director
y to a temporary location.
The script then uses a net start command to restart the services on a schedu
le. I then backup the temporary files that the xcopy command created. My q
uestion is, in the event of a System Crash, can I reinstall and configure SQ
L server and then restore
the temporary files and have them work? If not, is there another way to suc
cesfully back up SQL Server? Any help is very much appreciated. Thanks in
advance.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.|||"Mike" <mhebner@.getnet.net> wrote in message
news:9CB58890-10EF-477E-84E1-C953D7EE6534@.microsoft.com...
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?
You should try out restoring the files to a different server and see if it
works. There is no substitute to this. It is always better if you have
carried out some test restores to confirm that your process will work, even
if somebody else says that it will, as it gives you some experience for when
you might have to do it for real.