March 19, 2008 at 10:38 am
All:
I am running into an issue that I need some assistance with. My goal is to create a set of scripts that I can re-use for implementing replication using mirroring. The purpose of the script that I have so far is to allow someone to be able to state where they want to create the backups and other information so that they end up with a set of instructions and commands that they need to run on the partner server. I ultimately hope to make it so that the backup file transfer can be done as well, but that is down the road.
Ok, so the issue is this. I have discovered that in my script that if I run the initial DB Backup and then a subsequent backup of the Log File that I can not restore the Log File on the partner server. If however I create a backup using the SQL Server GUI, then all I have to do is restore the backup... it is not necessary to create the log file backup and restore it. Also, if I script the backup of the database, then move it to the partner server, restore it, then go back and run the script to create the log backup... move that... then restore it on the partner server it works! Why?
My question is... what is SQL Server doing differently then what I have written in my script?
Additionally, I am following the steps in the following article: http://msdn2.microsoft.com/en-us/library/ms189047.aspx
In that article they clearly state that the log file must be backed up and restored on the partner server... what I don't understand is why this is necessary when we are scripting the solution and not when we use the GUI? I have additionally run the profiler on the Partner server to see if there was any difference in the commands executed and I did not see anything that was obvious.
It's a work in progress... so please bear with me. 🙂
--==============================================================
--DB Mirroring Script
--
--Mark Anderson
--Note: If this helps you, use it! Just send me some work! 🙂
--
--These are the steps necessary to create a back up of the DB and
--Log file necessary to setup replication.
--Steps Taken from: http://msdn2.microsoft.com/en-us/library/ms189047.aspx
--Make sure that you have the approprite directory location available
--on the server that you are creating the backups on!
--==============================================================
declare @DBName varchar(50)
declare @BackupDir varchar(50)
declare @RestoreDir varchar(50)
declare @sql varchar(2000)
declare @DataLocation varchar(100)
declare @LogLocation varchar(100)
--Change this name to the DB that you are working with
set @DBName = 'MirrorTestDB'
--Change this to the location of where you want to save the backups to
set @BackupDir = 'C:\DBMirroring\'
--Change this to the location of where the backups were saved to on the other server
set @RestoreDir = 'C:\DBMirroring\'
--Change this to the location of where you are restoring the data files to
set @DataLocation = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\'
--Change this to the location of where you are restoring the data files to
set @LogLocation = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\'
print '--Setting the Recovery Model to "FULL" on @ ' + CONVERT(varchar,GETDATE(),113)
USE master;
EXEC('ALTER DATABASE ' + @DBName + ' SET RECOVERY FULL;')
print ''
print '--Backing up Database: @ ' + CONVERT(varchar,GETDATE(),113)
set @sql = 'USE ' + @DBName + ' BACKUP DATABASE ' + @DBName +
' TO DISK = ''' + @BackupDir + @DBName + '_DBBackup.BAK'' WITH FORMAT'
exec(@SQL)
print ''
print '--Backing up the Log file for Database: @ ' + CONVERT(varchar,GETDATE(),113)
set @sql = 'USE ' + @DBName + ' BACKUP LOG ' + @DBName +
' TO DISK = ''' + @BackupDir + @DBName + '_LogBackup.BAK'''
exec(@SQL)
print ''
print '--====================================================================='
print '--Next Steps:'
print '--1. Migrate the backups to the other server where the Mirror DB will reside'
print '--2. Restore the database from the backup. Make sure that you are using the NO RECOVERY Option!!!'
print '--3. Restore the log backup.'
print '--4. After restores have completed setup the Mirroring by performing the following:'
print '--4a. Right click on the database on the PRIMARY server'
print '--4b. Click on Properties'
print '--4c. Choose Mirroring from the options available on the left hand side of the properties window.'
print '--4d. Configure the security.'
print '--4e. Do NOT START Mirroring yet!'
print '--4f. Choose which level of data protection you want. If you chose to have a witness server then you have 3 avialable options.'
print '--4g. Start Mirroring.'
print '--'
print '--For Steps 2 & 3 the following output is the scripting that you need to run.'
print '--Run these on the server where the restore will be completed!'
print '--If you need to know the logical names of the backup files run the following command:'
print '--RESTORE FILELISTONLY FROM DISK='''+ @RestoreDir + @DBName + '_DBBackup.BAK'''
print ''
print '--If you need to remove the Mirroring run the following command:'
print '--ALTER DATABASE ' + @DBName + ' SET PARTNER OFF'
print ''
print '--MANUAL RECOVERY'
print '--To use the DR database, simply remove the Mirroring from the database and then restore the'
print '--database state. Basically what you are doing is to take the database from "Restoring" mode'
print '--to a normal operating state.'
print '--RESTORE DATABASE ' + @DBName + ' WITH RECOVERY'
print '--====================================================================='
print ''
set @sql = 'RESTORE DATABASE ' + @DBName +
' FROM DISK = ''' + @RestoreDir + @DBName + '_DBBackup.BAK''' +
' WITH REPLACE, NORECOVERY,
MOVE ''' + @DBName + ''' TO
''' + @DataLocation + @DBName + '.mdf'',
MOVE ''' + @DBName + '_Log'' TO
''' + @LogLocation + @DBName + '_Log.ldf''';
print @sql
--exec (@SQL)
print ''
set @sql = 'RESTORE LOG ' + @DBName +
' FROM DISK = ''' + @RestoreDir + @DBName + '_LogBackup.BAK''' +
' WITH NORECOVERY'
print @sql
--exec (@SQL)
March 20, 2008 at 4:42 am
I don't want to take the wind out of your sails, but have you considered Log Shipping? In previous versions of MSSQL you had to do some scripting (similar to what you are writing at the moment) to get it working, but in MSSQL 2005 it is all built in and fairly simple to configure. You could simply use the built-in SPs, such as:
sp_add_log_shipping_primary_database
As for your specific problem, there is no fundamental difference between backing up the DB and Logs using the GUI or using scripts. I suspect that something is happening with the LSNs (Log Sequence Numbers).
March 20, 2008 at 7:50 am
Andy:
I think you are right about the LSN thing... but what is confusing is that if I simply use the backup that was created with the GUI then I can start mirroring right away. If however I create the backup with the script, it demands that I provide a log backup. There has to be something different about the backups that MS is not advertising. Further more, it is necessary that I not run the backup of the DB and Log at the same time, I need to restore the DB backup, then go back and get the log backup.
If anyone can explain what the difference here is I would really appreciate it.
-Mark
March 21, 2008 at 2:29 am
You could, as an experiment, run SQL Server Profiler in the background whilst backing up the database via the GUI. You should be able to see exactly how the GUI is executing the backup.
You may find that the default settings, via the GUI, set a flag (eg. NORECOVERY, NOINIT) which your script does not. Perhaps you are not correctly moving the logical files to the required file path?
Having said that, it would still be difficult to explain the scenario you are experiencing.
You said "Further more, it is necessary that I not run the backup of the DB and Log at the same time, I need to restore the DB backup, then go back and get the log backup." If I am interpreting this correctly, you have to restore the DB on the Mirror (with NORECOVERY, obviously), then go back to the Principal, backup the log and then restore the log (again, with NORECOVERY) on the MIRROR, for your scripts to work?
Are you sure that a log backup does not occur whilst you are restoring the backup on the Mirror? What frequency is your log backed up? Are you sure you are doing full backups, not incremental/differential backups?
Just some thoughts which might point you in the right direction.
Andy
March 21, 2008 at 8:24 am
Andy:
Good thoughts... I will take a look again at the profiler when I get a chance. I did however do exactly that when I was trying to figure this out but I did not run it on the Primary... I thought that the issue would have been on the Partner server with some commands that I was not issuing correctly on the restore.
You are correct in the interpertation that I had to go back to the Primary and then run the backup of the Log, then bring that back across. One thing that I noticed in using the GUI to perform backups was the option to create a "new media set". I wonder if this is the clue to what was going wrong with my restores that I did using the scripting. I "think" that the scripted restores create new media sets, and when running the backup through the GUI it does create new media sets unless you explicitly specify to. So... I am currently trying this to see if this is the real cause... I will post back what I find on that. I am trying to understand if whether this GUI version of the backup keeps logging information while the scripting version does not. I know there is a way to look at the file set, i.e. what is in the backup... I will have to look at both versions to see if I see any difference.
Thanks for your input on this!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply