Setting up a recurring job (using T-SQL) to restore from backup nightly

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  April 14, 2008 at 4:50 pm

  #802307

  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]

• JPBECK

  Mr or Mrs. 500

  Points: 554

  More actions

  April 14, 2008 at 6:55 pm

  #802328

  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.

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  April 16, 2008 at 7:57 am

  #803086

  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]

• Jonas Jisendal

  SSChasing Mays

  Points: 636

  More actions

  April 16, 2009 at 7:45 am

  #978828

  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

• suneel kamavaram

  SSCommitted

  Points: 1873

  More actions

  December 18, 2011 at 10:18 am

  #1422912

  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