11-14-09, 23:10 #1Registered User
- Join Date
- Mar 2002
Unanswered: Sql Server in Stand by mode? Really? Help!?!
I am trying to carry-out a full database restoration in standby mode on my Sql Server 2008 (on Windows 2003 Datacenter Edition R2 SP2). After this, I then have to restore about 62 transaction log files; after which my client intends to send me 4 transaction log files everyday (with which I intend to keep our database up-to-date). The aforementioned full backup file is about 140 Gb's and each transaction log file averages about 10 - 20 MB's in size.
Problem is, I only just realized that I cannot restore the database in RECOVERY mode (I did this 4 weeks ago) and then switch it back to NORECOVERY/Stand-by (today) so that I can apply all the subsequent transaction logs :-(. So I decided (or tried) to do another full database backup restoration (today) in Stand-by mode (while keeping the database accessible but in READ-ONLY mode), so that I can apply all subsequent transaction logs as they come in on a daily basis.
this was my original restoration attempt:
RESTORE DATABASE [Easygrants_RBI] FROM DISK = N'F:MSSQL10.MSSQLSERVER\MSSQL\Backup\Easygrants_RB I_backup_200910270200.bak'
WITH STANDBY = N'F:\MSSQL10.MSSQLSERVER\MSSQL\Log\UNDO_Easygrants .DAT',
MOVE N'Easygrants_v53' TO N'F:\MSSQL10.MSSQLSERVER\MSSQL\Data\EasyGrants_RBI _Data.MDF',
MOVE N'Easygrants_v53_log' TO N'F:\MSSQL10.MSSQLSERVER\MSSQL\Log\EasyGrants_RBI_ Log.LDF', NOUNLOAD, REPLACE, STATS = 10
I got this error:
"This backup cannot be restored using with standby because a database upgrade is needed....."
I think the original database whose backups I'm trying to upgrade is Sql Server 2005.
Is there anyway around this problem without upgrading the 2005 Sql Server (of which I have no control over). Can I apply 2005 Transactional Log files to a Sql Server 2008 database in stand-by mode? without having to do an upgrade???????
11-15-09, 05:35 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
No, because a database upgrade (from 2005 to 2008) ir required, you can't apply log files after the restore.
You can "fake it" by:
1) Setting up a named instance of SQL 2005
2) Restoring a read only copy of the client database on the named instance.
3) Creating a SQL Agent job to copy the database from SQL 2005 to SQL 2008
Not pretty, but it would get you what you want.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.