I need to restore the database daily using a scheduled SQL job. Below is my requirement: First time I will have Full db backup file and then from the following day I will have diff db backup files in the network folder. So the first time I need to restore the Full db backup file and create the db. And then after that every day I need to restore the diff db backup file. The database will be used by the users for writing reports. I want to use SSIS package for this. Any ideas. Thanks.sqldev

It sounds like a reporting requirment for a decisionsupport style database running from yesterdays data, would I be correct in this? If this is the case then you're going to have to restore the latest full backup each time you wish to refresh this DB with the NO RECOVERY option, you'll then need to restore the latest diff using WITH RECOVERY. Out of interest why would you want to use SSIS for this? Are you restoring to a different server? I personally wouldn't use SSIS for this, I would favour log shipping or database mirroring with database snapshots if on a different server as this will also help your HA.

Use master.dbo.xp_cmdshell stored procedure and loop through backup folder inserting file names into temp table. It will give a list of all backup files, for example - .bak, dif and trn. Get the last full backup file (.bak) from temp table, find the last dif file (.dif) after the last full backup and all .trn files after the last .dif file. Using "RESTORE FILELISTONLY from DISK" get primary, secondary and log files structure of a database. Construct your SQL and restore full backup .bak, diff backup .dif and all .trn files Sergei

Hello SQLDev1, Yes, a SSIS package is suitable for a database restore. And all you need is an Execute SQL Task; I have seen exactly this answered on our forum before: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/7bd8cecf-3926-4a43-a9a3-fe48ebcfe993Arthur My Blog

I reviewed the above link, in my case all I will get from my client is FullDB.bak file the first time and then from the second day onwards I will be getting the DiffDb files which will be placed on our network folder. Then I need to use those files to restore/update my DB daily.sqldev

Any ideas? Thanks.sqldev

Still the same approach can be used, just the SQL statement to restore from DiffDb will be needed. The way a database restored is first you restore the full back up and then the differential backups.Arthur My Blog