Scripting a SQL Restore from a daily backup

[KAMAZON]

Hello Team Anandtech, I hope all is going well.

I am attempting to help out our B.I team with their Data Warehouse and they want to have a nightly backup of their live production database be restored on their B.I home server. I can do this manually by changing the BKF (SQL backup file) file name in my script, but I want this automated so it is not person dependant.

Here is some background info, my script, and I'm curious if you have any suggestions. Perhaps I can make the backup file a SQL variable in my restore script but i'm not sure what to do from there.

In this example, let's say this is my backup source:
Backup source: \\server1\dbbackup\CABACKUP0415080500.BAK
Database Name: CACINEMA
Database Backup Location File: \\server1\dbbackup\CABACKUP0415080500.BAK'

Here is the restore script
-- DB Restore Script
RESTORE DATABASE [CACINEMA] FROM DISK = N'\\server1\dbbackup\CABACKUP0415080500.BAK' WITH FILE = 1,
MOVE N'CACINEMA_Data' TO N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CACINEMA.mdf',
MOVE N'CACINEMA_Log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CACINEMA.ldf', NOUNLOAD, STATS = 10
GO



And I need to restore that DB on a different server. In SQL you have to tell it where you want to extract the MDF (master database file) and LDF (Log Database File) files. You give them a logical name as wel, so the MDF logical name is: CACINEMA_Data and the LDF logical name is CINEMA_Log


Now, the only thing I really need to do is add a kill all connections, drop database command for our BI server which is no big deal. The problem comes in telling it the NEW backup file to copy over. I want it to automatically update ?\\server1\dbbackup\CABACKUP0415080500.BAK? to ?\\server1\dbbackup\CABACKUP0416080500.BAK? for the next days backup, in this case the 16th.



Any suggestions? Was my post clear? Any feedback is appreciated, thank you!

 

[KB]

To clear all open connections: http://mail.localplanet.co.uk/...r_sql_connections.aspx

To generate that time-based filename:

DECLARE @backupfile varchar(255)

SET @backupfile = '\\server1\dbbackup\CABACKUP' + CASE WHEN MONTH(GETDATE()) < 10 THEN '0' + CONVERT(varchar(1), MONTH(GETDATE())) ELSE CONVERT(varchar(2), MONTH(GETDATE())) END + CONVERT(varchar(2), DAY(GETDATE())) + RIGHT(CONVERT(varchar(4), YEAR(GETDATE())), 2) + '0500.BAK'

PRINT @backupfile

SET @sql = ''
SET @sql = @sql + 'RESTORE DATABASE ' + @dbName + CHAR(10)
SET @sql = @sql + 'FROM DISK = ''' + @backupfile + '''' + CHAR(10)
.........

PRINT @sql
EXEC(@sql)

 

[KAMAZON]

KB you the man!! I can't wait ot load this up in my test environment and give it a go!