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 |
|
nielsvdc
Starting Member
2 Posts |
 Posted - 2003-05-23 : 08:51:53
|
| After reading this great article about setting up log shipping on http://www.sql-server-performance.com/sql_server_log_shipping.asp, I thought I would also try it. Setting up the log shipping was no problem. I first tried it with northwind database and everything worked smoothly. But then I tried it on our real database (ofcourse in testing environment) which is about 9-10GB big. Backup and copying worked without any problem. But with restoring the database I had a problem. The database will stay in Loading status.I'm using the following Transact-SQL scripts (I replaced my databasename with Northwind):----- Backup job -----BACKUP DATABASE Northwind TO Northwind_Backup WITH INITGOWAITFOR DELAY '00:00:05'----- Restore Job ------RESTORE DATABASE NorthwindFROM DISK = 'D:\mssql\backup\northwind_backup.bak'WITH REPLACE,RECOVERY,MOVE 'Northwind' TO 'D:\mssql\data\northwind_data.mdf', MOVE 'Northwind_log' TO 'D:\mssql\log\northwind_log.ldf'GOWhen I try to recover the database with:RESTORE DATABASE 'Northwind' WITH RECOVERYit is giving the following error message:"File 'Northwind_Data' was only partially restored by a database or file restore. The entire file must be successfully restored before applying the log."So probably the backup file was not created well, you might think. But when I use Enterprise Manager to restore the database using the same backup file, then the backup restores and recovers good!Someone any idea what I can do? |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-05-23 : 12:03:27
|
| Did the restore complete? use WITH STATS=2to see the progress of your restore.Also, I thought for log shipping, when you restore the Full backup you are supposed to do it wit NO RECOVERY, or WITH STANDBY?-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-23 : 12:51:59
|
| Yes your secondary database will either be in a loading state or be in standby (also read-only). So your RESTORE command needs to specify WITH NORECOVERY. You do not use the WITH RECOVERY option until you need to use the secondary database in a disaster recovery situation or maybe just to test things out. You use the WITH RECOVERY option on the LAST transaction log that you need to apply.Tara |
|
|
|
nielsvdc
Starting Member
2 Posts |
Posted - 2003-05-26 : 06:18:46
|
| We not only want to use the secondary database in a disaster recovery situation, but we also want to use it for data retrieval. Unfortunatly we're using a badly created ERP system (Concorde), which uses char fields to hold salesnumber and ordernummers !!!! And we're still going to use it for sometime. So to retrieve filtered data from the database without slowing down the SQL Server to much, which slows down the ERP app, we set up a second server and want to keep this database up-to-date with log shipping, so that we can use it for data retrieval to other self created apps.So we need to have this second database online |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-27 : 12:24:28
|
| You can not use this secondary server except for read-only (also known as standby mode) access if you are going to use log shipping. You should look into replication instead.Tara |
|
|
|
|
|
|
|
|
Help!! Log shipping