Restore a database with TimeStamp that changes everyday

Question

Post reply

Restore a database with TimeStamp that changes everyday

SmilingLily

SSC Eights!

Points: 920
More actions
March 28, 2016 at 8:51 am

#312356

Hi,
I have a Prod Server with multiple databases. I copy all the databases to DR box overnight. I need to restore all the copied databases on DR Box.
But every time the Databases come with Timestamp at the end. Example - Databasename_backup_*.bak
How can I write a Automated Restore Job for backups like this?
Please advise
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ManicStar SSCoach Points: 17992 More actions · Answer 1

How are you with Powershell? A script could do this pretty easily.

SmilingLily SSC Eights! Points: 920 More actions · Answer 2

SmilingLily

SSC Eights!

Points: 920

March 28, 2016 at 9:54 am

#1868016

I never used power shell.

ManicStar SSCoach Points: 17992 More actions · Answer 3

SmilingLily (3/28/2016)
I never used power shell.

The other option might be ssis, but Powershell would be easier.

You could possibly use a foreach loop container to point at the place where your backup files go to live. Iterate through each backup and build tsql logic to restore it to the proper database instance.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I agree that power shell would be easier.

I have done it using DTS and SSIS using the file system object.

Search for the latest date file and rename, restore, archive and purge the oldest file.

If I had the code I would share it with you. The company went belly up.:crying:

Regards.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

If you use powershell, don't get hung up on the naming standard. Just go for the latest date. You can read that property from the files.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Thanks Grant for the info!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ManicStar SSCoach Points: 17992 More actions · Answer 7

This might be a starting point for you:

https://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Auto-Restore-08d8c77f