2

I basically have 6 daily SQL Server backups (.bak) that need a restore everyday. I want to automate this process by having the SQL Agent run the script daily. I have the following restore script written, and I know it's not completely correct, that is why I'm here to ask for any pointers is much appreciated!

use master;
go

set nocount on

-- 1 - variable declaration 
declare @dbname nvarchar(max) --database to restore 
declare @backuppath nvarchar(max) --backup location
declare @filelisttable --list the table to populate 
declare @sql nvarchar(max) --dynamic sql 
declare @mdf nvarchar(max) --logical primary data file
declare @ndf nvarchar(max) --logical 2nd data file
declare @ldf nvarchar(max) --logical log data file
declare @dp nvarchar(200) --data file path
declare @lp nvarchar(200) --log file path
declare @backupfile nvarchar(500)

-- 2 - initialize variables
set @dbname = 'test'
set @backuppath = '\\mybackuppath\c$\test'

-- 3 - get list of files
insert into @filelisttable exec('restore filelistonly from disk = ''' +@backuppath+'''') --temptable to retrieve all back up files 

-- 4 - retrieve top 6 recent data extracts 
select  @backuppath  = max(backupfile)
from @filelist
where backupfile like '%.bak' --how to pick up most recent 6 back up files w/timestamp on trails?

set @sql = ('use [master])
    restore database @dbname ['+@dbname+']
        from disk = N'''+@backuppath+'''
            with replace,
            move N'''+@mdf+''' to N'''+@mdf+'''
            move N'''+@ndf+''' to N'''+@ndf+'''
            move N'''+@ldf+''' to N'''+@ldf+''''

-- 5- the 6 backup files that need to be restored daily

/** I have to run this manually everyday, and I want to automate this.
RESTORE DATABASE test FROM
    DISK = 'C:\test\test_Customer_20160518_1.bak',
    DISK = 'C:\test\test_Customer_20160518_2.bak',
    DISK = 'C:\test\test_Customer_20160518_3.bak',
    DISK = 'C:\test\test_Customer_20160518_4.bak',
    DISK = 'C:\test\test_Customer_20160518_5.bak',
    DISK = 'C:\test\test_Customer_20160518_6.bak'
    WITH REPLACE,
    MOVE 'test_Data1' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test_CUSTOMER.mdf',
    MOVE 'test_Data2' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test_CUSTOMER_1.ndf',
    MOVE 'test_Log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test_CUSTOMER_2.ldf'
    GO
**/

use master 
alter database test set multi_user with rollback immediate;

go

return

The goal of this script is to automate a restore with the variables declared, restore the 'test' database from the backup path where 6 files sit, create a temp filelist table to be used for restore, pick it up from there, and restore the test database everyday based on the latest date stamped files.

Currently, there are old and new files in there. I need a WHERE statement to only use current ones.

Improve this question
0

0

Reset to default