SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


restores


restores

Author
Message
sandhyarao49
sandhyarao49
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 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
Deepak-405225
Deepak-405225
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 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
mobasha
mobasha
SSC Eights!
SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)

Group: General Forum Members
Points: 884 Visits: 1284
u can use:
cluster server, log shipping, database mirroring or so..

..>>..

MobashA
Van Heghe Eddy
Van Heghe Eddy
SSC Eights!
SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)

Group: General Forum Members
Points: 871 Visits: 900
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
mobasha
mobasha
SSC Eights!
SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)

Group: General Forum Members
Points: 884 Visits: 1284
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
Van Heghe Eddy
Van Heghe Eddy
SSC Eights!
SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)

Group: General Forum Members
Points: 871 Visits: 900
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86042 Visits: 45226
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, MVP, M.Sc (Comp Sci)
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


Van Heghe Eddy
Van Heghe Eddy
SSC Eights!
SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)

Group: General Forum Members
Points: 871 Visits: 900
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86042 Visits: 45226
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search