Restore a Backup from network drive which has multiple .bak files

Question

Experts, I am trying this since a week now.

Goal: Restore a Backup from network drive which has multiple .bak files

Nework Drive: \\nodccstsqlb1\ca$\NODCCSTCA4\PCAPO\

Files under it: Lot of .bak and lot of .trn files

Need T-SQL script or SQL Job which picks up the Latest .BAK only from mentioned network drive.

File are like:

PCAPO_backup_2012_08_08_000502_2404151.bak

PCAPO_backup_2012_08_07_050001_0921233.bak

...................................................................................

Please help.

Thanks

Answer 1

My code till now is:

RESTORE DATABASE [PCAPO]
FROM  DISK = ????? WITH  FILE = 1, 
MOVE N'PCAPO_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MIRROR\MSSQL\DATA\TCAPO.mdf', 
MOVE N'PCAPO_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MIRROR\MSSQL\DATA\TCAPO.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
GO

Answer 2

Upload the file names in the directory.

Once you have the data in table, it is easy to select the file you need.

http://www.sqlusa.com/bestpractices2008/list-files-in-directory/

Construct a dynamic SQL string to RESTORE and execute it:

http://www.sqlusa.com/bestpractices/dynamicsql/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING

Answer 3

This blog post may help it is not the exact thing you want though

http://blogs.msdn.com/b/ai/archive/2012/08/09/migrating-sql-server-2000-user-databases-to-sql-server-2012.aspx

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
My Blog
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.