How can we make Database backup and restore script

Hello ,
can anybody tell me how can we make DATABASE BACKUP AND RESTORE SCRIPT through t-sql .And thn i like to make a schedular that everymonth database backup and restore query run automaticaaly according to schedular.
regards
jagpal singh

Below link will help you to find out the T-SQL commands.
http://msdn2.microsoft.com/en-us/library/ms191304.aspx
Additionally I would like to suggest you few ways of achieving the same thing.
You have an easier way of scheduling your backups in SQL Server. If your using SQL Server 2000, use the maintenance plan wizard in Enterprise Manager tool to configure this.
If you’re using SQL Server 2005, same maintenance plan wizard will be available under Management folder of the SQL Server Management Studio tool.
Next option would be to use a third party backup tool (e.g. Redgate, Idera) depending on your requirement to achieve this task.

Hello,
thaxx for reply but i dont want to go for management studio . i know that way but actually i want to create a script for database backup and thn for restoring and thn i want to delete database bakup which is dont successfully .

Here is a script to create a restore script:-- Written by Gregory A. Larsen
-- Declare variables used in SPdeclare
@cmd nvarchar (1000) declare
@cmd1 nvarchar (1000) declare
@db nvarchar(128)declare
@filename nvarchar(128)declare @cnt int
declare
@num_processed intdeclare
@name nvarchar(128) declare
@physical_device_name nvarchar(128) declare @backup_start_date datetime
declare
@type char(1) -- Turn off the row number messageset
nocount on-- Define cursor to hold all the different databases for the restore script will be builtdeclare
db cursor for select
name from master..sysdatabaseswhere
name not in ('Northwind', 'pubs', 'tempdb') and name not like 'temp%'-- Create a global temporary table that will hold the name of the backup, the database name, and the type of database backup.create
table ##backupnames (name nvarchar(100), database_name
nvarchar(100), type
char(1) )-- Open cursor containing list of database names.open
dbfetch next from db into @db -- Process until no more databases are leftWHILE
@@FETCH_STATUS = 0BEGIN-- initialize the physical device name
set @physical_device_name = ''-- get the name of the last full database backupselect @physical_device_name = physical_device_name , @backup_start_date = backup_start_date
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_idjoin msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where @db = database_name and type='d' and backup_start_date =(select top 1 backup_start_date from msdb..backupset
where @db = database_name and type = 'd'
order by backup_start_date desc) -- Did a full database backup name get foundif
@physical_device_name <> '' begin-- Build command to place a record in table that holds backup namesselect @cmd = 'insert into ##backupnames values (' + char(39) + @physical_device_name
+ char(39) + ',' + char(39) + @db + char(39) + ',' +
char(39) + 'd' + char(39)+ ')' -- Execute command to place a record in table that holds backup names
exec sp_executesql @cmdend-- Reset the physical device nameset
@physical_device_name = ''-- Find the last differential database backupselect @physical_device_name = physical_device_name, @backup_start_date = backup_start_date
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_idjoin msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where type='i' and backup_start_date = (select top 1 backup_start_date from msdb..backupset
where @db = database_name and type = 'I' and backup_start_date > @backup_start_date
order by backup_start_date desc) -- Did a differential backup name get foundif
@physical_device_name <> ''begin-- Build command to place a record in table that holds backup namesselect @cmd = 'insert into ##backupnames values (' + char(39) + @physical_device_name
+ char(39) + ',' + char(39) + @db + char(39) + ',' +
char(39) + 'i' + char(39)+ ')' -- Execute command to place a record in table that holds backup names
exec sp_executesql @cmdend-- Build command to place records in table to hold backup names for all transaction log backups from the last database backupset @CMD = 'insert into ##backupnames select physical_device_name,' + char(39) + @db + char(39) + ',' + char(39) + 'l' + char(39) +
'from msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id join msdb..backupmediafamily c on a.media_set_id = c.media_set_id ' +
'where type=' + char(39) + 'l' + char(39) + 'and backup_start_date > @backup_start_dat and' + char(39) + @db + char(39) + ' = database_name'-- Execute command to place records in table to hold backup names for all transaction log backups from the last database backupexec
sp_executesql @cmd,@params=N'@backup_start_dat datetime', @backup_start_dat = @backup_start_date-- get next database to processfetch
next from db into @dbend-- close and deallocate database list cursorclose
dbdeallocate
db-- Declare cursor to contain list of database to processdeclare
db cursor for select
distinct a.name from master..sysdatabases a join ##backupnames b on a.name=b.database_namewhere
a. name not in ('Northwind', 'pubs', 'tempdb')-- Open database list cursoropen
db-- Get first recod from database list cursorfetch
next from db into @db-- Generate Heading in Restore scriptprint
'-- Restore All databases'-- Process all databasesWHILE
@@FETCH_STATUS = 0BEGIN-- define cursor for all database and log backups for specific database being processeddeclare backup_name cursor for
select name,type from ##backupnames where database_name = @DB-- Open cursor containing list of database backups for specific database being processed
open backup_name-- Determine the number of different backups available for specific database being processed
select @CNT = count(*) from ##backupnames where database_name = @DB -- Get first database backup for specific database being processed
fetch next from backup_name into @physical_device_name, @type-- Set counter to track the number of backups processed
set @NUM_PROCESSED = 0-- Process until no more database backups exist for specific database being processedWHILE @@FETCH_STATUS = 0
BEGIN-- Increment the counter to track the number of backups processed
set @NUM_PROCESSED = @NUM_PROCESSED + 1-- Is the number of database backup processed the same as the number of different backups available for specific database being processed?
if @CNT = @NUM_PROCESSED-- If so, is the type of backup currently being processed a transaction log backup?
if @TYPE = 'l'
begin-- build restore command to restore the last transaction log
select @cmd = 'restore log ' + rtrim(@db) + char(13) +' from disk = ' + char(39) +
rtrim(substring(@physical_device_name,1,len(@physical_device_name))) +
char(39) + char(13) + ' with replace'-- Generate restore command, and other commands for restore scriptprint @cmd
print 'go'
print ' '
end
else
begin-- Last backup was not a transaction log backup
-- Build restore command to restore the last database backup
select @cmd = 'restore database ' + rtrim(@db) + char(13) +' from disk = ' + char(39) +
rtrim(substring(@physical_device_name,1,len(@physical_device_name))) +
char(39) + char(13) + ' with replace'-- Generate restore command, and other commands for restore scriptprint @cmd
print 'go'
print ' '
end
else -- Current backup is not the last backup
-- Is the current backup being processed a transaction log backup?
if @TYPE = 'l'
begin-- Build restore command to restore the current transaction backup, with no recovery
select @cmd = 'restore log ' + rtrim(@db) + char(13) +' from disk = ' + char(39) +
rtrim(substring(@physical_device_name,1,len(@physical_device_name))) +
char(39) + char(13) + ' with replace, norecovery'-- Generate the restore command and other commands for restore scriptprint @cmd
print 'go'
print ' '
end
else
begin-- Current backup being processed is not a transaction log backup
-- Build restore command to restore the currrent database backup, with no recovery
select @cmd = 'restore database ' + rtrim(@db) + char(13) +' from disk = ' + char(39) +
rtrim(substring(@physical_device_name,1,len(@physical_device_name))) +
char(39) + char(13) + ' with replace, norecovery'-- Generate the restore command and other commands for restore scriptprint @cmd
print 'go'
print ' '
end-- Get next database backup to process
fetch next from backup_name into @physical_device_name, @typeend-- Close and deallocate database backup name cursor for current database being processedclose
backup_namedeallocate
backup_name-- Get next database to process
fetch next from db into @dbend-- Close and deallocate cursor containing list of databases to processclose
dbdeallocate
db-- Drop global temporary tabledrop
table ##backupnames

