April 14, 2008 at 4:50 pm
Actually, upon further reflection, it seems likely that you need to add your backup directory name to the DIRectory command:
Insert into #BakDir exec xp_cmdshell 'dir D:\BKUP\Demo /B'
declare @file varchar(max)
Select @File = FileName from #BakDir
Print 'Restoring from file:'
Print @file
Print '--======'
RESTORE DATABASE [Demo] FROM DISK = @file
WITH FILE = 1, MOVE N'Demo' TO N'D:\SQLData\Demo.mdf',
MOVE N'Demo_log' TO N'D:\SQLData\Demo_log.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 14, 2008 at 6:55 pm
This is the result of running that SQL:
(2 row(s) affected)
Restoring from file:
--======
Msg 3044, Level 16, State 2, Line 7
Invalid zero-length device name. Reissue the BACKUP statement with a valid device name.
Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
April 16, 2008 at 7:57 am
Yeah, it's definitely going to the wrong directory.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 16, 2009 at 7:45 am
Hi !
I modified the script a bit like this
"Create table #BakDir(FileName varchar(255))"
" Insert into #BakDir exec xp_cmdshell 'dir E:\Name1\*.bak /B' "
If i do Select * from #BakDir i can se what´s in the table ...and with this it is only the file called .bak regardless the date that EM put on the backupfile
But it didn´t anyway......
If I put in the line "print @file" nothing comes up.......
Yes !! I found it !!
like this :
" Insert into #BakDir exec xp_cmdshell 'dir E:\Name\*.bak /B' " - remove the " " just there to get the text look right......
declare @file varchar(8000)
Select @file = filename from #BakDir where filename is not null
print 'file'
print @file
generates this
file
File_200904162013.BAK
December 18, 2011 at 10:18 am
This script takes backup file path from backupmediafamily table and tries to restores backup to db db_toberestored with out validating the existence of backup file.
declare @path varchar(250),@dbtype varchar(5),@dbname varchar(20)
set @dbtype ='D' set @dbname ='dbname'
set @path=(select physical_device_name from msdb.dbo.backupmediafamily where media_set_id=
( select max(media_set_id) from msdb.dbo.backupset where type=@dbtype and database_name=@dbname
and CONVERT(VARCHAR(10),backup_finish_date,111)=CONVERT(VARCHAR(10),GETDATE(),111)))
--print @path
RESTORE DATABASE [db_toBerestored] FROM DISK = @path WITH REPLACE, FILE = 1,
MOVE N'db_PRIMARY_DATA' TO N'C:\db_model_pri.mdf',
MOVE N'db_LOG' TO N'C:\db_log.ldf', NOUNLOAD, STATS = 10
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy