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?
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?
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?
changing master database location in sql server 2000
HELLO PLEASE HELP ME I AM TRYING TO CHANGE THE LOCATION OF MASTER DATABASE ( MDF AND LDF ) AND ERRORLOG FILES I HAVE TRIED EDITING STARTUP PARAMETERS IN ENTERPRISE MANAGER I FAILED ,I TRIED IN COMMAND PROMPT "SQLSERVR.EXE " I SUCCEEDED IN CHANGING THE DATA FILE AND ERROR LOG BUT UNABLE TO CHANGE THE LOG FILE PATH FROM DEFAULT LOCATION C:PROGRAM ............ TO D:DATA PLEASE GIVE ME THE PROCESS TO CHANGE DATA ,LOG AND ERROR LOG FILES of master database thanq in advance
When I select All Actions>Backup Database and then click the ... button to choose a location on the hard drive, SQL Enterprise Manager Hangs and I have to kill it from Task Manager.
All other areas of EM browse the disk drive without a problem. (DTS, File Groups, etc.)
I had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).
ALTER DATABASE [User_DB] MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf') GO
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 .
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??????
Does 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?
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?
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!!
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
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.
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
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.
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 ?
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
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.
I'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?
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
I 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.
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.
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?
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.
