SQL Script for take backup of Database in sql server



In sql server there are 2 built-in stored procedures for drop the already existing backup device and create the new device in the user defined path.
Before create the backup device, must drop the device. Because when you create a backup device, if backup device had already been created, sql server throw a error. So very first time have to create a backup device manually.
Afterwards you can use this script.
This is very use ful as user can take backup where user wants it since this procedure takes the path as parameter.


Create Backup device manually in Sql Server 2008

Go to Server Object where right click on Backup Device, Then choose New Backup Device. if you choose that, sql server let you to create the New Backup Device.
(Please refer the figures as shown below)

Figure 1
Figure 2Figure 3


Figure 4
Figure 5


Script for Drop the Backup Device

EXEC sp_dropdevice 'Time_Attendance'

Script for Create the Backup Device

EXEC sp_addumpdevice 'disk', 'Time_Attendance', @Path


Script for initializes the backup with user specified name

BACKUP DATABASE Time_Attendance
TO Time_Attendance with name= @Name, INIT

Comments

Popular posts from this blog

SQL SERVER – TRIM() Function – UDF TRIM()