Feed on
Posts
Comments

Archive for the ‘SQL Backup and Restore’ Category

SQL SERVER - Delete Backup History - Cleanup Backup History

Posted in Author Pinal, Best Practices, Database, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 11, 2008 | 3 Comments »

SQL Server stores history of all the taken backup forever. History of all the backup is stored in msdb database. Many times older history is no more required. Following Stored Procedure can be executed with parameter which takes days of history to keep. In following example 30 is passed to keep history of month.
USE msdb
GO
DECLARE [...]

Read Full Post »

SQLAuthority News - Book Review - Pro SQL Server 2005 Replication (Definitive Guide)

Posted in DBA, Database, SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Book Review, SQLAuthority News, T SQL, Technology, tagged on October 13, 2008 | No Comments »

Pro SQL Server 2005 Replication (Definitive Guide) (Hardcover)
by Sujoy Paul (Author)
Link to Amazon (This is not affiliate link)
Quick Review:
This is good book for any novice developer to start in the world of database replication implementation and maintenance. Replication is important part of highly availability and one book covers all the concept and methodology at [...]

Read Full Post »

SQL SERVER - Behind the Scene of SQL Server Activity of - Transaction Log - Shrinking Log

Posted in Author Pinal, SQL, SQL Authority, SQL Backup and Restore, SQL Documentation, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on August 21, 2008 | 4 Comments »

Imran Mohammed continues to help community of SQL Server with his very enthusiastic writing and deep understanding of SQL Server architecture. Let us read what Imran has to say about how Transaction Log works and Shrinking of Log works.
Question from lauraV
Please help me understand. I am taking a full backup once a day, and transaction [...]

Read Full Post »

SQL SERVER - 2008 - Download and Install Samples Database AdventureWorks 2005 - Detail Tutorial

Posted in Author Pinal, Database, SQL, SQL Add-On, SQL Authority, SQL Backup and Restore, SQL Documentation, SQL Download, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology on August 10, 2008 | 14 Comments »

Just a day ago I received question from reader who just installed SQL Server 2008. After installation user did not find any samples database along with installation. User want to install samples database which he is very much used to.
Sample database are now moved to Microsoft’s opensource site of Codeplex. Visit following link to visit [...]

Read Full Post »

SQL SERVER - mssqlsystemresource - Resource Database

Posted in Author Pinal, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on July 27, 2008 | No Comments »

Just a day ago I received following email
“Dear Pinal,
While I was exploring my computer in directory C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data I have found database mssqlsystemresource. What is mssqlsystemresource?
Thanks,
Joseph Kazeka”
Simple question like this are very interesting. mssqlsystemresource is Resource Database. It is read only database and contains system objects (i.e. sys.objects, sys.modules and other sys schema [...]

Read Full Post »

SQL SERVER - FIX - ERROR : 9004 An error occurred while processing the log for database. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

Posted in SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Error Messages, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on July 21, 2008 | 2 Comments »

ERROR : 9004 An error occurred while processing the log for database.
If possible, restore from backup.
If a backup is not available, it might be necessary to rebuild the log.
If you receive above error it means you are in great trouble. This error occurs when database is attempted to attach and it does not get attached. [...]

Read Full Post »

SQL SERVER - 2008 - Introduction to Row Compression

Posted in Author Pinal, SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on July 6, 2008 | 3 Comments »

In my previous article SQL SERVER - 2008 - Introduction to New Feature of Backup Compression I wrote about Row Compression and I have received many request to write in detail about Row Compression. I like when I get request about any subject to write about from my readers.
Row Compression feature apply to zeros and [...]

Read Full Post »

SQL SERVER - 2008 - Introduction to New Feature of Backup Compression

Posted in SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on July 3, 2008 | 3 Comments »

Backup and Data Storage is my most favorite subject and I have not written about this for some time. I was experimenting with new feature of SQL Server 2008 and I come across very interesting feature of Backup compression.
Let us see example of Database AdventureWorks with and without compression. After taking backup with compression enabled [...]

Read Full Post »

SQL SERVER - SQL Joke, SQL Humor, SQL Laugh - Silly Mistake

Posted in DBA, Database, SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Query, SQL Server, SQL Tips and Tricks, Software Development, T SQL, Technology on May 15, 2008 | 1 Comment »

It is really very bad of person to laugh on others misfortune, however dark humor is based on the same concept. It has been long time since I wrote something funny on this blog. Recently, I have came across forum discussion regarding backup misery of one of the developer. I feel very sorry for the [...]

Read Full Post »

SQL SERVER - 2005 - Restore Database Using Corrupt Datafiles (.mdf and .ldf) - Part 2

Posted in Author Pinal, SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on April 10, 2008 | 3 Comments »

Blog reader Donald Crowther has posted following comment. I have not tested this solution and when I tried to test it, it did not work for me. However, I have received email from two of my Jr. DBA who have done experiment about this and they are suggesting it works.
If you have tried everything and [...]

Read Full Post »

SQLAuthority News - 500th Post - An Interesting Journey with SQL Server

Posted in About Me, Author Pinal, Best Practices, DBA, Data Warehousing, Database, SQL, SQL Add-On, SQL Authority, SQL Backup and Restore, SQL Coding Standards, SQL Constraint and Keys, SQL Cursor, SQL Data Storage, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Humor, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQL Utility, SQLAuthority, SQLAuthority Author Visit, SQLAuthority Book Review, SQLAuthority News, SQLAuthority Website Review, Software Development, T SQL, Technology on March 7, 2008 | 9 Comments »

I am very pleased to write my 500th post. After 500 posts, I still have same feeling when I wrote first post on this blog. I would like to thank my family for their continuous support in writing this blog. Most of all I want to thank all of YOU for being wonderful readers of [...]

Read Full Post »

SQL SERVER - Sharpen Your Basic SQL Server Skills - Database backup demystified

Posted in Author Pinal, Database, SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on February 7, 2008 | No Comments »

Read my article in SQL Server Magazine January 2007 Edition
I will be not able to post complete article here due to copyright issues. Please visit the link above to read the article.

Read Full Post »

SQL SERVER - Solution - Log File Very Large - Log Full

Posted in Author Pinal, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on December 16, 2007 | No Comments »

I have been receiving following question again and again either through email or through comments on this blog.
My log file is too big, what should I do?
Answer to this question is in three steps.

Backup the log file to any device.
Truncate the log file.
Shrink the log file.

I have previously written two article about this issue. Refer [...]

Read Full Post »

SQL SERVER - Shrinking Truncate Log File - Log Full - Part 2

Posted in Author Pinal, SQL, SQL Authority, SQL Backup and Restore, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on November 22, 2007 | 9 Comments »

About a year ago, I wrote SQL SERVER - Shrinking Truncate Log File - Log Full. I was just going through some of the earlier posts and comments on this blog and one particular comment by Praveen Barath caught my eye. It is very good explanation. I am copying complete explanation here with full credit [...]

Read Full Post »

SQL SERVER - 2005 - Generate Script with Data from Database - Database Publishing Wizard

Posted in Author Pinal, DBA, SQL, SQL Add-On, SQL Authority, SQL Backup and Restore, SQL Documentation, SQL Download, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on November 16, 2007 | 14 Comments »

I really enjoyed writing about SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects. Since then the I have received question that how to copy data as well along with schema. The answer to this is [...]

Read Full Post »

SQL SERVER - DBCC CHECKDB Introduction and Explanation - DBCC CHECKDB Errors Solution

Posted in Author Pinal, SQL, SQL Authority, SQL Backup and Restore, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology on November 13, 2007 | 4 Comments »

DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified database. If DBCC CHECKDB ran on database user should not run DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG on database as DBCC CHECKDB includes all the three command. Usage of these included DBCC commands is listed below.
DBCC CHECKALLOC - [...]

Read Full Post »

SQLAuthority News - Best Articles on SQLAuthority.com

Posted in Author Pinal, DBA, Data Warehousing, Database, SQL, SQL Add-On, SQL Authority, SQL Backup and Restore, SQL Coding Standards, SQL Constraint and Keys, SQL Cursor, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Humor, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQL Utility, Software Development, T SQL, Technology on November 4, 2007 | 2 Comments »

SQL SERVER - Cursor to Kill All Process in Database
SQL SERVER - Find Stored Procedure Related to Table in Database - Search in All Stored procedure
SQL SERVER - Shrinking Truncate Log File - Log Full
SQL SERVER - Simple Example of Cursor
SQL SERVER - UDF - Function to Convert Text String to Title Case - Proper [...]

Read Full Post »

SQL SERVER - Importance of Master Database for SQL Server Startup

Posted in Author Pinal, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 31, 2007 | 3 Comments »

I have received following questions many times. I will list all the questions here and answer them together.
What is the purpose of Master database?
Should we backup Master database?
Which database is must have database for SQL Server for startup?
Which are the default system database created when SQL Server 2005 is installed for first time?
What happens if [...]

Read Full Post »

SQLAuthority News - Book Review - Backup & Recovery (Paperback)

Posted in DBA, Database, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Book Review, T SQL, Technology on October 17, 2007 | 1 Comment »

Backup & Recovery [ILLUSTRATED] (Paperback)
by W. Curtis Preston (Author)

Link to Amazon (This is not affiliate Link)
Short Summary:
This book’s does not only teaches you have to create safe backup but it takes you to the next level where a large organization can save tons of dollars a year by making their backup and restore faster and [...]

Read Full Post »

SQL SERVER - FIX : Error 3154: The backup set holds a backup of a database other than the existing database

Posted in Author Pinal, DBA, SQL, SQL Authority, SQL Backup and Restore, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, SQLAuthority Book Review, T SQL, Technology on September 27, 2007 | 61 Comments »

Our Jr. DBA ran to me with this error just a few days ago while restoring the database.
Error 3154: The backup set holds a backup of a database other than the existing database.
Solution is very simple and not as difficult as he was thinking. He was trying to restore the database on another existing active [...]

Read Full Post »

Older Posts »