Scheduled automated restore.

• oughtsix

  SSC-Addicted

  Points: 448

  More actions

  January 15, 2019 at 2:32 pm

  #381187

  My backups run every night starting at 2:00am.  I need to restore the latest backup to our Development sql server.  I name my backups xxxxx_yyyy-mm-dd_hh-mm-ss.

  Here is the script I came up with to search the backup directory for the latest backup file and restore it on the Dev server.
declare @files table ([FileName] varchar(255), Depth int, IsFile int)
declare @databasename nvarchar(255) = 'DentalEdge';
declare @restoretodatabasename nvarchar(255) = 'DentalEdgeQA';
declare @source_path nvarchar(255) = '\\backup10\SQL19Share\backup\UserDBs\Full\DentalEdge';
declare @source_file nvarchar(255);
declare @database_data_path varchar(255) = 'E:\Data';
declare @database_log_path varchar(255) = 'F:\Logs';

  insert into @files EXEC Master.dbo.xp_DirTree @source_path,1,1
SELECT TOP(1) @source_file = [FileName] FROM @files WHERE IsFile=1 ORDER BY [@files].[FileName] DESC    
PRINT @source_file

  declare @url nvarchar(255) = @source_path + N'\' + @source_file;
declare @databasefile nvarchar(255) = @databasename;
declare @databaselog nvarchar(255) = @databasename + '_Log';

  declare @sqlStr1 nvarchar(max) = '';
set @sqlStr1 = @sqlStr1 + 'BEGIN TRY ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '  ALTER DATABASE [' + @restoretodatabasename + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '  DROP DATABASE [' + @restoretodatabasename + '];' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'END TRY ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'BEGIN CATCH ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'END CATCH ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'RESTORE DATABASE [' + @restoretodatabasename + ']' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '    FROM DISK = N''' + @url + ''' ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'WITH ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'move N''' + @databasefile + '_data'' to N''' + @database_data_path + '\' + @restoretodatabasename + '.mdf'',' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'move N''' + @databaselog + ''' to N''' + @database_log_path + '\' + @restoretodatabasename + '_log.ldf'',' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '    NOUNLOAD, ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '    REPLACE,' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '    RECOVERY,' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '    STATS = 1' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '' + char(13) + char(10);

  print @sqlStr1;

  EXECUTE(@sqlStr1);


• Michael L John

  One Orange Chip

  Points: 27038

  More actions

  January 15, 2019 at 2:44 pm

  #2019009

  oughtsix - Tuesday, January 15, 2019 2:32 PM

  My backups run every night starting at 2:00am.  I need to restore the latest backup to our Development sql server.  I name my backups xxxxx_yyyy-mm-dd_hh-mm-ss.

  Here is the script I came up with to search the backup directory for the latest backup file and restore it on the Dev server.
declare @files table ([FileName] varchar(255), Depth int, IsFile int)
declare @databasename nvarchar(255) = 'DentalEdge';
declare @restoretodatabasename nvarchar(255) = 'DentalEdgeQA';
declare @source_path nvarchar(255) = '\\backup10\SQL19Share\backup\UserDBs\Full\DentalEdge';
declare @source_file nvarchar(255);
declare @database_data_path varchar(255) = 'E:\Data';
declare @database_log_path varchar(255) = 'F:\Logs';

  insert into @files EXEC Master.dbo.xp_DirTree @source_path,1,1
SELECT TOP(1) @source_file = [FileName] FROM @files WHERE IsFile=1 ORDER BY [@files].[FileName] DESC    
PRINT @source_file

  declare @url nvarchar(255) = @source_path + N'\' + @source_file;
declare @databasefile nvarchar(255) = @databasename;
declare @databaselog nvarchar(255) = @databasename + '_Log';

  declare @sqlStr1 nvarchar(max) = '';
set @sqlStr1 = @sqlStr1 + 'BEGIN TRY ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '  ALTER DATABASE [' + @restoretodatabasename + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '  DROP DATABASE [' + @restoretodatabasename + '];' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'END TRY ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'BEGIN CATCH ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'END CATCH ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'RESTORE DATABASE [' + @restoretodatabasename + ']' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '    FROM DISK = N''' + @url + ''' ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'WITH ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'move N''' + @databasefile + '_data'' to N''' + @database_data_path + '\' + @restoretodatabasename + '.mdf'',' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + 'move N''' + @databaselog + ''' to N''' + @database_log_path + '\' + @restoretodatabasename + '_log.ldf'',' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '    NOUNLOAD, ' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '    REPLACE,' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '    RECOVERY,' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '    STATS = 1' + char(13) + char(10);
set @sqlStr1 = @sqlStr1 + '' + char(13) + char(10);

  print @sqlStr1;

  EXECUTE(@sqlStr1);


  And what is your question?

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/