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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-10-24 : 09:39:44
|
| Dave writes "I have a database on a server. A full backup is performed each night. The .bak file is about 30 gigs.I need to copy this database to another server each night. At the moment I have to copy the .bak to the other server and restore it. I want to get around having to copy the 30 gig file each night by using differential backups.Any ideas how to do this?" |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-10-24 : 11:20:12
|
| It looks like you can not restore to a standby database, unless you are using transaction logs. I could be wrong, and I do not have a system to test that on, just now. How is the performance, if you restore from a fileshare on the production server? |
 |
|
|
gkrishn
Starting Member
16 Posts |
Posted - 2005-10-25 : 12:15:21
|
| Y you are doing this on daily Basis. Cant you setup a standby server or replication? |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-10-25 : 14:13:01
|
quote:
Originally posted by gkrishn
Y you are doing this on daily Basis. Cant you setup a standby server or replication?
Think outside the box. Maybe this is for a testing area. Maybe his business rules say he can't run replication... Replication, bad. Logshipping, good.  Dave,If you continue to do a complete backup nightly then you cannot get around this. To restore a dif backup you need the complete backup too. You might be able to do a complete backup once a week and dif backups nightly. That would allow you to copy the dif backups 6 nights a week and copy the full only once per week. I don't know your companies business rules so you'll need to decide what to do.Have fun,Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
schuhtl
Posting Yak Master
102 Posts |
Posted - 2005-10-25 : 14:14:49
|
| Dave,Depending on how fast your network is and how busy your db server is at night you could do something like below. RESTORE DATABASE DbNameGoesHereFROM DISK= '\\ProductionServerName\H$\MSSQL\Backup\BackupFileName.BAK' WITH REPLACE , MOVE 'DbName_Log' TO 'E:\MSSQL\Data\DbName_Log.ldf', MOVE 'DbName_data' TO 'G:\MSSQL\data\DbName_Data.mdf' |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-10-26 : 09:07:06
|
quote:
Originally posted by schuhtl
Dave,Depending on how fast your network is and how busy your db server is at night you could do something like below. RESTORE DATABASE DbNameGoesHereFROM DISK= '\\ProductionServerName\H$\MSSQL\Backup\BackupFileName.BAK' WITH REPLACE , MOVE 'DbName_Log' TO 'E:\MSSQL\Data\DbName_Log.ldf', MOVE 'DbName_data' TO 'G:\MSSQL\data\DbName_Data.mdf'
You can't have the SQL Agent running under the local system account for this solution though. Not sure if that applies or not but it's something to keep in mind.Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
|
|
|
|
|
Backup Problem