Backing Up SQL Server

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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! ), 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!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote