May 25, 2010 at 7:57 am
Hello,
DBA Newbie here...
I have a log shipping scenario setup between two SQL Server 2005 servers. I would like to bring the secondary databases online, move connections to this server and take the primary offline, but I'm not quite sure how to do that. There are 12 databases that are all in Standby / Read-Only mode on the secondary server. Would the below be appropriate steps?
1) disconnect users from the primary so no new updates are made
2) log ship the final trn logs (jobs run every 15 minutes, but I could manually run the jobs to create, copy and restore)
3) bring secondary DBs out of Standby / Read-Only mode using the below on each of the 12 DBs:
restore database DATABASENAME with recovery;
4) take off line the primary server's DBs
Any other steps or SQL commands I'm missing?
Thanks,
Shawn
May 25, 2010 at 8:05 am
Hi,
Yes; And make sure to transfer the logins and map them accordingly on the databases.
Thank you
[font="Verdana"]Renuka__[/font]
May 26, 2010 at 4:15 am
Dear All,
I have configure the Log shipping on my Two SQL Server 2005 Machine. Every Thing goes fine , All log file are transferring from Primary to secondary server , I have made some change in the Primary Server ie ( Creation of Table , Insert Some Value in that table ) Now i want to restore my Secondary Database.
The Secondary database is showing in Restoring state ,
Please help me that how can i restore my secondary database..
Thank in Advance
Basit Khan
Jn DBA
GLT - Mumbai
May 26, 2010 at 8:40 am
Hi,
The changes you mentioned (Creation of Table , Insert Some Value in that table ) will be reflected through log shipping.
Once you recover the database (from restoring mode), the logshipping is broken and it needs to re-configured again. If that is OK, apply the last transaction log backup file manually at the secondary server 'WITH RECOVERY' option.
If the it is already applied, then issue the command
RESTORE DATABASE 'DB NAME' with RECOVERY
GO
The database will be online. Make sure to check and the logins once the DB comes online.
Thank you
[font="Verdana"]Renuka__[/font]
May 26, 2010 at 12:40 pm
Renuka,
Thanks for the reply. My conversion from the Primary to Secondary server went well this morning!!
Shawn
May 26, 2010 at 11:40 pm
Dear Renuka...
I have Restore the Secondry database with the Query as you have said . The Database restore succesfully ... but the change that i have made in the primary databse has not refecled.. As i have created a "Name" table in primary database all log of Primary database are copy to secondry DB. Now when i use select statement in Secondry DB it give a error : Msg 208, Level 16, State 1, Line 1 Invalid object name 'Name'
Can you tell me why the change has not replicate in Secondry DB
May 26, 2010 at 11:52 pm
Thanks Reunka ,
For your Kind Help...
Basit...
June 3, 2010 at 3:56 am
Now there is no logshipping between pri & sec. U have to reconfigure it.
June 3, 2010 at 5:27 am
[font="Verdana"]@Basit - There are all possibility that the jobs failed or log sequence was broken.Assuming that you have already stopped the log shipping what ideally you should do is take a differential followed with log backup with norecovery...and once that is done restore it on the secondary ...it should bring your secondary in line with prod and then you can complete the swing activity...
In general you should follow the following steps for effective logshipping swing.
1.Ensure all scheduled backups have completed successfully
2.STOP/DISABLE LOG SHIPPING BACKUP JOBS
3.RUN (LOG SHIPPING) TRAN LOG COPY AND RESTORE JOBS ON Secondary until the last log is applied then DISABLE all copy & restore jobs.
4.REMOVE THE LOG-SHIPPING FROM PRIMARY SERVER’S MAINTENANCE PLANS for the required Db's
i.Go to maintenance jobs folder ON Primary
ii.delete destination server information from inside the maintenance job for each DB.
iii.remove logshipping & delete maintenance job
5.Kill all users IN Required database on primary server
6.BACKUP LAST TRAN LOG On Primary Server and place them is easy accessible folder Eg.(\\Primary Server Name\C$\MSSQL\Backup\FTRN)
eg. BACKUP LOG Check21DB TO DISK = 'C:\MSSQL\BACKUP\FTRN\' WITH norecovery
7.COPY LAST LOGS TO Secondary Server to say ‘FTRN’ folder (\\Secondary Server \C$\MSSQL\Backup\FTRN)
E.g
xp_cmdshell 'copy \\PriamryServer \C$\MSSQL\BACKUP\FTRN\*.trn \\SecondaryServer\C$\MSSQL\Backup\FTRN'
8.RESTORE the above LAST LOGs for each database ON Secondary with recovery
RESTORE LOG DBNAME
FROM DISK = '\\SecondaryServer\C$\MSSQL\Backup\FTRN\xxx.trn'
WITH RECOVERY
and once all the logs have been applied you can make setup Log shipping on Secondary.[/font][/font]
~RD
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply