Results 1 to 7 of 7

Thread: Backup SQL DB

  1. #1
    Join Date
    Nov 2008
    Posts
    1

    Backup SQL DB

    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

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    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.

  3. #3
    Join Date
    Feb 2009
    Posts
    7
    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

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    OP knows how to set sql job to backup db.

  5. #5
    Join Date
    Feb 2009
    Posts
    7
    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

  6. #6
    Join Date
    Jun 2011
    Posts
    3
    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.

  7. #7
    Join Date
    Oct 2012
    Posts
    1
    You can go two ways:

    - using batch script with scheduling;

    - using software with full automation.

    Batch script:

    Code:
        @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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •