SQL Server Performance

restore most recent backup

Discussion in 'Contribute Your SQL Server Scripts' started by MichaelB, Jan 15, 2014.

  1. MichaelB Member

    the code below will kick people off the database (single user mode) and restore the most recent backup file from a network share and put it back into multiuser mode. this is great for DEV or TEST refreshes that you need to schedule and dont want to make a special backup or copy/rename the backup file for the receiving server.

    Code:
    /******************************************************
    Script: looks at the backup directory and restores the
        most recent backup (bak) file
        You will have to modify the code
        to match your database names and paths.
        DO NOT USE IN PRODUCTION.  It kicks all users off!
    
    Created By:
        Michael F. Berry
    Create Date:
        1/15/2014
    ******************************************************/
     
    --get the last backup file name and path
    
    Declare @FileName varChar(255)
    Declare @cmdText varChar(255)
    Declare @BKFolder varchar(255)
    
    set @FileName = null
    set @cmdText = null
    set @BKFolder = '\\MyBackupStorageShare\server\FULL\'
     
    create table #FileList (
    FileName varchar(255),
    DepthFlag int,
    FileFlag int
    )
     
    --get all the files and folders in the backup folder and put them in temporary table
    insert into #FileList exec xp_dirtree @BKFolder,0,1
    --select * from #filelist
    
    --get the latest backup file name
    select top 1 @FileName = @BKFolder + FileName from #FileList where Filename like '%.bak' order by filename desc
    select @filename
     
    --kick off current users/processes
    ALTER DATABASE DBName
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
     
    --execute the restore
    exec('
    RESTORE DATABASE [DBNAME] FROM  DISK = ''' + @filename + '''
    WITH  MOVE N''DBName_Data'' TO N''E:\SQLData\DBName.mdf'', MOVE N''DBName_Log'' TO N''E:\SQLLogs\DBName_log.ldf'',  NOUNLOAD,  REPLACE,  STATS = 10')
     
    --Let people/processes back in!
    ALTER DATABASE DBName
    SET MULTI_USER WITH ROLLBACK IMMEDIATE;
    go
    

Share This Page