01/16/2004 Mssql2

[soky]

I would think that once you have made a carbon copy of the table structure in your local DB... DTS scheduled can accomplish your backups automatically as often as you like.

The other method I would look into is a "pull" publication... sorry I can't help with that though.

--- I have been trying to come up with this scheme as well... it would appear that DTS with a scripted data transformation is the best way.

step one: create a duplicate database locally. (Careful with indexes and keys)
step two: Create a DTS package including and data transformations.
Step three: Schedule the DTS to run every night at 2:30 am (or at a time convenient to us all)
Step four: Create a back up schedule for your local database that coincides with your DTS run.

Now you have a working copy of your database locally + a backup version if needed to restore on another SQL server.

These are just my ideas based on a recent issue I was forced to resolve... what ever you chose; I would appreciate hearing about your solution.

[Brangwyn]

Originally Posted by newmem

thanks......but can AspEnterpriseManger help me change the value without running a query? Like EM?

Newmem, ASPEnterpriseManager will let you change your data thats no problem

EM is not possible to use at all, even if I could connect it takes too long to show the db and not everyone has got fast and stable net access.

But once the initial waits over it should be quite usable, I've used it on 56k modem connections before when out of town and never had any real complaints.

My issue still remains, how do I make a backup of my clients databases. I have SQL Server running locally on my development machine. I have EM. I have tried to do a full export (generating DTS)

Never had a problem exporting a DB via DTS from Vortech, you are logging in with the customers credentials aren't you ? .. you also need to make sure you don't copy security objects or settings and create the customers login on your local SQL Server as well.

[DatabaseMentors]

Actually my error message when attempting to copy via DTS was saying that I didn't have access to database XYZ while I am logged in as DBO for MYDB. I have no idea where it gets the database XYZ from, when in EM I can manipulate objects in MYDB without any problems.

I suppose I should make a trouble ticket about it, but honestly I was so fustrated with responses I had received during the last outage, my energy was better spent building a way to work around the limitations that seem ever changing. (Now I am working on explaining to my clients about POP disappearing.)

Thanks for telling me that I SHOULD be able to use DTS and that a trouble ticket should be able to fix it up for me.

Scott Friend
Database Mentors

[Brangwyn]

If MYDB is a different separate db to XYZ, then unless you've set them up to use the same logon you will get permission errors. Each logon normally relates to an individual database, when your granted dbo rights it will only be for the database with which that logon is associated. Don't confuse having the dbo role assigned to a logon with having full dbo rights on the sql server, the latter you'll never get in a shared environement for obvious reasons.

But yes DTS should be no problemo.

[antic]

Just a heads-up... I installed Microsoft SQL Web Data Administrator (see post #77) and from that time I couldn't use Enerprise Manager to connect to either my local SQL server databases or the ones in my live accounts. The error I was getting back was "SQL Server does not support encryption."

[edit]My apologies, it wasn't that, it was the previous version - "Web Data Admin" found at http://www.microsoft.com/downloads/d...displaylang=en (see post #83) which caused this problem. I have reinstalled the later version from post #77 and it is working ok. Don't install the version in post #83.[/edit]

Uninstalled it, still no go... had to do a System Restore back to just before it was installed, then everything worked ok again.

Perhaps it installed a different certificate on my machine or played with encryption settings... just letting you know in case you have the same problem.

If this is indeed caused my SQL connection problem, I would advise extreme caution if Vortech thinks of installing this on one of the SQL servers, in case we all suddenly experience problems connecting.

[Brangwyn]

Yeah haven't had any problems myself with the version I posted so far, been messing around with it for a day or so now.

If the latter is the case, then the Matrix will have to run the MSI on every one of their Windows servers to register the COM object that's needed... read on...

Just rereading some earlier posts in the thread you've probably already realised I think that it doesn't get installed on the windows boxes, just the SQL Servers will do it.

[antic]

Weird problem with System Restore tho... in the process it seems to delete any files you have downloaded with IE! So when I restored back to the day before yesterday, several downloaded files disappeared. If it's not one thing it's another...

[newmem]

has there been any official word from Brad or the others about this yet?