FAQ: Top 5 SQL Server backup and recovery problems

Frequently Asked Questions: SOLVE FIVE COMMON PROBLEMS

How can I recover my password in SQL Server 2000? Is it possible to back up the stored procedure only? Can I examine transaction logs to recover lost data? How can I backup a remote database? 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:

• Red-Gate SQL Log Rescue
  
• Lumigent Log Explorer
  
• ApexSQL Log
  

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:
  • Idera SQLsafe
  • Red-Gate SQL Backup
  • Quest LiteSpeed for SQL Server

• 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:
  • Neverfail for SQL Server

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.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Backup and Recovery SQL Server Mailbag: Data restoration and DB property management Achieving high availability and disaster recovery with SharePoint databases How to 'do' SQL Server disaster recovery The keys to database backup protection for SQL Server Choosing a SQL Server disaster recovery solution Licensing a standby server for SQL Server replication Can I encrypt and restore a database backup in SQL Server 2005? SQL Server errors, failures and other problems fixed from the trenches Get SQL Server log shipping functionality without Enterprise Edition SQL Server 2008 backup compression pros and cons SQL Server Backup and Recovery Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary rollback  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary