June 28, 2007 at 2:17 am
Help appreciated.
We are trying to create a copy of a database for MI purposes (SQL Server 2005).
The environment is a two-node cluster and the original database is on one instance, whilst the intention is to put the MI Copy on the second instance. So far ok, but trying to create this as a repeated job daily is hitting problems.
Using the 'Copy Database' wizard fails on execution due the presence of what I view as reserved works in the original database (i.e. Userid as a column name). As the database is a vendor supported structure we do not have much sway to change this issue (and I have tried!).
Using the Import Data wizard works as a one-off (first time), but when trying to schedule it, it fails due to what I view as temporary files created by the wizard not being around. I did try saving these temp file to another directory and getting the scheduled job to look there for them, but even though I have changed the job, when you go back in to verify things it has reverted back to the original temp file location. It also appears to have access issue, even though I am in theory running it under SA authority?
If anyone has any ideas on overcoming these issues or another method I can try, that would be great.
Thanks
June 28, 2007 at 3:11 am
You do you copy the whole database. You have the option of database snapshots in SQL 2005 that can be a consisntent copy till the point the snapshot was created and can be used for reporting purposes. Also database snapshots occupy less space in disk than your database files
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 28, 2007 at 3:35 am
I thought Snapshot was for Enterprise versions (we are not using enterprise for this clustered set up as it's not needed for the two node setup).
I have had a separate feedback from a guy at Microsoft, with regard to Set Quoted_Identifiers on, with a view to [] delimiting the reserved word column names.
No joy with that yet.
Thanks for the prompt reply BTW.
June 28, 2007 at 4:11 am
Database snapshots are supported on all the editions of SQL 2005. Use that instead of copying the database to other server or so. I too knew that SQL 2005 2-node clustering can be done with standard edition and that it does not require a enterprise edition for the same.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 28, 2007 at 9:23 am
That is incorrect. Database snapshots are only available in the Enterprise and Developer Editions.
You have a lot of options available to you: snapshot replication, log shipping, full backup & restore.
June 30, 2007 at 12:27 am
Robert I agree. Snapshot are available with only Enterprise and Developer Editions. Sorry for the wrong post.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 2, 2007 at 12:50 am
Thanks for the Snapshot suggestion.
I set that up last week after the post, and so far it appears to cover the requirements nicely.
Thanks for the comments. So nice to put out a shout for help and get constructive feedback.
July 3, 2007 at 4:36 am
Snapshot process is working fine. Daily publisher/distribution tasks completing.
However, I now have a related problem.
The database being used as the source here is a vendor supported structure. Periodically we will get upgrades that may include dropping and recreating tables. The problem is, the upgrade fails as it detects the table is in a snapshot replication arrangement.
I don't want to have to drop and recreate the snapshot details each upgrade, so is there anyway to disable the existing details to allow the vendor upgrade of the database, then re-enable it and run a re-initialisation at the publisher/pull at the distributor?
Thanks
July 4, 2007 at 5:27 am
if thats the case you would be better off using nightly backup\restores. This will be simpler to maintain and not be affected by any application upgrades.
horses for courses.
---------------------------------------------------------------------
July 4, 2007 at 7:05 am
Running snapshot replication is like your nightly backup/restore and easy to implement too than your backup and restore as there is a chance that your restore may fail if a users still accesses the database when its in progress.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 4, 2007 at 7:07 am
There is a option to re-initialize subscriptions with replication you can read more about replication and its options in BOL.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply