SQL Server Performance Forum – Threads Archive

SQL2000 restoring to another SQL2000 server

Hello, I am bit new to this so bare with me please. I have two SQL2000 servers. One of the servers is a "live" production database that is backed up. The second SQL2000 is a test server. I would like to update the second SQL2000 server from the backups of "live" production server – Restores. I would like to automate this process too. Can anyone point me in the right direction on this? Any available scripts or location to show me how? Thanks…[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

You can find out in BOL: Replication.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I don’t think that’s replication — it’s just a restore We do that all the time — it’s pretty simple to set up, depending on how fancy you want the automation to be. Our setup allows us to run a script that’ll locate the most recent backup file and restore it automatically — the only tricky part is the bit that locates the lastest file. Other than that, all you need is basically a restore statement; see BOL Topic "How to restore a database with a new name (Transact-SQL)" for the syntax. The only other tricky part I can see is that you might need to have the same logins (or a subset of the logins you need to use) on the test server where you are restoring, otherwise you could have an authentication issue. If you need to preserve all the various logins for the second server, then read about sp_help_revlogin at http://support.microsoft.com/kb/246133/
————————–
Yes, I am a geek. I know. I can’t help it.

"I would like to automate this process too."
May be Cogs have to explain that.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks for the quick information. I really appreciate it. What I meant by automate is after the backup on the "live" SQL server completes, have a script (or Job) that runs to restore that database to the "test" SQL server. Without pressing the go button (in so many words). Just trying to keep it simple.

Good point, Luis — I think a job with a restore statement in it would do that. Or, to be on the safe side, a job that a. locates the backup file, b. kicks out any users that might be in the target db, and then c. performs the restore. Or replication <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />, as you suggested. ————————– Yes, I am a geek. I know. I can’t help it.

Why not take help of Log shipping in this case, it will automate all the backup and restore operations without any problems. http://www.sql-server-performance.com/sql_server_log_shipping.asp for a good start. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>