Once again | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Once again


Sorry if this question seems redundant (I’ve seen several other posts addressing this same subject) But here goes… I have about 30 Databases on a Production SQL Server STANDARD Edition 2000k Instance. All Backups are performed using SQL Server Maintenance plans (Full Nightly backups and periodic Xaction log backups during business hours) I would like the ability to copy the FULL Backups to another Server – perhaps on a nightly basis – and then restore them to this other Server as a means of having some sort of Disaster Recovery. I know with Enterprise Edition, you can set up linked servers and do log shipping or replication, but I don’t have that luxury here. I also don’t have access to the MS Backoffice Resourvce Kit – which I understand has some canned scripts to do what I want to do here. My problem really is that because I don’t have control over the backup filenames, how to I automate the restore process on the second Server? any help would be much appreciated.
I’m not sure what you mean by "I don’t have control over the backup filenames" but … Our environment (restore) is very close to what you want to do. I do have the luxury of controlling the backup filename(s) so in my environment I actually do the backups to the server I want to restore on, then do my restore, then backup to tape from the non-live server. This way I don’t have to hammer the hard drive to create the backup, and then to turn around and read it later to do the movement to the other server (I use the other server as a reporting server. We do the restore nightly so all of our reports (all being about 99%) can be run from the reporting server using the day old data. I use the following script to do the restore. RESTORE DATABASE OASISProd
FROM DISK = ‘D:NextGenEMRDBBackupOASISProd.BAK’
WITH MOVE ‘BLANK_DATA’ TO ‘E:MSSQLDataOASISProd_data.mdf’,
MOVE ‘BLANK_LOG’ TO ‘E:MSSQLDataOASISProd_log.ldf’, REPLACE I would think that even if you can’t control the output filenames, you could create a Windows Scheduled task to copy the file that was output by the backup to the other server. Assuming you have access to both via shares. Schedule the move of the data to occur at some time, figure out how long it takes, and schedule the restore job to occur after you have safely copied the file. A couple of things to keep in mind is that while you will have backups of your data from your database, a lot of important things like User/logons, jobs, stored procs etc will be contained in Master/MSDB database instead of your database so you’ll need to consider how often you need to back them up and restore them to your disaster recovery server. As I say, in our environment we go through the restore more for reporting than for disaster recovery. However, we do have a disaster recovery model in place, which involves another server that is offsite in a data center and we simply use a product called Double Take which is just a hardware level mirroring solution. So we say mirror this/these directories to this other drive and give it the server/share to mirror to. It replicates any changes to our database from a hardware level to our disaster recovery server. The software also permits us to rename the other server (pretty coool) so that our clients can then simply restart the application(s) and will then start using it. Because you need to keep in mind that assuming you can get the other server up and running, you still need to have all of your applications have it accessible and quickly. The real time mirroring/replication permitted us to know that we are only down for a very short time, and that we have no missing data. Whereas, assuming you restored as of last night, you’ll then need to go through and load all of the log files, and you’ll only have data up to the point of the last log file. Just another option to consider. We also considered Log Shipping which you can look up as a possibility. Log Shipping if you haven’t read about it before, simply lets you ship updates to the logs to the other server. The other server is sitting there at a point like you would be at manually doing a restore. It says "Any more log files to load", and the ‘auto’ part of the log shipping is like you manually saying "yes here is another one now." In our tests it worked very quickly and did accomplish the goal of keeping data very up to date for the recovery server. However, in our environment we also have a lot of other files (non sql files) that we needed to be sure were up to do date, so the SQL Log Shipping approach didn’t solve it. Hope something helps,
Dalton

Thanks Dalton.. All that helps to keep things in perspective and also gives me food for thought.. What I meant when I said that I ‘have no control over the backups filenames’ is that we use SQL Maintenance plans to do our Database Backups and the MAINTENANCE PLAN names the backup files.. ie database_db_200511131947.bak This is what I have put together today to get things done here and I was wondering what you thought of it: I created a ‘Disaster Recovery’ SQL Instance on a Server other than my Production DB Server, recreated my Databases and restored the Master, msdb and Model databases from Production. I created a second set of Maintenance Plans on my Production DB Server to do a full set of User DB Backups and System DB backups to this second Server running my Disaster Recovery Instance – The backups over the Network Really don’t take any longer than the Local DB Backups that I am doing… Now that I have a set of Backups from my Production Instance on the DR Instance, I can setup and run a stored procedure that I found on another forum to restore these backups. What the Stored Procedure does is look in a directory that you specify for Backup files with in this format: database_db_.bak It will restore the most recent .bak file only and it will restore is as whatever the name is before the _db …. in other words, if it finds two files – one called database_db_200511131947.bak and one called DATABASE_db_200511121947.bak it will restore the DATABASE_db_200511131947.bak file as database as a called DATABASE Really pretty slick. The only things I really have to worry about are ‘orphaned’ users after the restore – easily fixed running EXEC sp_change_users_login ‘Auto_fix’, ‘username’ and the occasional New User – but users are easily scripted out and recreated on another Instance. Granted, this only gives me a restore point as good as the last full backup that actually made it to the DR Server… but we are running SQL Standard Edition and I understand that Log shipping is only supported with Enterprise and Developer Edition. I love the idea of another Server offsite and the hardware mirroring, but that sounds expensive and not soemthing that MGT would go for – even if we did have another site to host a Server for us!!! I’d really love to hear any thoughts you have on what I’m doing here for DR !!!!!
]]>