December 15, 2005 at 4:44 am
Hi all,
I have 2 SQL servers 2000 under windows 2000 Server (both standard Edition) on 2 different location with a link of 1Mb between them.
My problem is that the production database (let's say the one on the site number1) should be nightly "imported" on the second server...because it will be used in "read only mode" by our customer portal website application...
What is the best way to do it ?
Nb : today, i have a dump that is made every evening on the production database (for backup purpose), which is zipped by my backup job, and is then replicated to a local backup server; which will then send it to the remote location using a product like "Double Take"...So i today have the zipped dump (size : 1Go) of the production server of main site on the second site too...
PS : today, database is a size of 15Go
Where could i find a script that could unzip the dump, restore it on the remote server and recreate all needed account/logins o nthe SQL server ?
Thanks
Florent
December 15, 2005 at 5:08 am
You can use SQL Server jobs to do this quite easly.
Create a job step that executes as a cmdexec, which will allow you to run the dos command to unzip the required file.
Then create a second job step that restores the required file using the RESTORE command:
i.e.
RESTORE DATABASE mydatabase
FROM DISK = 'driveletter:\filepath\filename'
WITH [whatever options are relevant to you]
Create a third step (or run as part of the second step) that executes master.dbo.sp_change_users_login 'auto_fix','username' for each user.
Note that the third step relies on the logins already existing, which only need to be created once.
All of this assumes that you know what the name of the file is. If the name of the file is unknown (i.e. it contains a time stamp then it's going to need to be a little more involved).
Hope that helps get you started.
December 15, 2005 at 5:26 am
Hi,
may i just create a batch file calling "osql.exe mysqlscript.sql" command to execute the restore instead of creating it into SQL itself ? i'm not good at jobs into SQL itself..
Concerning options, my problem concerning restore is that :
1) there may have users reading the database i must restore. How to force disconnect them and disallow new connections until the restore is ended ?
2) database to restore will be up and running, so i suppose i should add options to tell that i want to restore onto the same one ? where could i find a list that explains each option ?
thanks
Florent
December 16, 2005 at 1:42 am
Search the scripts section of this web site for examples of scripts for your remaining questions. I posted:
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=764
That should handle your restore for you and have seen others that should meet your remaining issues.
Andy
December 19, 2005 at 7:51 am
This setup seems to me to be a textbook case for using replication. Use transactional or snapshot replication to make a one-way copy that is updated hourly, daily or whenever. Over a 1 megabit link, the first copy will take forever for a 15 gig database, but after that it won't be a huge load unless you are changing an awful lot of data each day.
Am I missing something? Most folks just don't try replication. For one way trips to a reporting database, it's not that hard. Hard happens when you didn't design the app for replication in the first place and you try to implement Merge Replication!
Student of SQL and Golf, Master of Neither
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply