January 31, 2014 at 2:55 am
Hi Experts,
This could be a laymans query, please help.
We are planning to move a StandBy/Readonly mode Database to a new server. This DB is not configured for log shipping. What are the ways I can do that please share your ideas..?
After moving to new DB we would like to put this into StandBy Mode again.
January 31, 2014 at 3:02 am
Why is the DB in Standby? Why does it need to go back in Standby?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2014 at 2:52 am
Detach / Attach will not work on Stand by / Read only database.
What you are trying to achieve?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 11, 2014 at 7:37 am
USE master
ALTER DATABASE myDatabase SET offline
ALTER DATABASE myDatabase MODIFY FILE (name=myDB_filename, FILENAME='X:\New_Path\myDatabase.mdf')
ALTER DATABASE myDatabase MODIFY FILE (name=myDB_logname, FILENAME='X:\New_Path\myDatabase_log.ldf')
** Physically move the files at this point to their new locations specified in the MODIFY FILE statement **
ALTER DATABASE myDatabase SET ONLINE
... all done. Files moved and your database is still in Standby / Read Only mode.
*** [Edit] Sorry, my mistake, just saw that you wanted to move it to an entirely new machine, not just a new drive.
February 13, 2014 at 4:55 pm
Brad Scheepers (2/11/2014)
USE masterALTER DATABASE myDatabase SET offline
ALTER DATABASE myDatabase MODIFY FILE (name=myDB_filename, FILENAME='X:\New_Path\myDatabase.mdf')
ALTER DATABASE myDatabase MODIFY FILE (name=myDB_logname, FILENAME='X:\New_Path\myDatabase_log.ldf')
** Physically move the files at this point to their new locations specified in the MODIFY FILE statement **
ALTER DATABASE myDatabase SET ONLINE
... all done. Files moved and your database is still in Standby / Read Only mode.
The OP wants to move the database to a new server so this will not work.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2014 at 4:56 pm
Ravid_ds (1/31/2014)
Hi Experts,This could be a laymans query, please help.
We are planning to move a StandBy/Readonly mode Database to a new server. This DB is not configured for log shipping. What are the ways I can do that please share your ideas..?
After moving to new DB we would like to put this into StandBy Mode again.
If it's not part of log shipping why do you have it in standby mode?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2014 at 8:30 pm
Agree with everyone is saying about the standby...seems like we're missing something.
To answer your question, simple take a full backup on the current server, then restore it to the new server. Done.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 14, 2014 at 1:01 am
MyDoggieJessie (2/13/2014)
To answer your question, simple take a full backup on the current server, then restore it to the new server. Done.
A database in StandBy cannot be backed up. Hence my earlier question about why the DB is in standby, what the purpose is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2014 at 2:58 am
If you really want to move the database and put in standby mode using a backup you'll need to perform the following on the source server
RESTORE DATABASE yourdb WITH RECOVERY[/CODE]
BACKUP DATABASE yourdb TO DISK = 'DRIVE:\PATH\FILENAME.BAK'
Move the backup file to the new server and then
RESTORE DATABASE yourdb FROM DISK = 'DRIVE:\PATH\FILENAME.BAK'
WITH STANDBY = 'DRIVE:\PATH\FILENAME.TUF'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply