Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Backing Up SQL Server

    I need to back-up a SQL Server (Version 2000). With our current backup software we use, Tivoli (if I spelled that right), it will not back up some of the SQL Server files that are open.

    Do I need to shut down all the services associated with SQL Server or shut down the SQL Server itself - then do the back-up? I know this is done all the time and want to know what the best practice is. Ideally, for my current situation, I need to just back up one of the databases on the server and would rather not shut down the whole thing to do it.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Backing Up SQL Server

    Hi Mike,
    I don't know the actual details involved but as far as I know most people have their SQL servers dump data to a flat file and then back that file up rather than backing up the open database itself. If you do want to back up the database itself while it's open, you can buy software (such as Open File Manager by St. Bernard Software) that will backup open files such as Oracle DBs, SQL Server, Exchange etc even while those programs are running.
    Hope that helps.
    PS If you need more detail on the data dumps from SQL Server let me know and I'll check with our DBA.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backing Up SQL Server

    Rory, thanks, I will take you up on that. My situation is that we want to upgrade a database on the SQL server before applying an upgrade. I think your suggestion of dumpping the data to a file and then backing up that file is sound. We also need to investigate Open File Manager for the long term, but this should get us by for now.

    If we backed up all files on the SQL server while it is running, with the files not backed up due to SQL being running, is that enough to restore the data itself? I'm guessing not, since I'm sure St. Bernard Software is in business for a reason

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backing Up SQL Server

    I have to admit that I'm a little confused about what you're up to, but Rory has you pointed in the right direction, IMHO. If you don't have backup software that can back up SQL Server db's directly, then backing up each database to a file with that file in a network directory that your backup software can get to should give you the level of protection you need. If it's a database that has a high number of transactions, you may also consider backing up your transaction logs to a network location your backup software can access. Good luck!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backing Up SQL Server

    All we are doing is backing up the data before installing an upgrade to an application that runs off the SQL Server. I have already pushed once for the purchase of Open File Manager but am going to use this example to push this again. In the mean time, my intent is to dump our existing data to a flat file to get this going. I will also do as you suggest with the transaction logs.

    To dump all the data in a database, do you just write an SQL statement that selects everything from each table and save it to a file? Or is there something built into SQL Server that facilitates this tedious sounding task? Also, what does IMHO stand for?

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backing Up SQL Server

    Sorry, sometimes acronyms get the better of me! That would be In My Humble Opinion.

    SQL Server does expose a pretty easy way to do this. Open up SQL Server Enterprise Manager, and drill into your server until you find the database you want to back up. Right click on the database, choose All Tasks -> Backup Database, and follow the wizard down the yellow brick path! I
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backing Up SQL Server

    Ah, I see. And this is great, I'm also starting out my Monday with an extended vocabulary.

    Thanks for the advice Shane. I have a small database that I created on the SQL server that I want to experiment with. If I were to do a backup of the database, the master, MSDB, and model - delete my small database - then do a restore of all these files, will that bring the database back to full functionality from the last backup?

  8. #8
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backing Up SQL Server

    It should, assuming there haven't been any changes to the data since your backup, though I wouldn't recommend restoring the system databases without a really, really, really good reason! That is, practice backing them up, but only restore your test database.

    The scenario I had in my mind during the previous post was this: You back up the relevant database, then perform the application upgrade. Nuts, the upgrade didn't go as promised by the vendor (a shocking and wholly implausible scenario, I know! <img src=/S/doh.gif border=0 alt=doh width=15 height=15> ), and so you roll back the upgrade and restore the backup of your data database. If the upgrade made changes to the system databases, you might still be in trouble and those system database backups would come in handy. But complete lack of functionality with the threat of termination hanging over you is the only condition under which I'd consider restoring system databases.

    By the way, or BTW, in a car tuning forum that I cruise from time to time, they not only have forum acronyms to contend with, but car and after-market manufacturer and tuning and part and on and on acronyms as well. It's so confusing, somebody had to compose an Acronym FAQ! Silliness reigns!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backing Up SQL Server

    God, that is crazy hallarious.

    I did as you suggested in the backup. I then deleted my database in the SQL server and then restored it. It worked perfectly.

    Is there any advantage to doing this with Open File Manager? I mean, SQL 2000 even makes it easy to schedule these backups on individual databases.

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backing Up SQL Server

    Good! I'm glad your testing for recovery is working out OK -- here's hoping you won't have to use it!!

    What are the advantages of using a backup agent like Open File Manager? Well, I guess you'd save on storage space and eliminate a level of complexity. The agent would back the database(s) up directly to its media, say a tape, rather than SQL backing up the database(s) to a network location and the agent then backing up the contents of that network location to tape.

    Um...any one else want to chime in here? My implementation of SQL 2000 is not a high volume transaction implementation, but more of a data warehousing implementation. We use ARCserve to back up relevant databases nightly, which is overkill, honestly, and I use right-click quick backups when I need to which ain't all that often.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Posting Permissions

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