March 23, 2006 at 1:29 pm
I have an interesting backup / recovery question that I wonder if someone could help me with.
We have a company off site host one of our databases with some critical data in it, and this company is able to provide 24/7 support for the application that uses the data for us. This works out great, except for when it comes time to run reports at our main office off this data.
The solution we use to get arround this is a little bit clunky, but it works. Every week the hosting company puts a full backup of the database on an FTP site, and daily (or so) puts a transaction log backup. We then have a script that automatically downloads these files daily, and as needed we restore them on a server in our office to allow people to run reports.
Currently, we manually restore the database. I am looking into a way to automate this task.
I would like to schedule a task to run weekly to do the full restore (which I can do with no problem) and a second task to run daily to do the transaction log restores. This is where I am hitting a snag. The file is downloaded daily, but the frequency that they actually preform the transaction logs doesn't really stay constant, so the file we get each day can contain one or many transaction log backups in a single file. I have been able to find the 'RESTORE LOG' SQL statement, where you can do something like:
RESTORE LOG [DB_NAME] FROM DISK = N'PATH_TO_LOG_FILE' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [DB_NAME] FROM DISK = N'PATH_TO_LOG_FILE' WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [DB_NAME] FROM DISK = N'PATH_TO_LOG_FILE' WITH FILE = 3, NOUNLOAD, STATS = 10
GO
This would be a perfect solution if there was a constant number of transaction log backups in the file. But since there aren't, I'm not sure what to do with this. Is there a way to query out the number of log backups that are in a file?
By the way, it's SQL Server 2000 we are running for this database.
Any suggestions would be appriciated.
Thanks,
Jason
The Redneck DBA
March 23, 2006 at 3:17 pm
I do not know how you could accomplish this with a standard t-sql script. I would say that you either need to create a vb script to check in your directory for t-log backups and dynamically create a t-sql script and run it through i-sql (or o-sql). You could also come up with a fairly complex DOS .bat file to do the same. Create a .bat file that will do a dir /B > C:\temp\backups.txt of your directory holding your backups and then read in the filenames from the text file and echo restore commands out to a file (dynamically create your restore.sql script) and use i-sql or o-sql to call the script. Check out this site for some good dos scripting help:
http://www.robvanderwoude.com/index.html
Go to batch files>>examples>> look for ReadINI.bat. This will give you good examples of how to read through a file.
Either way, you will have to create your own automation process. Good luck.
March 23, 2006 at 6:09 pm
1. Make them do a consistent number of backups.
2. check the BOL for RESTORE HEADER ONLY (and there's one other RESTORE something ONLY). One of those commands just gets you the information from the backup headers and includes the file information. Copy it to a table and query it for the MAX file number, enter that into a parameter and then do the RESTORE.
-SQLBill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply