restores

  • 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

  • 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.

    [font="Verdana"]- Deepak[/font]

  • u can use:

    cluster server, log shipping, database mirroring or so..

    ..>>..

    MobashA

  • 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

  • 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

  • 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

  • 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
  • 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

  • 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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply