1. Add Backup Device
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
USE master
go
EXEC sp_addumpdevice
@devtype = 'Disk',
@logicalname = 'DB_Simulasi',
@physicalname = 'D:\Backup\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
--------------------------------------------------------------------------------------------------------------
DB_Simulasi 0 0 0 16 2 D:\Backup\DB_Simulasi.bak
(1 row(s) affected)
name type type_desc physical_name
-----------------------------------------------------------------------------------------
DB_Simulasi 2 DISK D:\Backup\DB_Simulasi.bak
(1 row(s) affected)
3. Test for backup database using backup device on disk
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
BACKUP DATABASE DB_Simulasi TO DB_Simulasi
-----------------------------------------------------------------------
-----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 12.612 seconds (41.399 MB/sec).
4. Optional: To remove backup device
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
EXEC sp_dropdevice 'DB_Simulasi'
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------
Result Messages:
Device dropped.
5. 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