June 18, 2009 at 8:06 pm
Pretty sure you can setup log shipping using a Standard Edition versions of SQL Server, you just have to do all heavy lifting yourself, no wizards.
June 19, 2009 at 1:41 am
I find very userfull document step by step to do http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx
But when I restore DB
RESTORE DATABASE database_name
FROM DISK = 'g:\mssql7\backup\database_name\database_name_backup_device.bak'
WITH
DBO_ONLY,
REPLACE,
STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
MOVE 'logical_name' TO 'h:\mssql7\data\database_name.mdf',
MOVE 'logical_name' TO 'f:\mssql7\log\database_name_log.ldf'
WAITFOR DELAY '00:00:05'
EXEC sp_dboption 'database_name', 'single user', true
I get this error of SP sp_dboption
Error is : Server: Msg 5066, Level 16, State 1, Procedure sp_dboption, Line 470
Database options single user and dbo use only cannot be set at the same time.
I use SA only for both DBs
June 19, 2009 at 4:53 am
thang_ngo_2002 (6/19/2009)
EXEC sp_dboption 'database_name', 'single user', trueI get this error of SP sp_dboption
Error is : Server: Msg 5066, Level 16, State 1, Procedure sp_dboption, Line 470
Database options single user and dbo use only cannot be set at the same time.
I use SA only for both DBs
use this to set database in single user mode
alter database database_name set single_user
with rollback immediate
June 19, 2009 at 6:06 am
I use "alter database ... set single_user with rollback immediate" instead of "EXEC sp_dboption.."
But when I restore LOG:
RESTORE LOG NW2
FROM DISK = 'c:orthwind_log.bak'
WITH
DBO_ONLY,
STANDBY = 'c:\NW2\undo_NW2_log.ldf
I get error:
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
June 19, 2009 at 6:08 am
change the database context to master database and then run restore log...
June 19, 2009 at 6:17 am
I already switch to Master DB in SQL Analyzer before I get this error :sick:
June 19, 2009 at 6:19 am
it should run... 🙁
try executing alter database mydb set single_user with rollback immediate and then try restoring the backup again...
June 19, 2009 at 6:25 am
When I rerun I get this error
Server: Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'NW2' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
I check in Enterprise Management, Current Activity, Lock/Process ID. I see that DB is locked by TSQL "alter database NW2 set single_user with rollback immediate"
After I kill it, I can RESTORE LOG
June 19, 2009 at 6:27 am
may be someone had fired a huge insert/update query which was taking long to rollback....
June 19, 2009 at 6:37 am
No, it's small test DB that I created, no one than me use it
After first time I run BACKUP LOG/RESTORE LOG successfully, the next time I get error:
Processed 1 pages for database 'NorthWind', file 'Northwind_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.107 seconds (0.014 MB/sec).
Server: Msg 3101, Level 16, State 2, Line 4
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 4
RESTORE LOG is terminating abnormally.
I try to run again I get error:
BACKUP LOG successfully processed 0 pages in 0.063 seconds (0.000 MB/sec).
Server: Msg 4305, Level 16, State 1, Line 4
The log in this backup set begins at LSN 23000000023700001, which is too late to apply to the database. An earlier log backup that includes LSN 23000000023400001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 4
RESTORE LOG is terminating abnormally.
How to continue to BACKUP LOG/RESTORE LOG?
June 19, 2009 at 6:51 am
i hope this is what you're doing. Backup of ur primary db
Backup database yourdb to disk = 'path\myFullBak.bak'
Restore this full on ur secondary
restore database yourdb from disk='path\myFulldb.bak' with standby
insert/delete/update into ur primary db
backup log on ur primary.
Backup log yourdb to disk = 'path\mylogBak.bak'
Restore log on secondary.
restore log yourdb from disk = 'path\mylogBak.bak'
I've restored the log in normal mode so that u can query the secondary database.
Can you post all code that u've used to backup ur primary and all restore code as well as the sequence u're firing them..
June 19, 2009 at 7:00 am
Yes, your way is the same as mine
I test with Northwind DB and backup to NW2 DB in the same server
1) Backup and restore DB
BACKUP LOG Northwind WITH TRUNCATE_ONLY
WAITFOR DELAY '00:00:05'
BACKUP DATABASE NorthWind TO DISK = 'c:orthwind.bak' WITH INIT
WAITFOR DELAY '00:00:05'
RESTORE DATABASE NW2
FROM DISK = 'c:orthwind.bak'
WITH DBO_ONLY,REPLACE,
STANDBY = 'c:\NW2\undo_NW2.ldf',
MOVE 'Northwind' TO 'c:\NW2w2.mdf',
MOVE 'Northwind_log' TO 'c:\NW2w2_log.ldf'
2) Backup and restore LOG
BACKUP LOG NorthWind TO DISK = 'c:orthwind_log.bak' WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'
RESTORE LOG NW2
FROM DISK = 'c:orthwind_log.bak'
WITH
DBO_ONLY,
STANDBY = 'c:\NW2\undo_NW2_log.ldf'
I want to run step 2 several times to NW2 get the latest transactions as Northwind
June 19, 2009 at 7:16 am
Can you perform backup/restore in norecovery/recovery modes. I've changed ur queries a bit.
1) Backup and restore DB
BACKUP DATABASE NorthWind TO DISK = 'c:\orthwind.bak' WITH INIT
RESTORE DATABASE NW2
FROM DISK = 'c:\orthwind.bak'
WITH
norecovery,replace,
MOVE 'Northwind' TO 'c:\NW2w2.mdf',
MOVE 'Northwind_log' TO 'c:\NW2w2_log.ldf'
2) Backup and restore LOG
BACKUP LOG NorthWind TO DISK = 'c:\orthwind_log.bak'
RESTORE LOG NW2
FROM DISK = 'c:\orthwind_log.bak'
WITH
recovery
June 19, 2009 at 7:43 am
Do you mean restore in recovery mode?
I follow document: set DB Recovery model Full and use norecovery mode
June 19, 2009 at 7:46 am
yes restore in norecovery mode. and you cannot backup log if your source database is not in FULL recovery mode.
restore the full backup in norecovery mode and restore the log backup in recovery mode as i mentioned in the queries in last post. then query the new database to see if everything is fine.
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply