Backup SQL DB

[Mour]

Hello:

I have a brand new SQL Server 2005 with a few 5GB databases. In my main database I want to set up backups at 01:00 every day, a full backup nightly.
When the full backup occurs at night I would like to have it copied and restored to another server which will be far away from the head office on our network
So I’m asking if any one have the knowledge on that or if there is any script for that

Thank you-
Mour

[rmiao]

You can copy file to remote server with another step in sql backup job, as long as sql agent service account has write permission on remote location. Or can set sql job remote server to restore db from prod server without copying file, as long as sql agent service account on remote server has read permission on prod server of course.

[Rajesh GGKTECH]

Try this,
In SQL Server Agent Right Click on Jobs and click on New Job
In General tab enter Name, Owner(Database owner who has admin permissions) and Select Enabled check box
In Steps tab Click on New button and enter step name and select required database
In Command enter:

USE AdventureWorks
GO
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\MSSQL\BACKUP\AdventureWorks.Bak'
WITH FORMAT,
NAME = 'Full Backup of AdventureWorks'

In Schedules tab click new button and enter required details in New job schedule window and click Ok
Click Ok

[rmiao]

OP knows how to set sql job to backup db.

[Rajesh GGKTECH]

hi,
to move file from location to other location in same network

execute this query

DECLARE @DelFile NVARCHAR(1000),
@File NVARCHAR(100)
SET @File = 'Text.bak'
SET @DelFile = 'MOVE ' + 'D:\Users\Rajesh\' + REPLACE(@File, ',','.')+ ' ' + 'D:\Users\Rajesh\Training\'
EXEC master.dbo.xp_cmdshell @DelFile

[AlexGreen]

With this tool http://sqlbackupandftp.com you can schedule full, differential and transaction logs backups and send them to a remote site on your LAN or an FTP repository.

Compression and encryption are also available if you need it.

[Jackie]

You can go two ways:

- using batch script with scheduling;

- using software with full automation.

Batch script:

    @ECHO OFF
    SETLOCAL

    REM Get date in format YYYY-MM-DD (assumes the locale is the United States)
    FOR /F “tokens=1,2,3,4 delims=/ ” %%A IN (‘Date /T’) DO SET NowDate=%%D-%%B-%%C

    REM Build a list of databases to backup
    SET DBList=%SystemDrive%SQLDBList.txt
    SqlCmd -E -S YourSQLServer -h-1 -W -Q “SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN (‘master’,'model’,'msdb’,'tempdb’)” > “%DBList%”

    REM Backup each database, prepending the date to the filename
    FOR /F “tokens=*” %%I IN (%DBList%) DO (
    ECHO Backing up database: %%I
    SqlCmd -E -S YourSQLServer -Q “BACKUP DATABASE [%%I] TO Disk=’ftp://192.168.1.1:Backup%NowDate%_%%I.bak’”
    ECHO.
    )

    REM Clean up the temp file
    IF EXIST “%DBList%” DEL /F /Q “%DBList%”

    ENDLOCAL

Then schedule this script and add net use command if it is needed.

Another way is to use ready-to-go solution like backup software. I prefer the cheapest one like Handy Backup, but you may want to use something like Norton or Acronis.