Here is a SP that will backup a single database. Note how the backup directory name is obtained. You might need to modify this depending on what version of SQL and want instance you are running this against. Maybe someday I will make this work for multiple versions and instances.USE [DBA]
GOSET
ANSI_NULLS ONGOSET
QUOTED_IDENTIFIER ONGOalter proc [dbo].[usp_backupdb] -- exec dbo.usp_backupdb model
-- Author: Gregory A. Larsen
-- This stored procedure will create a full,diff, or log backup of a database. Using
-- The standard naming conventions. The backup will be placed in the '
-- default instance backup directory as found in the registry.@DBNAME
NVARCHAR(128),@BACKUPTYPE
NVARCHAR(20) = 'FULL', -- options should be on of these FULL, DIFF, and LOG@SCHEDTYPE
NVARCHAR(20) = 'ADHOC' -- options should be ADHOC, or SCHEDas
DECLARE
@TODAYS_BACKUP NVARCHAR(128)Declare
@CMD NVARCHAR(1000)Declare
@BACKUPDIR NVARCHAR(500)--
-- Create temporary table to hold regedit results
--create
table #regedit (Value nvarchar(128), Data nvarchar(500))--
-- insert backup directory information from registry into temp table
--insert into #regedit exec ('master.dbo.xp_regread ' +
'@rootkey = ''HKEY_LOCAL_MACHINE'',' + '@key = ''SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServer'',' +
'@value_name = ''BackupDirectory''')--
-- Get the name of the backup directory
--select @BACKUPDIR=Data from #regedit --
-- Drop temp table, since it is no longer needed
--drop
table #regedit--
-- Determine name of Todays backup
--set @TODAYS_BACKUP = rtrim(@DBNAME) + '_' +
convert(char(8),getdate(),112) + '_' + substring(convert(char(8),getdate(),108),1,2) +
substring(convert(char(8),getdate(),108),4,2) +
substring(convert(char(8),getdate(),108),7,2) +'_' + rtrim(@SCHEDTYPE) +
'_' + rtrim(@BACKUPTYPE) + '_BKUP'--
-- Build command to create backup device
--set @CMD = 'sp_addumpdevice ' + char(39) +
'disk' + char(39) + ',' + char(39) +@TODAYS_BACKUP
+ char(39) + ',' + char(39) +
rtrim(@BACKUPDIR) + '' + @TODAYS_BACKUP + '.BAK' + char(39)print
@CMD--
-- execute command to build backup device
--
exec (@CMD)--
-- Full Backup?
--if
@BACKUPTYPE = 'FULL'begin--
-- Build backup command for full backup
--
set @CMD = 'BACKUP DATABASE [' + rtrim(@DBNAME) + '] to [' +@TODAYS_BACKUP
+ '] with retaindays=6'end
-- then @BACKUPTYPE = 'FULL'Else
-- else @BACKUPTYPE = 'FULL'begin
if @BACKUPTYPE = 'DIFF'
begin--
-- Build backup command for Differential Backup
--
set @CMD = 'BACKUP DATABASE [' + rtrim(@DBNAME) + '] to [' +@TODAYS_BACKUP + '] WITH retaindays=6,DIFFERENTIAL'
End -- Then @BACKUPTYPE = 'DIFF'
Else -- else @BACKUPTYPE = 'DIFF'
Begin--
-- Build backup command for tranaction log backup
--
set @CMD ='BACKUP LOG ' +
'[' + @DBNAME +'] to [' +@TODAYS_BACKUP
+ ']' + ' with retaindays=6'
End -- @BACKUPTYPE = 'DIFF'End
-- @BACKUPTYPE = 'FULL'print
@cmd--
-- execute backup command
--exec (@CMD)

