Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

restores Expand / Collapse
Author
Message
Posted Sunday, May 25, 2008 7:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 18, 2013 9:50 PM
Points: 119, Visits: 650
I have a database on my sqlserver 2005.
If due to any of the reason my server crashes.So what i have to do.

My solution is create another instance and restore the available fullbackup. During this process is there any need to stop the services and after restoring we have to start the services. is this approach correct one. plz guide me.After restoring how about the logins and users. will they able to login to sql server.

Suppose if i have two databases. If one databse is corrupted we can restore it with the available fullbackup of database.my question is while restoring we need to stop the service sor not.
If we stop the services then the second database will be offline.Is there any solution for these type of situations
Post #506324
Posted Sunday, May 25, 2008 10:29 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 26, 2012 2:14 PM
Points: 402, Visits: 598
For all the user databases and model,msdb you need not stop the services. Its just a normal restore. But inorder to restore the master database you need to start Sql server in single user mode and then perform the restoration.

For your scenario if your server goes down you need to perform the following,

1. Reinstall Sql Server 2005 and apply all the service packs as it was before.

PS: You need to have the same build and same edition of Sql Server 2005 to restore the system databases.
eg.) if your full backup was taken when you were using Sql 2005 SP2 with cumulative hotfix 2 you need to reinstall Sql 2005 SP2 cumulative hoptfix 2 to perform the restoration.
Refer this KB for more info, http://support.microsoft.com/kb/264474

2. Restore all the user databases from most recent full backup
3. Restore msdb and model from the most recent full backup
4. Stop Sql Server and start it in single user mode and restore the master database. Refer Step 5 alone from this link for more info,
http://sql-articles.com/index.php?page=articles/rebuildsysdb.html
5. The users will be there once you restore the user databases and the logins will be available once you restore the master database.
6. There might be mismatched ids when you restore any database and in that case you need to make use of sp_change_users_login stored procedure to fix the same.


- Deepak
Post #506369
Posted Monday, May 26, 2008 10:18 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Sunday, January 29, 2012 1:45 AM
Points: 710, Visits: 1,284
u can use:
cluster server, log shipping, database mirroring or so..


..>>..

MobashA
Post #506528
Posted Tuesday, May 27, 2008 12:31 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, June 30, 2014 8:31 AM
Points: 647, Visits: 760
Hi,
This question bugs me a little bit, If i understand it correct then the users are placed in the user databases but the logins (eg windows groups) are stored in the master database.

Then if i still right, should one also make daily backups of the master database as well .???
Are there any special methods for performing such a backup, or can we use the same as for user databases?

I every book i read i notice a lot of warnings to make sure to backup your databases but still haven't found one that warns to make also master database backups.

can someone make this clear for me please.?
Tnx in advance,
Eddy
Post #506695
Posted Tuesday, May 27, 2008 1:12 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Sunday, January 29, 2012 1:45 AM
Points: 710, Visits: 1,284
u take a backup for the master after mager change:
like creating linked server, add login, server level trigger and so on.
there is no need to backup it every day, any way its a small database even if u take a daily backup it wont be a problem.


..>>..

MobashA
Post #506715
Posted Tuesday, May 27, 2008 2:10 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, June 30, 2014 8:31 AM
Points: 647, Visits: 760
hi mobasha,
tnx for the reply, it was just to make sure.
the other system databases (temp - model- msdb) does the need for a backup exsists here also.?

For disaster recovery do we use a Ghost backup of the system C:\ drive (OS, SqlServer, other soft)
This one is tested and made it possible to restore to a system state in 5'

But then it could also be wise to put the master database on another drive because it would be overwritten by a ghost restore since its on the same drive.

Eddy
Post #506740
Posted Tuesday, May 27, 2008 2:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 42,470, Visits: 35,541
TempDB -no. It's recreated every time the service starts.
Model - not unless you've made changes to it.
MSDB - yes. It's where your job definitions and history, backup histories, SSIS/DTS packages and similar are kept.

Take sQL backups of the databases and don't depend on ghost. Unless it's aware of SQL, the backups of the system databases that ghost takes could be unusable



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #506744
Posted Tuesday, May 27, 2008 3:23 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, June 30, 2014 8:31 AM
Points: 647, Visits: 760
Tnx Gail,
Just what i needed to know,
The ghost backup is for system disaster recovery,
Database backups are not with ghost,
I use Red gate SqlBackup for this. + from time to time a normal backup for the ease of restoring to another development server

little question here arise also:
i take daily Full backups using sqlbackup
i take log backups every 15'
if i then also take a full backup using sqlserver on a certain point will the log chain not be broken eg, possible restore with red gates sqlbackup.? or is sqlserver clever enouch to see the difference.?

for the other question:
I will include all system databases except tempdb in my maintenance plan for daily backups.

Eddy

Post #506763
Posted Tuesday, May 27, 2008 3:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 42,470, Visits: 35,541
eddy (5/27/2008)
if i then also take a full backup using sqlserver on a certain point will the log chain not be broken eg, possible restore with red gates sqlbackup.?


A full backup resets the differential base. A full backup does not truncate transaction logs, therefore does not break a log chain. Doesn't matter if it's native backup or a 3rd party tool.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #506766
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse