Backup a Database Using Query

1.Full Backup


declare @Path varchar(500) ,
@DBName varchar(128)

select @DBName = 'msdb'
select @Path = 'c:\backups\'

declare @FileName varchar(4000)
select @FileName = @Path + @DBName + '_Full_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','')
+ '.bak'

backup database @DBName
to disk = @FileName


2. Diff Backup


declare @Path varchar(500) ,
@DBName varchar(128)

select @DBName = 'msdb'
select @Path = 'c:\backups\'

declare @FileName varchar(4000)
select @FileName = @Path + @DBName + '_Diff_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','')
+ '.bak'

backup database @DBName
to disk = @FileName
with differential


3.Log Backup


declare @Path varchar(500) ,
@DBName varchar(128)

select @DBName = 'msdb'
select @Path = 'c:\backups\'

declare @FileName varchar(4000)
select @FileName = @Path + @DBName + '_Log_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','')
+ '.bak'

backup log @DBName
to disk = @FileName

4.Restore Backup

declare @Path varchar(500) ,
@DBName varchar(128)

select @DBName = 'msdb'
select @Path = 'c:\backups\'

declare @FileName varchar(4000)
select @FileName = @Path + @DBName + '_Log_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','')
+ '.bak'


Restore Database @DBName From Disk = @FileName With Replace, Move 'Msdb_Data' To @Path + @DBName + '_Data.MDF', Move 'Msdb4_Log' To @Path + @DBName + '_Log.LDF'