Tags

,


Create a Store Procedure:

Use Pubs

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Backup_Database]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[Backup_Database]
GO

/*  
Procedure Name : Backup_Database 
Parameters     : 1) @dbName varchar(100) Database name for which the backup has to be done.  
        : 2) @Path Path where the file database backup has to be stored.  
 
Functonality   : Take the backup of the specified database at the specified location. 
 
*/ 
 
Create  Procedure Backup_Database  

@dbName varchar(100) = ”, 
@Path   varchar(200) = ” OUTPUT 
)   
As  
Begin 
Declare @Now varchar(100),
@DefaultPath Varchar(100),
@Qry Varchar(100)
   
— if no database name is specified then take the backup of the current database
if Isnull(@dbName,”) = ”  
Select @dbName = db_Name()
Else If Not Exists (Select * From Master..Sysdatabases Where [name] = @dbName)
Begin
RaisError(‘Could Not located Specified Database in Sysdatabases, Please Check the Database Name!!’,16,1)  
  return  
End  
— if the path is null or blank then create the dir called backups where the mdf files exists and
— make the backup over there :-)
if isnull(@path,”) = ”   
Begin 
   Select @DefaultPath = Left(FileName,len(FileName) – CharIndex(‘\’,reverse(FileName))) + ‘\Backups’
From Master..SysDatabases Where [Name] = @dbName
Set @Qry = ‘mkdir “‘ + @DefaultPath + ‘”‘
Exec master..xp_cmdshell @Qry ,no_output
set @path = @DefaultPath
End  
— Make the filename  
  SELECT @Now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), ‘-‘, ”), ‘ ‘, ”), ‘:’, ”) 
  Select @path =  + @Path + ‘\’ + @dbName + ‘_’ + @Now + ‘.Bak’
  print @path
— Take the backup of the database at the specified folder  
Backup Database @dbName  
To Disk = @Path 
With Init  
if @@Error <> 0   
Begin 
         RaisError(‘Error Occurred while taking the Backup, Please check Error Log for Details’,16,1)  
         return   
End  
 
Print ‘Backup Taken Successfully at ‘ + @path 
End  
 
GO
Backup_database “Pubs”,”\\YourComputer\Shared”

Advertisements