Automate weekly DB restore

• NewBornDBA2017

  SSCarpal Tunnel

  Points: 4832

  More actions

  July 31, 2017 at 11:13 am

  #322917

  All, I need to refresh our Dev from prod and I am wondering if someone can help me out since I am still at the beginning/learning stage of development.
  Current Backup file : C:\Backup\FULL\DC2THKHDB04_VA24138_Live_FULL_20170730_030000.BAK
  Backup file from last week : C:\Backup\FULL\DC2THKHDB04_VA24138_Live_FULL_20170723_030000.BAK
  This is code I am trying to write but I don't even know if my approach is correct
  declare @Date datetime

  set @Date = '' --I am not sure what goes here.    

  RESTORE DATABASE [VA24138]
FROM DISK = N'C:\Backup\FULL\DC2THKHDB04_VA24138_Live_FULL_' + @Date + '_030000.BAK'
WITH FILE = 1, MOVE N'e4_2_2_0_baseline_data' TO N'D:\DBFiles\VA24138_Live.mdf',
MOVE N'e4_2_2_0_baseline_data2' TO N'D:\DBFiles\VA24138_Live2.mdf',
MOVE N'e4_2_2_0_baseline_log' TO N'E:\DBLogs\VA24138_Live_Log.ldf',
NOUNLOAD, REPLACE, STATS = 5

  GO

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  July 31, 2017 at 12:18 pm

  #1953446

  using the technique described here:
  http://improve.dk/sql-server-datetime-rounding-made-easy/

  you probably want to declare your variable as NVARCHAR:
  DECLARE @date nvarchar(8);

  then you can use CONVERT function to do something like:
  SET @date = CONVERT(nvarchar(8), DATEADD(wk, DATEDIFF(wk, 0, GetDate()), -1), 112)