Sorry for the spam. But here is a version that I think is instance aware. Works in 2000 and 2005, I think. Let me know if it doesn't
USE [DBA]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[usp_backupdb] -- usp_backupdb model
-- Author: Gregory A. Larsen
-- This stored procedure will create a full,diff, or log backup of a database. Using
-- The standard naming conventions. The backup will be placed in the '
-- default instance backup directory as found in the registry.
-- Works for SQL Server 2000 and 2005.
@DBNAME NVARCHAR(128),
@BACKUPTYPE NVARCHAR(20) = 'FULL', -- options should be on of these FULL, DIFF, and LOG
@SCHEDTYPE NVARCHAR(20) = 'ADHOC' -- options should be ADHOC, or SCHED
as
DECLARE @TODAYS_BACKUP NVARCHAR(128)
Declare @CMD NVARCHAR(1000)
Declare @BACKUPDIR NVARCHAR(500)
--
-- Get the name of the backup directory
--
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'BackupDirectory', @BACKUPDIR OUTPUT

--
-- Determine name of Todays backup
--
set @TODAYS_BACKUP = rtrim(@DBNAME) + '_' +
convert(char(8),getdate(),112) + '_' +
substring(convert(char(8),getdate(),108),1,2) +
substring(convert(char(8),getdate(),108),4,2) +
substring(convert(char(8),getdate(),108),7,2) +
'_' + rtrim(@SCHEDTYPE) +
'_' + rtrim(@BACKUPTYPE) + '_BKUP'

--
-- Build command to create backup device
--
set @CMD = 'sp_addumpdevice ' + char(39) +
'disk' + char(39) + ',' + char(39) +
@TODAYS_BACKUP + char(39) + ',' + char(39) +
rtrim(@BACKUPDIR) + '' + @TODAYS_BACKUP + '.BAK' + char(39)
print @CMD
--
-- execute command to build backup device
--
exec (@CMD)
--
-- Full Backup?
--
if @BACKUPTYPE = 'FULL'
begin
--
-- Build backup command for full backup
--
set @CMD = 'BACKUP DATABASE [' + rtrim(@DBNAME) + '] to [' +
@TODAYS_BACKUP + '] with retaindays=6'
end -- then @BACKUPTYPE = 'FULL'
Else -- else @BACKUPTYPE = 'FULL'
begin
if @BACKUPTYPE = 'DIFF'
begin
--
-- Build backup command for Differential Backup
--
set @CMD = 'BACKUP DATABASE [' + rtrim(@DBNAME) + '] to [' +
@TODAYS_BACKUP + '] WITH retaindays=6,DIFFERENTIAL'
End -- Then @BACKUPTYPE = 'DIFF'
Else -- else @BACKUPTYPE = 'DIFF'
Begin
--
-- Build backup command for tranaction log backup
--
set @CMD ='BACKUP LOG ' +
'[' + @DBNAME +'] to [' +
@TODAYS_BACKUP + ']' + ' with retaindays=6'
End -- @BACKUPTYPE = 'DIFF'
End -- @BACKUPTYPE = 'FULL'
print @cmd
--
-- execute backup command
--
exec (@CMD)

Hello,
Really thaxx but plz tell me which one is right one bcause u send me 3 scripts plz just tell me which is useful for me .
Thaxx Greg Larsen
Regards
jagpalsingh

There is only one restore script, use that one. And then use the last script for backup, skip the one in the middle. Note that usp_backupdb only backs up a single database, so you will need to drive it with a little loop that processes through all the DBs you want to backup.