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 |
|
creane
Starting Member
11 Posts |
 Posted - 2003-08-14 : 12:17:44
|
| Hi, Im hoping to apply restores of backups and t-logs on a different reporting server. My question is bascially whats the best approach, is there a mechanism in sql server 2000 (theyre both sql2k) whereby you can quickly transfer teh bak and t-logs between the two servers, using dts for example and then apply the restore on the reporting server, or do you have to copy the .bak and t-logs across using e.g. ftp and then apply teh restore.. I know there is a mechanism whereby you can copy the entire database, but I would like to restore the t-logs at various intervals during the day to keep the data in the reporting db as "live" as possible..any help greatly appreciated..Eric |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-14 : 12:21:39
|
| Have you considered replication instead of this method?Whichever method you use, it will need to copy the files. I typically create a custom T-SQL script that does the work for me. I would not use FTP for this though unless that is the only way you can get the files. To copy the files, you could write a batch file that does the copy command, or you could run the copy command using xp_cmdshell:EXEC master.dbo.xp_cmdshell 'copy \\FirstServer\SomeShare\MSSQL\Backup\*.* F:\MSSQL\Backup'Tara |
 |
|
|
creane
Starting Member
11 Posts |
Posted - 2003-08-14 : 12:30:08
|
| Thanks for the replyWont replication put extra strain on the production server? I know that it would be the best solution i.e. a pull subscription at various intervals, but the senior dba here would be reluctant to do this, The problem is also that the machines are in two geographichally different areas..so Im wondering will the copy command suffice.? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-14 : 12:35:28
|
| Well replication won't affect the production server that much, it just has to be setup properly and tested. Just because the servers are in two geographically different areas doesn't mean that a copy command won't work. We've got offices in different states around the U.S. and since the machines are all on the same general network, I can use copy command or any other command that I want. Just test it to find out if it will work for you. You could also talk to your network administrator to find out what he/she thinks would be the best way to transfer the files.Tara |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-08-15 : 15:16:19
|
| Sounds like what you want is log shipping.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-15 : 15:33:10
|
| Log shipping won't work unless their reports are only reading data, which means no temp tables even. Log shipping only allows the database to be not accessible or in read-only mode.Tara |
|
|
|
|
|
|
|
|
restore backups on different server