February 23, 2015 at 9:56 pm
Hi All,
I am working on a task. Currently we are taking a database backup and keeping that backups in a folder. The backups doesn't have time stamp on it. My task is
need to get the latest backup and copy that backups into some other server and then restore the database from there.
How could we achieve this? I am planning to create SSIS package.
Do we need script task for this task.How to get the .bak with latest create or moidified date. For now we doesn't have timestamp so need to go based on modified date?
February 24, 2015 at 3:51 am
You can directly create a SQL job to take backup on a remote share and restore them locally without using SSIS package. You have to write dynamic query to add date and time to the backup file .
February 24, 2015 at 5:36 am
The primary server is not in our control. We can't take backups on that. Other team will keep the backups in the middle and agree to give us access on that backup folder
February 24, 2015 at 5:52 am
This query will return the most recent full backup for the database you specify:
SELECT TOP 1 bs.database_name, bs.backup_finish_date, bmf.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE bs.type = 'D'
AND bs.database_name = 'DatabaseName'
ORDER BY bs.backup_finish_date DESC;
If the server where you want to restore the database has permission to the directory with the backup files, you can perform your restore using T-SQL. https://msdn.microsoft.com/en-us/library/ms186858%28v=sql.100%29.aspx
February 24, 2015 at 8:41 am
You can also query the backup files and look at the LSNs to do some matching to determine what's the latest. Ultimately that's the best. This script might help you: http://www.sqlservercentral.com/scripts/Filter+Backup/119946/
It needs some modification to work for you, but it shows the idea of getting info from the files, not backup tables.
February 24, 2015 at 11:14 am
Sorry guys.
I can't able to run anything on the other server. I have the access on the backup server only. From the backup server, I have to use some logic to find the latest backup then copy that latest to the local server
February 24, 2015 at 12:34 pm
ramana3327 (2/24/2015)
Sorry guys.I can't able to run anything on the other server. I have the access on the backup server only. From the backup server, I have to use some logic to find the latest backup then copy that latest to the local server
It sounds like you already know the steps you need to take.
1. Determine the most recent full backup.
2. Copy it to the destination server.
3. Somehow restore from the copied backup on the destination server.
The only big problem I see is how you're going to do a restore if you can't run anything on that server.
February 24, 2015 at 1:00 pm
I am clear about the steps but needs to find the latest backup from folder when there is no time stamps associated with backups . Is it possible through execute sql task or do we need script task to find the latest backup file in the folder?
February 24, 2015 at 1:07 pm
ramana3327 (2/24/2015)
I am clear about the steps but needs to find the latest backup from folder when there is no time stamps associated with backups . Is it possible through execute sql task or do we need script task to find the latest backup file in the folder?
There's the query above, but I can't help you with an SSIS task. Sorry, but I don't know much about SSIS other than how to spell it. Can a script task can execute a SQL query and pass the value along to the next task?
February 24, 2015 at 1:25 pm
The script I referenced does this. You need a RESTORE HEADERONLY on each file to find the latest.
Please read through the script to see how it works.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply