Restoring Master And MSDB To A New Server
Nov 10, 2005
I have a server attached to a SAN, all user db's are on the SAN, master, tempdb, and msdb are on the local machine. We will be replacing the machine (not the SAN) to a newer sql server. Our plan was to create the new server with same name, deattach current user db's,backup master and msdb, then connect new server with same name an IP to SAN. I then wanted to restore the master and msdb db's to the new machine, and attach the user db's. We are running log shipping to a stand-by server in this configuration. I was thinking I could let last tran log backup and copy occur, and then let the last restore happen on the standby. I would then shut down the agent. Should I not be able to restore the MSDB and master to the new server (as long as all file folder locations are the same) and be ready to go? Or are there issues when restoring the Master and MSDB to a new server from a different server.
View 5 Replies
ADVERTISEMENT
Jul 20, 2005
I'm trying to rebuild from a meltdown. I'm using disk backup files. Icould have sworn that I had SQL Server 2000 SP3 installed on the oldmachine, but maybe not.With SP3 installed, I get a message along the lines that the restorecan't be done because the backup was created using server version134218262 and this server is version 134218488.I've searched the documentation and can't find any reference to thoseversion numbers.In any case, I thought SQL Server 2000 could restore backups createdon any SP level to any SP level.Any ideas?--Regards.Richard.
View 4 Replies
View Related
Nov 7, 2007
I noticed when I restore a master database to a server other then the one which created the backup of master, SQL Server contains the following three local security groups that were defined on the source server. The problem is these groups are "local" and do not apply to the server where master was restored.
ServerNameSQLServer2005MSFTEUser$ServerName$InstanceName
ServerNameSQLServer2005MSSQLUser$ServerName$InstanceName
ServerNameSQLServer2005SQLAgentUser$ServerName$InstanceName
For example, if you have a default SQL Server instance named MARKETING_TEST the security folder will contain the following three entries.
MARKETING_TESTSQLServer2005MSFTEUser$MARKETING_TEST$MSSQLSERVER
MARKETING_TESTSQLServer2005MSSQLUser$MARKETING_TEST$MSSQLSERVER
MARKETING_TESTSQLServer2005SQLAgentUser$MARKETING_TEST$MSSQLSERVER
If you then backup the master database on an instance named MARKETING_PROD and restore it to MARKETING_TEST, the security folder on MARKETING _TEST will now contain the following three entries.
MARKETING_PRODSQLServer2005MSFTEUser$MARKETING_PROD$MSSQLSERVER
MARKETING_PRODSQLServer2005MSSQLUser$MARKETING_PROD$MSSQLSERVER
MARKETING_PRODSQLServer2005SQLAgentUser$MARKETING_PROD$MSSQLSERVER
These entries would be invalid because no such server exists and therefor no such local groups exists. There appears to be no Microsoft documentation explaining how to handle these groups when restoring master from one server to another. My assumption is that whenever restoring master to another server you must drop these three groups and add the correct corresponding groups along with the appropriate permissions. I don't understand why SQL Server would not rebuild this information for you during a restore.
Any explanations?
Dave
View 8 Replies
View Related
Jun 1, 2008
moving to a new sql server box because of a problem with the SAN its connected to.
started my named instance in single user mode and restored master. sqlserve.exe -c -m -s ovops
now the instance won't start. tried starting it with the -t3608 switch.. won't start!!
Its because my drive configuration is different on the new server than it was on the old server, I cannot start the instance because it is expecting model, msdb, temdb and all of the user databases on drives that don't exist?? what can I do?????
View 4 Replies
View Related
Oct 6, 2006
Hi,
I'm having trouble with restoring the master db on w2k3 sql2000 sp3a to a Secondary Standby server.
Firstly, I place the server in Single user mode and then restore the master db. The sql server then tries to restart sql services but immediately stops as soon as it starts up.
Can anyone help ?? Please !
Thx, Steve
View 1 Replies
View Related
Jan 24, 2002
Hi,
I am trying to build a test environment for our production server. I backedup and restored all my databases. I had to copy the jobs too, so I backedup and restored msdb database. When I try to change any of the jobs, I get the following message.
"Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server."
I have no clue what this message is about. Can someone please provide me with some clues about how I can modify my jobs and get my test environment to work.
Thanks,
Rob
View 2 Replies
View Related
Apr 18, 2006
I need some data from msdb.dbo.sysjobs and msdb.dbo.sysjobhistory but I need it from a backup file.
I don't need to restore the msdb database, I just need some data from the backup. Everytime I try to restore it under some innocuous name like bills_db it tells me msdb is in use.
Is there a way to restore this db to some other name and retrieve data from it?
View 2 Replies
View Related
Nov 10, 2000
QUESTION? ----!!!!!
I BACKED MSDB TO A DEVICE AND THEN RAN THE FOLLOWING CODE ON ANOTHER SERVER:
RESTORE FILELISTONLY
FROM DISK = 'd:mssql7ackupcomplete_bak.bak'
RESTORE DATABASE Test_MSDB
FROM DISK = 'd:mssql7ackupcomplete_bak.bak'
WITH MOVE 'MSDBData' TO 'd:mssql7datamsdbDATA.mdf',
MOVE 'Northwind_log' TO 'd:mssql7datamsdbLOG.ldf'
I get the following error: (Why are the logical and physical name referring to master database?)
LogicalName PhysicalName Type ...1
-------------- ------------------------- ----------- ...2 continued
master F:MSSQL7DATAMASTER.MDF D ...3 below
mastlog F:MSSQL7datamastlog.ldf L ...4
1... FileGroupName Size MaxSize
2... --------- --------- ----------
3... PRIMARY 11206656 35184372080640
4... NULL 1310720 35184372080640
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 3
File 'MSDBData' is not a database file for database 'Test_MSDB'.
Server: Msg 3013, Level 16, State 1, Line 3
Backup or restore operation terminating abnormally.
-------------------------------------------------------------------------
QUESTION? -- WILL BACKING UP MSDB AND RESTORING IT TO ANOTHER SERVER MOVE
ALL MY JOBS AS WELL? IF NOT HOW DO I MOVE MY JOBS FROM ONE
SERVER TO ANOTHER?
View 1 Replies
View Related
Dec 12, 2002
I restored msdb on another server after moving all user databases and logins. I expected to see the jobs after the restore but I didn't.
I've read the article(Q314546) about moving databases and it indicates that you don't have to script them if you move msdb.
I didn't restore master. Only msdb.
Your thoughts please?
Sidney
View 3 Replies
View Related
Oct 13, 1999
We're just in the process of moving to 7.0, although SQL 7 has the autogrow feature we'd prefer to size MASTER and MSDB properly in the first place to avoid disk fragmentation. Does anyone have an experience/suggestions as to appropriate sizes, particulary for MASTER?
View 5 Replies
View Related
Mar 22, 2001
Hi All,
I am working on a third party client product which shrinks all the databases in MS SqlServer based on the rule or parameter that is set. This product will be used in production server which has MS SqlServer 7.0.
My question is: Is it advisable to shrink Master,Model and Msdb databases in an production environment? Please give the reasons.
Thanks
Sri
View 3 Replies
View Related
Mar 1, 1999
Hello:
I am in the process of setting up a dbcc checkdb and dbcc newalloc job for the user database in production. Do I need to include master and msdb to have checkdb and msdb run against them daily?
Any information you can provide will be greatly appreciated. Thanks.
David Spaisman
View 1 Replies
View Related
Jun 29, 2007
Will I encounter any issues running DBCC CHECKDB on either msdb or master?
Thanks!
View 1 Replies
View Related
Aug 25, 2007
I have inherited a SQLS erver 2000 instance where the client neverimplemented a backup startegy for the "master" and "msdb" databases.MSDB is now showing errors and my only option for a restore is from atape backup of the server.Any thoughts..
View 1 Replies
View Related
Nov 3, 2003
When SQLserver2K was installed it placed master, model, msdb, tempdb data files in the installation location (i.e. C:Program Files....). This puts pressure on the C: drive, which also holds the page/swapfile. I want to move at least the tempdb location to the new 'Default data directory' and log directory we set after installation (i.e. E:MSSQLData).
How do I get tempdb to relocate to E: given that it gets recreated each time SQLserver starts?
TIA,
Al
View 1 Replies
View Related
Mar 18, 2008
How to move master , model and msdb database to some other location. Can we overwrite the master, model and msdb files
View 8 Replies
View Related
Sep 21, 2000
How does one correctly go about restoring the MSDB database?
How/what should I do with the status (loading)?
I have a good backup of the MSDB.
Any help will be appreciated.
View 2 Replies
View Related
Jul 20, 2006
Hi Guys,
Since,You cannot mirror the master, msdb, tempdb, or model databases.What will happen if i create new login,change existing security profile and new jobs,change of existing job on princicpal db. how these will be mirrored to other server and in case failover, how it will treat.
Thaks
View 1 Replies
View Related
Sep 16, 2015
Suddenly one day I found some corruptions have occurred in my SQL Server 2012 installation. Because my 'msdb' has been marked SUSPECT/CORRUPT; hence no new work can be done as per my schedules.
I have gone about trying to repair it. I followed this article here: [URL] [the region 'Create new MSDB Database'].
I am facing some sizeable difficulties in doing this on my own. Steps 1. & 2. I have done. I am now stuck at step no. 3.My instance name is SQLEXPRESS. So, when I give,
SQLCMD -E -S<SQLEXPRESS> -dmaster -Q"EXEC sp_detach_db msdb" [as per the article], what I get is:
The System cannot find the file specified.
So I am stuck at that point. What's with the 'cannot find file specified'? The stored procedure, or the mdf, ldf files for my msdb? What is it?Also, if and when I am through with step no. 3 I would like to know about steps 5. & 6. also which are soon to follow [like how to do them correctly, safely and from where, the different options]. This is a huge priority for me to get my sql server up and running again because I can't do my other coding works without it. Everything is stalled. Slowly reaching the desperation, SOS mode..
View 7 Replies
View Related
Nov 20, 2007
Hi all
What is best Disaster/Recovery methode to securing the system databases.
Master database allows only Full backup
MSDB allows Full ,Diff
View 5 Replies
View Related
Oct 10, 2014
On a 2008r2 server, I ran the frag utility against master and msdb and noticed they were severely fragmented.
Is it ok to defrag them using the standard Alter Index command?
View 7 Replies
View Related
May 3, 2004
Could anyone guide me how to restore Master, msdb to different server from the backup files.
thanks in advance.
View 3 Replies
View Related
Aug 30, 2000
Iam planning to move the production server from one server to another. Can any one tell me how to do the migration of the master,model and msdb database. Help in this regard is highly appreciated.Is it ok if I do the dts for these three databases Thanks
Naveen.
View 2 Replies
View Related
Sep 24, 1999
In SQL 7 Books Online, the suggested method for disaster recovery is to restore the master database along with msdb and user databases.
I am testing this and have an issue restoring the master database. I start SQL in single user mode connected as sa. Using Enterprise Manager to initiate the restore I get the following error:
Microsoft SQL-DMO (ODBC SQLState:01000)
ConnectionTransact (GetOverlappedResult())
Connection broken.
This stops SQL Server, and prevents subsequent connections and the only fix is to rebuild the master database.
Any ideas?
Although user databases can be recreated and restored without restoring the original master, configuration, security and user logon information is lost without it.
Thanks.
View 1 Replies
View Related
Nov 19, 2001
HELP
We are preparing Disaster recovery plans, and I need to recover the SQL servers. When testing this, I have run REBUILDM, and then set sql to single user and attempted restore of master. I receive error messages:
"2001-11-16 15:36:24.33 spid6 Getpage: bstat=0x9, sstat=0x1, cache
2001-11-16 15:36:24.34 spid6 pageno is/should be: objid is/should be:
2001-11-16 15:36:24.34 spid6 (1:988)/(1:988) 6/30
2001-11-16 15:36:24.84 spid6 ... IAM indicates that page is NOT allocated to this object
2001-11-16 15:36:24.88 spid6 Error: 605, Severity: 21, State: 1.
2001-11-16 15:36:24.95 spid6 Error: 3151, Severity: 21, State: 0
2001-11-16 15:36:24.95 spid6 The master database failed to restore. Use the rebuildm utility to rebuild the master database. Shutting down SQL Server.."
Subsequent attempt cause the same, UNLESS i reapply SP2 (the version running when the Master was backed up)
Is this a requirment or am I missing something??
Thanks in advance
View 2 Replies
View Related
Sep 18, 2002
Hi all, I'm hopeful that someone can help.. I'm working with my DRP scripts, and am having some trouble.
First I run Rebuildm with no issue, sql works fine (btw we're on SQL7 SP2)
then I start sql in single user mode
sqlservr -m
next, I attempt to restore my master db from a dump, and receive the following error:
Getpage: bstat=0x9, sstat=0x1, cache
pageno is/should be:objid is/should be:
(1:988)/(1:988)6/30
... IAM indicates that page is NOT allocated to this object
Error: 605, Severity: 21, State: 1.
Error: 3151, Severity: 21, State: 0
The master database failed to restore. Use the rebuildm utility to rebuild the master database. Shutting down SQL Server..
PLEASE HELP!
View 10 Replies
View Related
Nov 16, 2006
Everytime I attempt to restore the master database, it corrupts the entire instance of SQL server 2000. I have been using the method out on Microsoft's Knowledgebase:
http://support.microsoft.com/kb/822852/en-us
using sqlservr.exe and the switches -c -m -T3608 -T4022
I would like to know if anyone has other methods of restoring the master database from a backup device.
View 2 Replies
View Related
Jun 21, 2004
Hi all,
I am trying to restore my master database to a new server in sql 2000. I have started sql in single user mode from the command line and connected to enterprise manager and attempted to do the restore. I also tried query analyzer and used the restore database from disk ='file path of backup' used the move option with the correct file path and on both counts received the same error message...
Error: 9001, Severity: 21, State: 1.
Error: 3151, Severity: 21, State: 0.
Any ideas? The connection is then broken (which I would expect anyway as restoring master shuts down sql but, I checked and it has not restored the db)
Thanks!
View 4 Replies
View Related
Dec 20, 2005
I am running through a Disaster Recovery scenerio and I am having trouble restoring the Master and msdb DBs to a recovery box.
My production is a default Instance on WIN2k Server - SQL Server is 2000 Standard, sp3a
The box I'm trying to recover to is a Named Instance on WIN2003 server - SQL Server 2000, sp3a
I stopped/restarted my recovery server with -m and -T3608 startup parameters but when I attempt to restore the master backup from my production box to the recovery box, I get an error - it's a blank dialog box with a big red X and it says: Microsoft SQL-DMO - ODBS SQL State - HY000
when I check the sql server log: it shows the Server started in single user then it says recovering master db then starting up master DB then it just hangs..
Server event viewer shows this: 18052:error 3151, severity 21, state 0
not much to go on.. does anyone know what I'm doing wrong???
View 5 Replies
View Related
Aug 20, 2007
I am trying to restore master to a new server but location of data and log files is different in this new server. The previous location was 'D:Program FilesMicrosoft SQL ServerMSSQLdata" the current location is in the C: Drive; when trying to restore using MOVE:
RESTORE DATABASE master FROM DISK ='\PfileserversqlbackupMDFfilesmaster_db_20070 8170121.BAK' WITH RECOVERY ,
MOVE 'master' TO 'C:Program FilesMicrosoft SQL Serverdatamaster.mdf',
MOVE 'mastlog' TO 'C:Program FilesMicrosoft SQL ServerMSSQLdatamastlog.ldf'
I get the following error message:
The system database cannot be moved by RESTORE
Is there a way you can restore master to a different location?
Thanks,
Carlos
View 1 Replies
View Related
Apr 19, 2006
We set up a test box and having been trying to move an entire instancefrom our production server to this one. I have moved all my createddbs using the RESTORE WITH MOVE. Now I am trying to move the Master,Model, and msdb. This is where I am having trouble. On the productionbox the dbs are stored on D:mssql$instancenamedata. On the testserver the data is stored in C:Program FilesMicrosoft SQLServermssql$instancenamedata. Also the server names are different.So I finally got the Master from production to restore over the test'scopy. Now the instance will not start. I was trying to use thefollowing code in cmd line to connect to the instace so I could thencopy model and msdb:sqlservr -c -f -T3608 -T4022 -sINSTANCENAMEWhen this is trying to connect I read it is failing and saying that theMDF and LDF may be corrupt or not there. The problem is it is nowtrying to look in D:mssql$instancenamedata instead of C:ProgramFilesMicrosoft SQL Servermssql$instancenamedata. Any ideas on how Ican change this, or if I have to reinstall the entire instace, not runinto this again?
View 2 Replies
View Related
