Restore Backup from changing file name

  • Hi All

    I am trying to create a daily job that restores a backup of a database to a server as Read Only and Simple Recovery. That's all well and good. I have gotten that far, but now the problem is is that the backup file from which I'm restoring is a backup file generated from a standard maintenance plan. Meaning that the name of the file follows the SQL naming scheme which is dbname_backup_dateandtime.bak.

    How would I script this so that I can use T-SQL to select the file with the correct name. There is only one backup file which will be the latest, as previous backups are moved to tape daily.

    What I've got so far is the following. It's just how to incorporate the changing file name.

    USE MASTER

    GO

    ALTER DATABASE [dbname]

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    RESTORE DATABASE [dbname] FROM DISK = 'c:\Temp\dbname.bak'

    EXEC sp_dboption 'dbname', 'read only', 'true'

    ALTER DATABASE [dbname]

    SET RECOVERY SIMPLE, MULTI_USER WITH ROLLBACK IMMEDIATE

  • You can query the msdb database for the last backup of a database.

    Something like this:

    select top 1 name+'.bak' from msdb..backupset

    where type = 'D' and database_name = MyDb

    Order By backup_set_id

    Type 'D' means full backup, transaction log backups would be type 'L'

    [font="Verdana"]Markus Bohse[/font]

  • Thanks for the reply Markus, but I managed to find what I was looking for. And also your way would work if the db was on the same server 🙂

    Using xp_cmdshell I have found that the bak file can be selected by doing the following

    select @cmd = 'dir /B ' + @Path + 'dbname*.bak'

    create table #a (s varchar(2000))

    insert #a exec master..xp_cmdshell @cmd

    delete #a

    where s is null

    or s not like '%full%'

    select @filename = max(s) from #a

    So incorporating everything, the final version looks like the following and it works 🙂 :

    declare

    @Path varchar(50),

    @FileName varchar(100),

    @RestoreFile varchar(200),

    @cmd varchar(2000)

    set @Path = 'C:\Temp\'

    select @cmd = 'dir /B ' + @Path + 'TestDB*.bak'

    create table #a (s varchar(2000))

    insert #a exec master..xp_cmdshell @cmd

    delete #a

    where s is null

    or s not like '%TestDB%'

    select @filename = max(s) from #a

    set @RestoreFile = '' + @Path + @FileName + ''

    ALTER DATABASE [TestDBDuplicate]

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    RESTORE DATABASE [TestDBDuplicate] FROM DISK = @RestoreFile

    EXEC sp_dboption 'TestDBDuplicate', 'read only', 'true'

    ALTER DATABASE [TestDBDuplicate]

    SET RECOVERY SIMPLE, MULTI_USER WITH ROLLBACK IMMEDIATE

    drop table #a

  • you can also use the 'ren' command to rename the backup file, providing xp_cmdshell is enabled (SQL 2005)

    xp_cmdshell 'ren \\servername\sharename\Backups\databasename\xyz_db_*.bak new_name.bak'

    Then add a job step to restore from 'new_name.bak' etc

    just my 2 cents 🙂

  • You could also use the extended stored procedure xp_dirtree.

    EXECUTE xp_dirtree 'C:\Backup', 1, 1

    http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1270919,00.html

    The disadvantage is that it's undocumented. The advantage is that you don't have to enable xp_cmdshell.

    Ola Hallengren

    http://ola.hallengren.com

  • Thanks guys, I will try that last suggestion and let you know.

  • Awsome, exactly what I was looking for! My thanks to the original poster for adding his solution and all respondents for their suggestions.

    James.

  • This is awesome. Thanks to all of you. Below is script from your conversation that worked for me.

    --Find the backup file name to restore from

    declare

    @Path varchar(50),

    @FileName varchar(100),

    @RestoreFile varchar(200)

    set @Path = 'P:\MSSQL2008\BACKUP\DBNAME'

    create table #a (s varchar(200),d int,t int)

    insert #a exec master..xp_dirtree @path,1,1

    select @filename = s from #a

    set @RestoreFile = '' + @Path + @FileName + ''

    -------Kill all existing sessions before backup

    DECLARE @DatabaseName nvarchar(50)

    SET @DatabaseName = N'DBNAME'

    --SET @DatabaseName = DB_NAME()

    DECLARE @sql varchar(max)

    SET @sql = ''

    SELECT @sql = @sql + 'Kill ' + Convert(varchar, SPId) + ';'

    FROM MASTER..SysProcesses

    WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

    and spid > 50

    -- SELECT @sql

    EXEC(@SQL)

    --Set DB to Single User mode

    ALTER DATABASE [DBNAME]

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    RESTORE DATABASE [DBNAME] FROM DISK = @RestoreFile

    WITH REPLACE

    ALTER DATABASE [DBNAME]

    SET RECOVERY SIMPLE, MULTI_USER WITH ROLLBACK IMMEDIATE

    drop table #a

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply