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
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.
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.
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:
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:
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.
|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
Copyright 2007 TechTarget
Didn't find what you're looking for? Pose a question to our SQL Server experts.
This was first published in May 2007