none
(Sumber: milis SQL Server) Transact SQL Backup Database RRS feed

  • Pertanyaan

  • Dear Master SQL,

    Mohon bantuannya untuk backup database SQL di network drive atau berbeda PC.

    Saya sudah mencoba mapping drive di PC Server. Namun begitu saya akan backup melalui backup wizard dan juga script SQL. Error yang saya terima "Cannot open backup device".

    Script SQL yangs saya gunakan seperti ini :

    BACKUP DATABASE A

    TO DISK = N' \\IP Server\D$\Folder B

    GO

    Mohon solusinya dari para master semua.


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 Juli 2012 07.20
    Moderator

Jawaban

  • Xp_cmdshell tidak disarankan untuk dibuat enable, karena dikhawatirkan dipakai untuk keperluan attack ke mesin database, tetapi kalo memang sudah yakin aman, boleh saja diapakai.
    Untuk kasus backup via shared folder, seperti di email sy sebelumnya, pastikan account startup sql agentnya bukan menggunakan local system account, karena account ini yang dipakai untuk connect ke shared foldernya. Kalo melihat kasus Pak Afri, sepertinya account untuk startup sql agent nya masih menggunakan local admin :)

    Dijawab oleh: Deni


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 Juli 2012 07.24
    Moderator

Semua Balasan

  • Sepertinya scriptnya kurang, tidak ada nama file backupnya :)
    Mungkin seharusnya seperti ini kali ya
    BACKUP DATABASE A TO DISK = '\\NamaServerB\D$\FolderBackup\A.BAK'

    Dijawab oleh: Deni


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 Juli 2012 07.21
    Moderator
  • Dear Mas Deni,

    Dari script SQL yang saya dapat dari mas deni, masih belum berhasil. Error yang saya terima seperti ini :

    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device '\\afri-ptas\D$\ASICS_BackupTest.bak'. Device error or device off-line. See the SQL Server error log for more details.
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    Mohon solusinya dari master semua .


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 Juli 2012 07.22
    Moderator
  • Kalau dari pengalaman saya, saya lebih prefer mapping ke drive dulu.

    1. Pastikan XP_CMDSHELL Enable
    EXEC sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    EXEC sp_configure 'xp_cmdshell', 1
    GO
    RECONFIGURE
    GO

    2. Mounting Share Netwrok lewat Query Windows Via SSMS
    -- exec xp_cmdshell 'net use z: \\10.61.5.99\backup password /user:domain\domain_name /persistent:yes'
    pastikan user domain tersebut mempunyai hak write di share newtwork.
    3. BACKUP DATABASE [Contoh] TO  DISK = N'Z:\Contoh.bak'  NAME = N'Contoh-Full Database Backup'


    Dijawab oleh: Ewin


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 Juli 2012 07.23
    Moderator
  • Xp_cmdshell tidak disarankan untuk dibuat enable, karena dikhawatirkan dipakai untuk keperluan attack ke mesin database, tetapi kalo memang sudah yakin aman, boleh saja diapakai.
    Untuk kasus backup via shared folder, seperti di email sy sebelumnya, pastikan account startup sql agentnya bukan menggunakan local system account, karena account ini yang dipakai untuk connect ke shared foldernya. Kalo melihat kasus Pak Afri, sepertinya account untuk startup sql agent nya masih menggunakan local admin :)

    Dijawab oleh: Deni


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 Juli 2012 07.24
    Moderator
  • Dear Mas Ewin,

    sudah saya lakukan seperti kata mas ewin, tapi masih belum berhasil.

    Saya mendapat error seperti ini :

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
    Msg 5808, Level 16, State 1, Line 2
    Ad hoc updates to system catalogs not recommended. Use the RECONFIGURE WITH OVERRIDE statement to force this configuration.
    Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
    The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.
     
    Valid configuration options are:
    Msg 5808, Level 16, State 1, Line 2
    Ad hoc updates to system catalogs not recommended. Use the RECONFIGURE WITH OVERRIDE statement to force this configuration.

    Mohon solusinya.


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 Juli 2012 07.24
    Moderator
  • Kayaknya allow updatenya masih di set 0.
    sebelumnya boleh dicoba tambahin

    SP_CONFIGURE 'ALLOW UPDATES', 1
    
    GO
    

    RECONFIGURE WITH OVERRIDE

    GO


    Tapi kalau sudah selesai dibalikin lagi pak :D
    seperti kata pak Deni, XP_cmdshell termasuk dalam salah satu kategori hardening yang harus di disable.

    Dijawab oleh: Ewin


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 26 Juli 2012 07.27
    Moderator