August 30, 2007 at 6:21 pm
Hello
I am a Oracle DBA now entering the world of Sql Server. I tried out backing up and recovering the non-system / user databases (full followed by applying of transaction logs). In my setup master is set to full recovery mode, model is full recovery mode and msdb is set to simple recovery mode. As a result of this only model gets is transaction log backed up.
I read the contents of the following URL too:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q285288
I have another server (development box) where in master, model, and msdb are present. How do i restore the system databases from production on to the test box and test it? My apologies if my question seems trivial.
Thanks
August 30, 2007 at 7:13 pm
To restore master: http://msdn2.microsoft.com/en-us/library/aa173515(SQL.80).aspx
To restore msdb, model: you restore these databases as any other db and after you restore the master db.
Stop SQLServer Agent when restore msdb.
It's not necessarily to backup the log of model.
August 31, 2007 at 6:29 pm
Hello
Thanks for the quick reply. I am trying to test the recovery of the master db.
I read through the following link(s)
http://msdn2.microsoft.com/en-us/library/aa173515(SQL.80).aspx
http://www.dbarecovery.com/restoremasterdb.html which is from
The problem i am facing is when i start C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmangr -c -m it brings up the usual Server Starting pop up screen (from this i am not able to make sure if the databases will be in single user mode) and is start the service. When i connect to the Enterprise Manager (EM) , for model and msdb it shows the Single User mode check box. But for some mysterious reason, those boxes are greyed out for master. Hence i am not able to put master in Single user mode (or is it already in Single User mode??); I also do not know if there is any command option i can give to put the master db in single user mode from the Query Analyzer like
use master
sp_something(dbname here) which will put the masterdb in single user mode.
When i try to recover from backup, i get the message
RESTORE DATABASE must be in single user mode when trying to restore the master database.
I would appreciate any help in this regard.
Thanks
September 3, 2007 at 10:59 am
When you start the server with sqlservr -c -m, the server is in single-user mode, so all system databases are already in single-user mode.
From EM, just right-click the master database, select all-tasks -> restore datbase, select your backup file and click ok. It will give you a message that the restore is complete and it will stop the server.
If you do it from query analyzer, run this:
restore database master from disk=
September 4, 2007 at 6:27 am
Coming from a similar background. I am questioning the NEED to restore the system databases. I expect that you really don't need to. Can you detail why you think that you need to restore the system databases? People do put functions, procedures etc in the system database (there are reasons to do that) but it is rare that you need to restore it for the reason that you are explaining. (development).
The users, privs and such can easily be moved (see sp_rev_login)
September 4, 2007 at 1:16 pm
MDaniel
I will try this and get back to you. Thanks
September 4, 2007 at 1:17 pm
I am in the process of establishing a Recovery case scenario just in case the hardware crashes and i need to recreate the SQL Server db
September 4, 2007 at 4:53 pm
Most of the time you can get away by restoring only user databases and scripting logins and any other custom user procedures/function stored on master.
* Noel
September 9, 2007 at 2:20 pm
Thank you Noel & Others who have responded to my questions.
1. In Oracle, we can take a logical backup of the database using 'exp' command which will get the data + packages / procedures , role, grants, user logins ; However this is not a full backup to be used for recovery. In Sql server, is there any such provision? If not how do i regenerate all the sql statements? i am sure it would be stored in system database somewhere. Can someone point me to the URL where in i can find system views / sp pkgs and there structure / options of the sp pkgs?
2.Also, what are the differences between master , msdb and model dbs? Why 3 of them. I suppose they three would need to be backed up together.
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply