Get started Bring yourself up to speed with our introductory content.

FAQ: Top 5 SQL Server backup and recovery problems

Backup and recovery in SQL Server is another one of those 'do or die' areas. We've tallied your page hits and compiled a Top 5 list of frequently asked questions on the topic, so you'd have a quick reference.

Backup and recovery in SQL Server is another one of those 'do or die' areas. We've tallied your page hits and compiled a Top 5 list of frequently asked questions on the topic, so you'd have a quick reference. Find out how to reduce the size of a backup file in SQL Server 2000, how to utilize a transaction log to recover lost data and more.

 

Frequently Asked Questions:

SOLVE FIVE COMMON PROBLEMS

  1. How can I recover my password in SQL Server 2000?
  2. Is it possible to back up the stored procedure only?
  3. Can I examine transaction logs to recover lost data?
  4. How can I backup a remote database?
  5. How can I reduce the size of a full backup?

1. How can I recover my password in SQL Server 2000?

There isn't a method to reverse the password stored by SQL Server. If you're using mixed mode, you might be able to use a network sniffer to see the password zoom by on the wire.

Return to commmon problems FAQs

2. Is it possible to back up the stored procedure only? 

It is not possilble to back up only the stored procedure by using a BACK UP command, but you can right click on the object and script it out to a text file.

Return to commmon problems FAQs

3. Can I examine transaction logs to recover lost data?

Depending on the recovery model of your database you should be able to retrieve these transitions from the transaction log and replay them to recover the data. There are several products in the market such as:

 

Return to commmon problems FAQs

4. How can I backup a remote database?

We have a DR (standby database) site located 1000 km away from our primary database site. We have configured log shipping for the site and we want a complete database backup at the DR site every four days. How can we achieve this?

There are different approaches for handling a standby site. One approach is using log shipping such as you have stated above. There are a couple of ways to handle the movement of such a large amount of data like the full backup.

  • Use a third party backup tool that allows you to do compression of the backup files such as:

     

  • Write your full backup to multiple files, so each of these can be sent individually. These can also be compressed using WinZip or some other compression software.

     

  • Use a third party fail over solution that keeps the two boxes in synch. These tools also have compression capabilities to allow you to send data over a WAN. Some of these tools include:

Return to commmon problems FAQs

5. How can I reduce the size of a full backup?

There is not much you can do to manage the size of the SQL Server 2000 backup files. When you create a backup, the entirety of the database is backed up. Some things to check are how large the transaction log is and whether this can be truncated to reduce the size of the transaction log. Also, you can try to rebuild your clustered and non-clustered indexes to see if reorganization reduces the number of pages that SQL Server needs to store the data.

Another approach to create a smaller database for testing is to script out the objects from your production database and recreate on your test database. Then just move some of the data either by using DTS or BCP.

Return to commmon problems FAQs

 

ABOUT THE AUTHOR:   
 
Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.
Copyright 2007 TechTarget


Didn't find what you're looking for? Pose a question to our SQL Server experts.

 

This was last published in May 2007

Dig Deeper on SQL Server Backup and Recovery

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close