May 27, 2008 at 1:29 am
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
May 27, 2008 at 6:43 am
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]
May 27, 2008 at 7:01 am
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
May 28, 2008 at 4:34 pm
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 🙂
May 28, 2008 at 5:01 pm
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
May 31, 2008 at 12:52 am
Thanks guys, I will try that last suggestion and let you know.
June 17, 2010 at 7:40 am
Awsome, exactly what I was looking for! My thanks to the original poster for adding his solution and all respondents for their suggestions.
James.
September 13, 2011 at 2:48 pm
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