I have a SQL 2000 server that when they installed it they changed the default settings of the locations of the data files. Master included. I now want to restore master to a standard location. However everytime I do it, I then cannot log onto SQL Server. Any suggestions!!
View 1 RepliesHi Friends ...
I need your advice and help on the following problem . Say for example I have a SQL 7.0 Server A with 30 databases and i am in the process of transfering these databases to another system which is SQL Server B with 7.0 again .
Although the Databases can be copied by various means , can i restore Master database from the Server A to Server B so that my Server B will have all the database and login information as that of Server A.
As Master database is system database , it won't allow to be dropped and hence you cannot restore .
Is there any other way to restore Master database from One SQL server 7.0 to another with the same SQL version .
Any kind of assistance will be of great help to me . Many thanks in advance .
Kind Regards
Sneha
When I try to restore master, I keep getting an error message, while it is in the process of restoring, that says the connection has been broken. I assume it means the connection for single user mode. Any ideas on how to fix this??????
View 2 Replies View RelatedDoes anyone have anything that can automate the restoration of the master database in MS SQL 7? I need to do it on a daily basis on a back up server. I also need to do the 2 production databases and the msdb and model as well. Does anyone have any suggestions to keep me from reinventing the wheel?
Thanks,
Tim Davis
I'm trying to nail down some procedures for disaster recovery of our SQL 6.5 databases. If I rebuild master (or do a fresh install), then create the devices based on the output of sp_helpdevice, and then create the databases based on the output of sp_help_revdatabase, do I really need to RESTORE the master database? Are there any other objects in the master database (except for maybe some custom stored procedures) that I really need from my production server?
I've just recreated the device and database structures manually, so do I really need to restore master?
Another issue: the current wisdom that I've seen is that although databases need to be grown back in the same increments, the devices do not. Therefore, you can just DISK INIT them to their current size. Does anyone think this is a bad idea?
ALL,
When I attempt to restore a master database I am having some issues. I am using SQL Server 2000. I have set the start up option with -c -m to set the database in single user mode.
When I run the restore from enterprise manager I am get an popup window that is titled "SQL-DMo(odbc SQLSTATE:HY000)" with not message and then the database conection is lost. Thus the master datbase is appears to it was not resotred. As when I close enterprise manager and then restart the MSSQLSERVER service the database is in the same state.
Please advise on how i can get the Database restored.
Thanks
Chris
Hi.,Can restore master database and other databases from one server toother. I cant restore master database. i stared server in single usermode, and restore master database. it says successfully restored andserver stoped. I cant up the server... :(Regardsdishan
View 2 Replies View RelatedDo we need to know the database master key password inorder to restore the same from a Master Key backup ?
What if the scenario is like, I have Master Key backup file but do not have the password specified at the time of creation ?
Any help is appreciated.
satya
To restore the Master DB, BOL says to start SQL Server in single user mode.
I do this using: sqlserver.exe -m
The server appears to startup (Recovery complete message, etc.).
Then I take care to minimize the command window. The command
window now appears on my task bar.
I start EM and navigate my way to the Restore Database menu pick.
I fill out the appropriate boxes, etc. I get a window telling me that
the restore has been started.
Very shortly I get a popup window telling me the connection has been
lost. When I examine my taskbar, I no longer see the Icon for the
command line/window. Thus it appears to me that the server
running in single user mode has gone away and this explains
the reason for the window saying that the connection has been
lost.
I then start SQL Server, per normal.
I run DBCC CHECKDB. This tells me there are 0 allocation errors
and 0 consistency errors. Several thousand rows of data present.
Thus the master DB appears to be OK.
However I am unable to restore it.
Any ideas or suggestions would be appreciated.
How do I set the Master database to "single user mode"?
It is grayed out when I go to the property.
Thanks
I would like to restore the master database from a current backup tape but I got the error “Load database must be used in single user mode if trying to restore the Master database”. I tried to change the master database option but the option ‘single user’ cannot be changed for the ‘master’ database. Then I tried to start the SQL Server in Single-User Mode but I was not able to restore the master database from a tape.
Would anybody help me on how to restore the Master database like any other database?
Many thanks
Stella Liu
Hello All,
I am going through the procedure of restoring the master database as described in the Microsoft SQL 6.5 Survival Guide by SAMS. I deleted the master.dat file from c:mssqldatamaster.dat. I went into setup and choose rebuild master database. After rebuilding the master database, I went into setup again to add the "-m" server option to start server in single user mode so I can load the master database from backup. Then I went into Enterprise Manager to start the server. The server started, but when I try to double click on the server. It gave me the following
" A connection could not be established to [server name] - SQL server login failed."
I try reregistering the server, but gave me the same error. The SQL server is using standard security.
Does anybody know what I did wrong?
Pauli
Hallo
I do my backup using BACKUP database <myDB> TO DISK='D:BACKUPmyDB.bak'
I do this for ALL my databases every day.
Another product writes every day all the files on the server to tape EXCEPT the online databases because these files are open by SQL server 2000.
Now I restore the files of the tape to the server
This works well but I haven't got the online databases !!
And because the master database isn't available, SQL server wont start ...
BUT I have the backup of the master database 'D:BACKUPMaster_data.bak'
How can I use this master backup to do a restore even when SQL server can't start ?
Or must I do the bakup of the master database in another way ?
ALL,
When I attempt to restore a master database I am having some issues. I am using SQL Server 2000. I have set the start up option with -c -m to set the database in single user mode.
When I run the restore from enterprise manager I am get an popup window that is titled "SQL-DMo(odbc SQLSTATE:HY000)" with not message and then the database conection is lost. Thus the master datbase is appears to it was not resotred. As when I close enterprise manager and then restart the MSSQLSERVER service the database is in the same state.
Please advise on how i can get the Database restored.
Thanks
Chris
Our SQL Server has the databases on D drive and logfiles on the E drive. I have full database backups.
I want to move the databases over to another "standby server". However, the other server does not have an E drive. It is imperative that I be able to restore the Master database. But when I do, the SQL Server will not start, because the Master database expects the logfiles on the E drive.
Is it possible to restore Master without it being able to find what it needs on the E drive? Or in order to restore Master, does it have to find those other databases/logfiles?
I am going on the "disaster recovery" scenario. Assume that the original server has crashed, and all I have are backups. Can this be done?
Thanks....
I have a full backup from ServerA. I then put ServerB in single user mode and try to restore the backup from ServerA. (both are identical servers but have different names)
[*- From command prompt using sqlcmd -*]
USE master
GO
RESTORE DATABASE master
FROM DISK = 'D:BACKUPSserverA.bak'
GO
Msg 3154, Level 16, State 4, Server MSSQL1, Line 1
The backup set holds a backup of a database other than the existing 'master' database.
Msg 3013, Level 16, State 1, Server MSSQL1, Line 1
RESTORE DATABASE is terminating abnormally.
Any ideas on forcing the restore through T-SQL? The restore works fine from a master backup from itself (ServerB).
Not sure where to post this. How do I restore the master database to a brand new server that has a different name than the server the master currently resides on? I am using SQL Server 2005.
View 1 Replies View RelatedI've tried starting SQL from the command line in single user mode (-m). When I try to open Management Studio I receive an error that it can't connect. Can you only restore master from a command line in SQL 2005? Or can I do it from a GUI?
Thanks,
Mark
Hi everyone,
HELP!
I was wondering if someone could help me. I need to re-install the SQL Server (due to a registry corruption). What I plan to do is to:
- Detach all databases (including master, pubs, and tempdb)
- Copy databases to a different folder
- Uninstall SQL Server
- Reinstall SQL Server
- Copy the databases back
- Reattach all databases
Will that preserver all the security? (e.g. syslogins and sysxlogins) Will using an old "master" database on a new install pose any problems?
Any help will be GREATLY appreciated. Please email me at vchai@artinmotion.com
Thanks,
Vincent Chai
Can it be done?
View 2 Replies View RelatedI am trying to replicate our production SQL 6.5 environment on an isolated network for Y2K testing. I have installed SQL 6.5 on the test server, then ran sp_help_revdatabase on the production server to generate scripts for recreating the databases for testing. I used a zip drive to move dumps of the user databases from production to test. I now need to move over data from the master db, to recreate security and logins. But I don't think I can simply do a dump and restore, as I am not creating copies of all the production databases from our production server, just a few of them. Does anyone have a suggestion for a procedure to replicate the info I need from our production master database? Am I correct that restoring a dump of our production master database to the Y2K environment would be inappropriate?
Please advise.
Thanks,
Fred Smith
fsmith@physio-control.com
Physio-Control Corp.
I have 2 database servers, 1 @ production and 1 @ DR site.
To restore the master database to DR on a regular basis, I have to rely on either the DBA or SysAdm to perform the restore. It's pretty expensive, as they have to dedicate some time to do so.
I'm exploring the possibility of getting the Data Centre staff to do this instead, as they are stationed at the DR site. Please advise the SQL permisssions required to restore master database.
We are running SQL Server 2000.
Version: 10.00.1600 (I believe the first 2008 release)
I made a backup from the master, model and msdb system tables. Now I travelled to a different country, and I only have the backups and would like to access them. I was able to restore the master db to a new installation of mssql but, it wont start. I do know why: as I read after I quickly realised that master backups take the accounts, serverID, groups, directory structure, and lots of other things.Basically I have no chance of doing a master restore. The question here: Is there any way of getting my data out from the master backup file (thats where my stuff is)? I'm thinking of "restoring" it to a separate non-master db, table, anything. I know now that I did the most stupidest thing ever, but believe me I don't want the last half years work to just disappear.
I wish to restore master database which was located on "e:" on my server.
the restored copy is residing on "f:"
when i restore the backup and restart, the server does not start.
is there any way to avoid this and perform the restoration succesfully.
or the process followed by me is wrong/incomplete.
do let me know...TIA
I have this situation: -Two nodes cluster with two instances sql cluster 2012 and I need to move all on the new cluster 2012 with finally the same virtually name and instances.
It's possible to restore master order to preserve all security?
Hello,I need to restore the Master DB in my SQL 2k server (with sp3). Theproblem is that i don't have SQL backups of this file I only havegeneral backup of the entire c: drive i made using NTBackup. So itried all I know and succesfully run the rebuildm utility. Now when Itry to restore it from the general backup (not SQL backup) using"restore database master from gen_backup" i got error: "The file ondevice 'gen_backup' is not a valid Microsoft Tape Format backup set."RESTORE DATABASE is terminating abnormally.Any ideas how I can restore it?
View 2 Replies View RelatedHi All:
Please advise me about restore a master database from production to test server.
The reason to do this because I need to test and evaluate some login in master database.
I tried to restore master database to test server, but I got some errors regarding about user databases are not exists in test server. I don't want to restore user databases, I only need master database for evaluate user login.
Again thanks for your advice.
Hello,
I have a fresh install of sqlExpress and Management Studio Express on my test server. I want to restore my master database from backup.
From the command prompt I set the Sqlservr -s SQLEXPRESS -m
Then I opened another comand prompt and ran my SQLCMD script to restore the Master Database.
here is the sql script:
RESTORE DATABASE [Master] FROM DISK = N'E:COPLEYNEWSDATABASEBACKUPMaster.bak' WITH FILE = 1, MOVE N'mastlog' TO N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAMaster_1.ldf', NOUNLOAD, STATS = 10
GO
I recieve the following error.
Msg 3154, Level 16, State 4, Server COPLEYNEWSSQLEXPRESS, Line 1
The backup set holds a backup of a database other than the existing 'Master' dat
abase.
Msg 3013, Level 16, State 1, Server COPLEYNEWSSQLEXPRESS, Line 1
How do I restore a Master Database on SQL Express?
I am having problem restoring a data i backup in master database for an application upgrade. I have this error message that ' i can only restore in single usermode for master database that i should change to single user mode'
View 3 Replies View RelatedIf one is regularly taking backups of system databases, when does it become necessary to rebuild the master database. I am looking for a situation where rebuilding the master is preferred to restoring it from backup.
View 1 Replies View RelatedI have restored the master db in single user mode.
---
Processed 440 pages for database 'master', file 'master' on file 1.
Processed 3 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
---
I removed single user mode parameter(-m;)
When I start SQL Server in configuration manager getting below error
---
The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details
--
Environment is SQL Server 2000 64 bit.I restore from a script 'my' database, this works fine. However, allthe tables are also found in master, no data though.Anyone experienced this?
View 2 Replies View Related- First I made a full backup of master database
- Then I stopped SQL Server Service
- in cmd: cd C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLBinn
then sqlservr.exe -m
- I entered that single user mode
---> Problem here:
- I tried to connect to sql by sqlcmd -E --->
HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.
Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
---> I tried to connect to management studio:
TITLE: Connect to Server
------------------------------
Cannot connect to BISHOYSERVER.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'OFFICIALPROJECTAdministrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)