Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Question Unanswered: How to backup/restore entire database in easiest way?

    Hi,

    I'm found this sample for BACKUP DATABASE statement in SQL Server 2000 Book
    Online, but is seemed that only save file to server. How can I save backup
    file to local computer? or there is other way as easy as this statement for
    backup/restore database?

    -- Create a logical backup device for the full MyNwind backup.
    USE master
    EXEC sp_addumpdevice 'disk', 'MyNwind_1',
    DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'

    -- Back up the full MyNwind database.
    BACKUP DATABASE MyNwind TO MyNwind_1

    Thanks,
    Tien,

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    that is saving the backup to a local computer the path is in the
    sp_adddumpdevice code

    you would run the sp_addumpdevice procedure one time to create a backup file that would contain as many backups as neccessary.

    you then run the backup database statement against the device

    what is it that you want to do?

  3. #3
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    Just indicate like this:
    "\\MyComputer\<MyShare>\"
    The user used to do this from ISQLW must have enough permissions on the server and also on your machine.

    Paulo

  4. #4
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    I usually map network disk. For this I change job of Maintenance Plan:
    exec xp_cmdshell 'net use W: \\Backup_Server\DB_Backup'
    EXEC master.dbo.xp_sqlmaint N'-PlanID 1C7C4AA1-A06B-42CF-8985-3C62BB8E31DB -To "mwolf" -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\Backup all job DB on Backup_Serve.txt" -DelTxtRpt 5DAYS -VrfyBackup -BkUpMedia DISK -BkUpDB "W:" -DelBkUps 5DAYS -BkExt "BAK"'
    exec xp_cmdshell 'net use W: /DELETE'

  5. #5
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    Don't use a network drive. Use the path directly in the backup instruction.

    Paulo

Posting Permissions

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