August 17, 2009 at 2:20 am
Hello,
I have a client that wants to deploy a log shipping on a database (geographical log shipping). Once the database is at the destination box, we need to have a way to copy the database on the same server, while keeping the original database on the log shipping scheme.
I found a way and it works very well when there is no secondary data files:
Here are the main principles.
1. I create a copy of an empty database, B (keeping the same logical file names for all the files and using the physical names from the table master_files (mdf, ndf and ldf files) from the database in standby A.
2. I shut down the MSSQL service (downtime is allowed with non problem).
3. I copy the original files from B and replace the files from A with them.
4. I restart the the MSSQL service.
If there is no secondary file(s), it does work well, if there is secondary file, it puts the database in a state that is not available (not suspect state just unknown state where he cannot connect to the secondary files, even though the file name is correct)
I think this is because the secondary files are linked to the primary files.
Now I know you will tell me, why you do not replay the logs from the original log shipping and get the copy. That is a solution indeed but I want to avoid this one if possible.
Any thoughts?
Thanks.
Clement
August 18, 2009 at 1:58 pm
No idea, but I am curious. Why are you doing it this way?
August 18, 2009 at 2:50 pm
A client request.
The client wants to be able to do rebase of the production database onto a development box.
They have already the database in a clustered server but they want to have this one in case the cluster does fail as well.
It is a lot but I am ok with this.
While having the database on a log shipping and having the state on standby, I can copy the database on a recovered version so that the developer can work on several copies if they want and also having a rebase the next day if they want.
Of course I can replay the logs but I like to use this solution that does work very well if I do not have secondary files (ndf).
Clement
Clement
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply