:::: MENU ::::

Posts Categorized / Backup and Restore

  • Nov 12 / 2008
  • 0
Backup and Restore, dbDigger, System Stored Procedures, T-SQL Scripts

Backup all SQL Server databases (User and system) through T-SQL

There may be some situation where you are in need to backup all SQL Server databases. If you have a number of SQL Server databases (in majority of cases) then it would be a very good idea to use T-SQL command that will backup all SQL Server databases. Do not forget to calculate available space in drive which you are going to provide as backup destination. Use following T-SQL command to backup all SQL Server databases through a single command

SP_MSforeachdb
'BACKUP DATABASE ? TO DISK = ''C:backup?.bak'' WITH INIT'

We have used system stored procedure SP_MSforeachdb to invoke the given command for all databases. T-SQL command for SQL Server backup is provided next to SP_MSforeachdb as parameter. ? in the above command specifies the database name which is going to be handled by the stored procedure SP_MSforeachdb. It will provide the name of databases automatically one by one. It is important to note that above command will backup all system databases along with user databases

  • Nov 05 / 2008
  • 0
Backup and Restore, dbDigger

SQL Server 2005 Recovery Models for databases

The recovery model determines how database transactions are logged and what degree of concern is provided to data loss in case of any failure. In SQL Server 2005, three recovery models are available.

  • Full Recovery Model of SQL Server 2005

The full recovery model does the most extensive logging and allows the database to be recovered to the point of failure. Full recovery model presents the highest protection against data loss. You should always configure all production databases to use full recovery.

  • Bulk-logged Recovery Model of SQL Server 2005

The bulk-logged recovery model fully logs transactions but only minimally logs most bulk operations, such as bulk loads, SLECT INTO, and index creations. Bulk-logged recovery model allows the database to be recovered to the end of a transaction log backup only when the log backup contains bulk changes. Recovery to the point of failure is not supported.

  • Simple Recovery Model of SQL Server 2005

The simple recovery model minimally logs most transactions, logging only the information required to ensure database consistency after a system crash or after restoring a database backup. With simple recovery model the database can be recovered only to the most recent backup. This recovery model has the maximum exposure to data loss and should not be used where data loss in the event of a crash cannot be tolerated.

  • Aug 11 / 2008
  • 0
Backup and Restore, DBA best practices, dbDigger

DBA Best Practices for Backup

Backup is the skill that no one else in organization more than a DBA itself. It is ultimate shield and optimized back plan may be of great advantages. Following are some baselines for database backups

  1. All production databases should be set to use the full recovery model. This way, you can create transaction log backups on a periodic basis.
  2. Whenever possible, perform a daily full backup of all system and user databases.
  3. For all production databases, perform regular transaction log backups, at least once an hour.
  4. Perform full backups during periods of low user activity in order to minimize the impact of backups on users.
  5. Periodically test backups to ensure that they are good and can be restored.
  6. Backup first to disk, then move to tape or some other form of backup media.
  7. Store backups offsite.
  8. If using SQL Server 2005 encryption, be sure to backup the service master key, database master keys, and certificates.
  9. If you find that backup times take longer than your backup window, or if backup file sizes are taking up too much space on your storage device, consider a third party backup program, such as SQL Backup Pro or SQL tool belt.
  10. Document, step-by-step, the process to restore system and user databases onto the same, or a different server. You don’t want to be looking this information up during an emergency.

Chosen from SQL Server DBA best practices By Brad M.mcGehee

Pages:1234
Consult us to explore the Databases. Contact us