Restore Backup from changing file name

Question

Post reply

Restore Backup from changing file name

Terence-701215

Old Hand

Points: 343
More actions
May 27, 2008 at 1:29 am

#380677

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

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

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

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]

Terence-701215 Old Hand Points: 343 More actions · Answer 2

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

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 3

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 🙂

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 4

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

Terence-701215 Old Hand Points: 343 More actions · Answer 5

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

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 6

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

James.

syed_h_ali2001 SSC Enthusiast Points: 113 More actions · Answer 7

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