Can you restore the master database, running on windows server 2000, sql
server 2000 from a backup that was taken from c: to another server
restoring to d:?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Hi All,
I took a backup of master,model and msdb database which was on D: drive and i tried restore it on C:. Now if i try to start sql server it comes out with a erorr that model database cannot be found and it keeps looking for model in D: drive which is not there.I tried restore of model with move option but it says cannot drop model as its system database.I tried attach detach but it does not understand tempdb as temp db is also on d: and i cannot understand how to move or point it to c:
Suggestions will be greatly appreciated
Vishal
All,
I'm writing to ask if anyone knows whether or not MS SQL server stores in any system tables the association between a database and the drive letter/directory path where its corresponding MDF/LDF files are located.
Thanks,
Isaac
All,
I have a test server that needs the drive letters changed for the data, log and backup drives. Is there a way to make this change without reinstalling SQL Server?
Future guru in the making.
As part of a migration of data to a new SAN I have hit a bit of a snag in the migration. In summary what will happen is user database data files will be moved from one LUN (say drive F:) to a new LUN (say drive G:). Once all the data is migrated, plan is to remove dependency of that drive from SQL server and remove the drive and delete the LUN. So far, so good.
However one of the LUNs (drive D:) destined to be deleted also hosts the instance default directories, i.e. everything under MSSQL11.MSSQLSERVER (Data, Backups, FTData, JOBS, etc). BOL has articles on how to migrate system databases, including tempdb. But there is no guidance that I could find on how to relocate other folders. There are forums where users have listed registry changes, etc that can achieve this but these are steps I am unwilling to take on a production server.
So my plan is:
1) Add new drive to cluster (drive E:), sufficiently large enough to host instance default folders
2) Shutdown SQL server
3) Copy all default folders to new drive
4) Swap drive letters so that new drive is now D:
5) Start SQL server and if everything works, delete the original drive (which is now drive E:).
Hello - I have a SQL 2000 server which has a D: drive that contains all of my databases (system and user). I am running out of space on this volume and need to migrate the contents of this volume to a larger one. My initial plan was to introduce a new volume to the server (say a K: drive). Backup all databases (of course), and then stop all SQL services. Copy all data from D: to K:. Once data is copied, swap drive letter names (D: to I: and then K: to D. Then restart SQL services. SQL should not know any better since everything was on the D: drive when it went down, and everythiing is still on the D: drive when it came back up, correct?
The other option mentioned is to detatch the databases, copy the data and then reattach them in their new locations. I understand this method, but it seems more involved (and riskier) than just renaming the drives. Does anyone have an opinion regarding these two migration methods? Thanks for your help.
Chris
Pls explain how to backup and restore data on SQL Server with a CD drive instead of tape drive.
This is urgent.
Thanks.
Is this possible? I can only seem to restore when the *.bak is on a local drive which is ridiculous. I'm sure this translates to other operations in addition to restoring databases...
View 7 Replies View RelatedI have a 'need an opinion' question. I've always installed the system databases (master, msdb, model, tempdb) on the root drive (c:) with the sql application. Then I was thinking, the only things that really should be on the root drive is the sql app itself (and any other required apps). So...my question is, do y'all install the master, etc on a separate drive other than the root, ie d: or e:? The reason I ask is that if the tempdb database has alot of activity, and the database resides on the root drive, could it affect sql performance?
Just wondering. I just started working in a 'virgin' sql environment (setting up sql servers from scratch), and want to have sql installed in the best possible way for performance.
Thanks in advance for any info y'all can provide.
WHY master, msdb, model always seem to be on the " C " drive ....
View 4 Replies View RelatedCould anyone guide me how to restore Master, msdb to different server from the backup files.
thanks in advance.
Hello.
One of our hard drives has crashed and as a result we have lost our master mdf/ldf & user db mdf/ldf files. It's not that a critical system by any means, but if the hard drive crashes and the master mdf/ldf files & user db mdf/ldf are lost, is there any way of restoring the system?
I'm thinking we probably need to re-install Enterprise Manager completely, and re-install the user db from a backup.
Any advice/suggestions would be much appreciated.
Thanks in advance,
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.
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.
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
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!
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.
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!
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???
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
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 RelatedWe just recently found out that our production database (SQL Server 2005) was running on an evaluation license and that it would expire very soon. So we went ahead and bought a license for SQL Server 2005 Enterprise Edition and installed it last night. Before we started, we did full backups of SQL Server so that we could just restore the DB. Then we uninstalled the evaluation version and installed the enterprise version. However, when we tried to restore the master database, we got an error stating that the build/version number of the master database restore and the target SQL Server instance were different and that the restore could not be done. We decided not to try too hard and just manually entered the data into the already existing master database.
Well, we did not discover until this morning that our encrypted data was not decrypting because the service master key was not matching the one that was used to encrypt our data. We realized that we do not have any backups for the service master key and we do not know the user credentials that were used to create the original service master key.
We tried http://support.microsoft.com/kb/264474 already, but we could not get past step 6. We got the build/version mismatch error.
My question is does anyone have any ideas or suggestions on how to either get the master database to be restored or to get the service master key extracted? Our business is basically at a hault until we get this data encrypted.
Thank you in advance for your help.
I am experiencing a lot of problems trying to restore the master database for a mock Disaster situation. I started SQL server in single mode using
sqlservr.exe -m at the command prompt and then tried to restore the master database but was unsuccessful. Does anyone know the steps of how this can be accomplished.
Thanks
Kevin
Hi Guys,
I am trying to restore a master database from a server to another server. I followed all the step necessary to restoring the Master database. I first rebuild the master and then put the master in single user mode and then trying to restore the master database. The problem is the database says master database is restored success fully and also it shut down the service. When I restart the server it startup and then after some minuite it doesnt show started status in the services panel. when I refresh the services it still doesn't say started. I also try to connect to the database and it says the server is not available. I have added service pack 3a to this new server. Don't know why it says that. Any have any idea. I have to do this immediatly and please guys, let me know any solution to this problem. I really appreciate your help. Thanks again. Cheers!
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 View RelatedHi All,
I have a production issue should be attended immediatly.
We are running SQL Server 2000 in a production environment. The master database is corupted. We have a backup of Master database. But the server service is not starting. I don't know how to restore the master database from the backup when server is not up. Can u please letme know how to do it ASAP.
I really appreciated for your grate.... help.
Thanks in advance.
Jamy
Hi Everyone, I'm hoping someone can help me.
We've had a SQL login deleted (special application one) and I understand the best way I can get it back, is to restore the Master database. Is this true ? All the manuals are working on the fact that Master has become corrupt & that it should be rebuilt before restoring. Ours is not corrupt, so am I ok to skip rebuilding it first ?
Cheers
I am attempting to restore a master database backup to a new location
(new_masterdb) and am getting the following error:
System.Data.SqlClient.SqlError: There is already an object named 'sysnsobjs' in the database. (Microsoft.SqlServer.Smo)
There is empty so I am not sure why it is finding this object. How can I get around this?
I needed to take a backup of a database and use it on another server and followed the steps in the following article to do so:
[URL]
It restored fine on the destination but I still need to use the original and I get the following error from the application when trying to use it:
create a master key in the database or open the master key in the session before performing this operation.
This is after doing the following to try and restore the master key.
USE <database>;
GO
open
master key decryption by password = '<password>'
RESTORE MASTER KEY
FROM FILE =
'C:exportedmasterkey'
DECRYPTION
BY PASSWORD = '<password>'
ENCRYPTION
BY PASSWORD = '<password>';
GO
After doing this I got the message:
The old and new master keys are identical. No data re-encryption is required.
What else do I need to do?
I have to restore 'master' database in sql server 2005. The server has two instances. As part of restore strategy, I need to bring server in single user mode. When I ran following in command prompt
sqlservr.exe - m -s via Binn Folder I got following error message.
Your SQL Server installation is either corrupt or has been tampered with (SQL Registery version not same as server). Please uninstall then re-run setup to correct this problem.
I have been clueless how to resolve this problem. Your help will be appreciated.
Mike
I'm trying to recreate a sql database on another server and I installed sql server 6.5 on a server and then restored the master db using the Sql Setup program. It worked without any errors, but it did not create the loginthat will use the database that I have not restored yet. Aren't the Logins storied in the master database?
Thanks.