Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sindle@ensco.com
Starting Member
5 Posts |
 Posted - 2007-02-06 : 15:38:35
|
| I have 2 separate servers. Server2 is live with a few people editing the data (from an Access front-end via odbc). It is also serving the SQL database via the web and ASP.It is a relatively small operation (a couple a hundred web hits per day) and is not mission or time critical.We would like to do something that seems relatively easy--Keep a backup of the database on Server1. We would like to copy it over in the wee hours every morning. I am fairly new at SQL server.We were going to do the following:Drop databaseX on Server1Do a Copy Database package, created by the Wizard that copies databaseX from Server2 to Server1.This seems to work fine on a dummy database I set up, but did fail if there were any connections to DatabaseX that were live, during the copy.Am worried that the copy will fail if someone tries to Hit the databaseX via the web during the backup. Should I take the DatabaseX offline and do this? If so, how? Is there a better way to do this?We don't care if the database is unavailable for 5 or 10 minutes every morning.Thanks in advance. |
|
|
propanecan
Yak Posting Veteran
60 Posts |
Posted - 2007-02-06 : 15:41:54
|
| One option might be to backup your database on server2 (live server) and restore the backup to server1 in the morning. As the backup operation can be done dynamically, your online users should not affect the outcome. However, your server1 restore operation must of course have exclusive access to your database. |
 |
|
|
sindle@ensco.com
Starting Member
5 Posts |
Posted - 2007-02-06 : 17:29:53
|
| Again, kinda new here. WHen trying to create the backup of DBX on Server2, I tried have it write to:\\Server1\d$\SQL_BACKUPS_FROM_Server2But it wouldn't let me (said directory didn't exist). So I'm guessing the db would have to be backed up to Server2's filesystem. So I tried this and the backup on Server2 succeeded. THen I had to copy the files to Server1 and do the restore from there. Worked OK, but was not very automated.Questions:1. I couldn't get Server2 to write the backup files to Server1 directly.2. I couldn't get Server1 to be able to restore from files on Server2 (so I wouldn't have to do the copy).3. The copy database wizard allows us to tell the copy "not to copy logins." We don't want the logins copied. But the backup database procedure didn't give me any opportunity to "not copy logins." Any ideas here?Thanks in advance.Rob |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-07 : 03:49:25
|
| In my opinion your best bet is to backup locally, and then COPY the file to the other server.That way if the COMMs link (or anything related) fails you will still have a backup on the main server.If you are using a SQL Scheduled Task for this job it will be running with the permissions of the SQL Agent service - which may not (currently) have access to Server2?Kristen |
|
|
|
|
|
|
Database backup (via copy)