Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jun 2004
    Posts
    115

    Unanswered: sqlserver Backups and restore

    Hi

    I am new to sqlserver 2005 comming from Oracle DBA background.
    I saw sqlserver has got many db's on a server.

    Suppose i have a db called prod db on server called PROD.currently i am taking a backup of only the prod db is it necessary for me to take backups of other db's like model ,temp,msdb etc from the prod server if so why ?

    Also the transaction log file is growing huge how can i reduce it ?

    regards
    Hrishy

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Temp - no - as implied, it is a "cache"
    Model - only if you change something in it - new databases draw their default settings\ contents from here unless overridden when created
    msdb - yes - contains lots of maintenance info (job history & parameters, backup history & parameter)
    Master - God, yes - do you need to ask?

    What is the Prod db recovery model?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2004
    Posts
    115
    Hi

    pootle flump appreciate you taking time out and responding please pardon my ignorance (also my mental make up is still oracle)

    recovery model is set to full recovery.
    Master why should i back it up ?

    I have sucesfully restored the copy of my Prod Db on another server many times now ?

    Also the transaction log file is growing huge how can i reduce it ?


    regards
    Hrishy

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to start reading up on SQL Server I think. The documents (Books Online, plus all the literature) are light years ahead of Oracle documentation (according to the regulars on here anyway).

    Master is your most critical system database. If Master corrupts, there goes your instance until you fix\ restore it.

    I'll get on to the transaction log - there is more info needed yet. Are you backing up the prod db transaction log?

    >> I have sucesfully restored the copy of my Prod Db on another server many times now ?
    You tell me
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2004
    Posts
    115
    Hi Pootle Flump

    Thanks again
    Yes i do backup the transaction log along with the db.

    i need to take care to backup the master model and the msdb database.

    I have read the sqlserver book online documentation but there seems to be no clear advise on how to reduce the size of the transaction logs.

    I searched here a lot but unable to find any thread that would explain sqlserve recovery from a Oracle DBA perspective.

    I am sure there are many oracle DBA's who have been in the same position as i am.

    regards
    Hrishy

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It is documented in BoL - you have really read the whole of Books Online? You definitely backup the transaction log of the prod db? 99% of the time not backing it up is the cause of the problem. Is the activity on the database increasing greatly? If not, can you post the backup commands you use?

    There are loads of resources re. migrating to SQL Server if you google. I don't know Oracle, but if you can't find stuff re recovery in SQL Server that makes sence from an Oracle perspective then I would sugggest learning about recovery in SQL Server from the ground up without any preconceptions.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2004
    Posts
    115
    Hi Pootle Flump

    Yes i did read the BOL for backup and recovery.
    Yes i always take care to backup the transaction log of the prod db.

    But strangely for my database now the size of the transactionlog is 2gb and the size of the database is 1gb.

    In oracle the transaction logs are circular and this is what worries me as someday i might have a transaction log of 50gb and the database of only 25gb.

    i am searching on google on your lines.

    Thanks and appreciate your help and advise both very much

    regards
    Hrishy

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you post your backup commands? Same in SQL Server - by default, once tran log is backed up the backed up transactions can be overwritten.

    In order to shrink it you can look at SHRINKFILE, however we should really get to the bottom of the problem first.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2004
    Posts
    115
    Hi

    The command i use is like

    Code:
    BACKUP LOG PROD TO 
    DISK= 'C:\SQL\Backup\PROD-LOG--TODAYSDATE.bak'
    However after this the logfile is still at 2Gb

    regards
    Hrishy

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by db2hrishy
    However after this the logfile is still at 2Gb
    It will be. The command does not shrink the file - this would be inefficient - but it marks all completed and backup up transactions as ok to overwrite. I have been responding based on you saying the transaction log grows. Do you mean that it grows or do you now mean it does not shrink? These are two different things.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also forgot to ask - how often are you taking log backups?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2008
    Posts
    23
    if you are still looking to resolve the log file issue. two things.
    1.
    You cannot shrink the log file beyond its initial setting. you may want to check this aspect. We need to do some circumventuions to solve, if this is the issue.
    2.
    If that is not the case, and you have a full backup of your database and log files, you can shrink the log file by using he following commands

    use admin
    sp_helpfile
    backup log Admin with truncate_only
    dbcc shrinkfile(2)

    2 in shrinkfile is the fileid of the log file of my database named Admin
    you can see the fileid by using the command sp_helpfile.

    I agree for oracle users where the concept everything about one db is in one db, here for multiple databases control information is actually stored in a central place, which could be confusing in the beginning. I always backup master, msdb and model databases.

    hope it helps,
    jambu

  13. #13
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    DB in SQL is equivalent to schema in Oracle.

    in SQL you can take an online dump of the db, there is not an equivalent of export, backup database probably something in between a full schema export and a hot backup. You can do a cold backup, and stop sql and copy the mdf and ldf files, but I never do that.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by PMASchmed
    You can do a cold backup, and stop sql and copy the mdf nd ldf files, but I never do that.
    Dunno what a cold back up is but you can attach\ detach the files without stopping\ starting the service.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Nov 2008
    Posts
    1

    Thumbs down sqlserver backups and restore

    Hi dear hrishy
    if you are new to sqlserver 2k5 and came from oracle DBA. so sqlserver all the version you will easily understand with little bit practice.
    with reference to you question. there is no need to take backup of model, temp,msdb etc just take backup of your database e.g: PROD.
    if the transaction log file is growing huge then you can manage it with creating multiple log files with a specified amount of memory space.
    another good news for you .
    you can download the vb.net readymade backup & restore utitilies from google search easily..

    thanks

    Quote Originally Posted by db2hrishy
    Hi

    I am new to sqlserver 2005 comming from Oracle DBA background.
    I saw sqlserver has got many db's on a server.

    Suppose i have a db called prod db on server called PROD.currently i am taking a backup of only the prod db is it necessary for me to take backups of other db's like model ,temp,msdb etc from the prod server if so why ?

    Also the transaction log file is growing huge how can i reduce it ?

    regards
    Hrishy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •