October 24, 2002 at 9:41 am
Hello,
Please give me some advice on data backups.
I am a developer and manage about 20 SQL Server databases on a remote server using PC Anywhere. What I want to do once a week or so is "sync" my local copy of of the databases with the live data on the remote server. I current accomplish this by deleting my local copy of the database, disconnecting the remote copy, copying the mdf and ldf file from the remote machine to the local machine, then I attach the database. Then I recreate the necessary users. As you can see, this is a nightmare to do with 20 databases.
Q: How can I simplify the above to accomplish the same thing?
Thanks for any help.
- Ken Otto
October 24, 2002 at 4:34 pm
With many forms. You can make backups on the remote servers and then restore them in your local pc. Or you can set a snapshot distribution. Or script the database structure and import the data with bcp.
Or using a DTS package.
October 25, 2002 at 11:39 am
If the users are changing on the primary database I don't have a good answer. If they're not, and just the data is changing here is how I'd do it:
Write a maintenance plan to make the databases make a full backup into a common directory on the remote server. You can then zip them into one big file, download them to your local machine, and restore them into the local copies. That way you don't have to take the primaries offline at all. You can even write a DTS Job to do the restore for you.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
October 28, 2002 at 2:32 am
Thanks for the replies.
For BobAtDBS - I like the plan you presented. The users never change, for example I will have created a user "WebApp" on both machines. However I still have a question. When I bring the copy of the database online on my local machine, I need to created the user "WebApp", then recreate it. Am I creating my users wrong? Is there a way to share users between SQL Servers?
Cheers - Ken Otto
October 28, 2002 at 3:37 pm
Try this script for each user in the restored db to sync server logins with database users.
DECLARE @db sysname, @uid sysname
select @db = DB_NAME()
--First add normal user --
SET @uid = 'db_dser'
IF NOT EXISTS ( SELECT * FROM master..syslogins WHERE loginname = @uid )
BEGIN
EXEC sp_addlogin @loginame = @uid,
@passwd = '',
@defdb = @db
END
IF EXISTS ( SELECT * FROM sysusers WHERE name = @uid )
BEGIN
EXEC sp_revokedbaccess @uid
END
EXEC sp_grantdbaccess @loginame = @uid,
@name_in_db = @uid
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply