SQL Server Performance

How can we make Database backup and restore script

Discussion in 'Getting Started' started by jagpalsingh4, Mar 4, 2008.

  1. jagpalsingh4 New Member

    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
  2. susanthab New Member

    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.
  3. jagpalsingh4 New Member

    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 .
  4. Greg Larsen New Member

    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
  5. Greg Larsen New Member

    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)
  6. Greg Larsen New Member

    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)
  7. jagpalsingh4 New Member

    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
  8. Greg Larsen New Member

    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.

Share This Page