Cari Blog Ini

23 Juli 2012

Example of Creating Backup Device for Network Path

1. Add Backup Device for Network Path
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
USE master
go

EXEC sp_addumpdevice  
         @devtype = 'Disk',
         @logicalname =  'NetworkDevice',
         @physicalname = '\\192.xxx.x.xxx\Dari DBBackup\DB_Simulasi.bak'
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
Command(s) completed successfully.


2. Check Availability of backup device
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
select * from sys.sysdevices
select * from sys.backup_devices
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------



Result Messages:
name          size        low         high        status     cntrltype     phyname
--------------------------------------------------------------------------------------------------------------
NetworkDevice    0               0               0               16         2             \\192.xxx.x.xxx\Dari DBBackup\DB_Simulasi.bak

(1 row(s) affected)


name          type    type_desc    physical_name       
-----------------------------------------------------------------------------------------
NetworkDevice    2        DISK                        \\192.xxx.x.xxx\Dari DBBackup\DB_Simulasi.bak

(1 row(s) affected)


3. Test for backup database using backup device on disk

-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
    BACKUP DATABASE DB_Simulasi TO NetworkDevice
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
Processed 66832 pages for database 'DB_Simulasi', file 'SeaSQL_dat' on file 1.
Processed 1 pages for database 'DB_Simulasi', file 'SeaSQL_log' on file 1.
BACKUP DATABASE successfully processed 66833 pages in 52.724 seconds (9.903 MB/sec).


4. Check on Network Path for backup database

5. Optional: To remove backup device
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
    EXEC sp_dropdevice 'NetworkDevice'
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
Device dropped.


6. Check for backup device is already remove
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
select * from sys.sysdevices
select * from sys.backup_devices
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------


name          size        low         high        status     cntrltype         phyname
-------------------------------------------------------------------------------------------------------
(0 row(s) affected)


name          type    type_desc    physical_name       
-----------------------------------------------------------------------------------------
(0 row(s) affected)


Source:
http://syntaxhelp.com/SQLServer/Backup/FULL