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 [...]
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 »
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 SQL Replication 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 [...]
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 [...]
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 [...]
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 [...]
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. [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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.
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 [...]
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 [...]
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 [...]
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 - [...]
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 [...]
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 [...]
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 [...]
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 [...]
-
About Pinal Dave
Pinalkumar Dave is Microsoft SQL Server MVP and author of over 700 SQL Server articles. He has five years experience as Principal Database Administrator in MS SQL Server 2008/2005, .NET (C#) and ColdFusion MX. He has a Masters of Science degree in Computer Networks, along with MCDBA, MCAD(.NET) and ColdFusion Advanced MX Certifications.
MVP Profile

-
Blog Stats
- 5,224,118 Readers
-
SQLAuthority Links

My Homepage
My Resume
My Other Blog
SQL Server Mag Articles
--------------------
SQLAuthority
Best Articles
Favorite Articles
SQL Script Bank
Top Downloads
--------------------
SQL Interview Q & A
SQL Coding Standards
SQL FAQ Download
--------------------
SQL Random Article
Search SQLAuthority
Subscribe Email Update
SQLAuthority Feed
Translate SQLAuthority
--------------------
Jobs @ SQLAuthority
Find Your IP
Categories
- About Me (35)
- Author Pinal (509)
- Best Practices (39)
- Data Warehousing (25)
- Database (226)
- DBA (198)
- Main (67)
- Outsourcing Technology (4)
- Software Development (73)
- SQL (777)
- SQL Add-On (65)
- SQL Authority (777)
- SQL Backup and Restore (48)
- SQL Coding Standards (84)
- SQL Constraint and Keys (45)
- SQL Cursor (63)
- SQL Data Storage (19)
- SQL DateTime (33)
- SQL DMV (1)
- SQL Documentation (280)
- SQL Download (419)
- SQL Error Messages (360)
- SQL Function (96)
- SQL Humor (21)
- SQL Index (67)
- SQL Interview Questions and Answers (57)
- SQL Joins (357)
- SQL MVP (20)
- SQL Performance (406)
- SQL Puzzle (7)
- SQL Query (777)
- SQL Scripts (512)
- SQL Security (365)
- SQL Server (777)
- SQL Server DBCC (253)
- SQL Stored Procedure (131)
- SQL Tips and Tricks (777)
- SQL Trigger (40)
- SQL User Group (11)
- SQL Utility (55)
- SQL XML (2)
- SQLAuthority (17)
- SQLAuthority Author Visit (32)
- SQLAuthority Book Review (29)
- SQLAuthority News (149)
- SQLAuthority Website Review (18)
- T SQL (777)
- Technology (777)
-
Top Posts
- SQL SERVER - Insert Data From One Table to Another Table - INSERT INTO SELECT - SELECT INTO TABLE
- SQL SERVER - Insert Multiple Records Using One Insert Statement - Use of UNION ALL
- 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
- SQL SERVER - Import CSV File Into SQL Server Using Bulk Insert - Load Comma Delimited File Into SQL Server
- SQL SERVER - Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}
- SQL SERVER - Restore Database Backup using SQL Script (T-SQL)
- SQL Server Interview Questions and Answers Complete List Download
- SQL SERVER - Convert Text to Numbers (Integer) - CAST and CONVERT
- SQL SERVER - 2005 List All Tables of Database
- SQL SERVER - TRIM() Function - UDF TRIM()
- SQL SERVER - Shrinking Truncate Log File - Log Full
- SQL SERVER - Fix : Error: 18452 Login failed for user '(null)'. The user is not associated with a trusted SQL Server connection.
-
Recent Posts
- SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database Including Schema
- SQLAuthority News - SQL Server White Paper: SQL Server 2008 Compliance Guide
- SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database
- SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - Group Photo
- SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - Day 3
- SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - Day 2
- SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - Day 1
- SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - Link List
- SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa
- SQLAuthority News - RML Utilities - Usage and Additional Help
- SQLAuthority News - Download RML Utilities for SQL Server
- SQL SERVER - Delete Backup History - Cleanup Backup History
- SQL SERVER - Check Database Integrity for All Databases of Server
- SQLAuthority News - SQL Server 2008 Book Online Updated in October 2008
- SQL SERVER 2008 - Connect Visual Studio 2005 Patch Download
-
Recent Comments
- COBRASoft on SQL SERVER - 2005 - Database Table Partitioning Tutorial - How to Horizontal Partition Database Table
- SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database Including Schema Journey to SQL Authority with Pinal Dave on SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database
- Steve Hatchard on SQL SERVER - Difference between DISTINCT and GROUP BY - Distinct vs Group By
- Igor on SQL SERVER - FIX : Error 945 Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details
- Rosales on SQL SERVER - Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}
- Kunal Kumar on Contact Me
- Lutz Mueller on SQL SERVER - SELECT 1 vs SELECT * - An Interesting Observation
- Steve Walker on 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
- Rahul on SQL SERVER - 2005 - Display Fragmentation Information of Data and Indexes of Database Table
- Rahul on SQL SERVER - 2005 - Display Fragmentation Information of Data and Indexes of Database Table
- Makarov on Contact Me
- babu on SQL SERVER - Do Not Store Images in Database - Store Location of Images (URL)
- paresh13 on SQL SERVER - 2005 List All Tables of Database
- Manish on 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
- pinaldave on SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - Day 1
Archives
- November 2008
- October 2008
- September 2008
- August 2008
- July 2008
- June 2008
- May 2008
- April 2008
- March 2008
- February 2008
- January 2008
- December 2007
- November 2007
- October 2007
- September 2007
- August 2007
- July 2007
- June 2007
- May 2007
- April 2007
- March 2007
- February 2007
- January 2007
- December 2006
- November 2006
-
Pages
-
Latest Articles
-
pinaldave
- SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database Including Schema
- SQLAuthority News - SQL Server White Paper: SQL Server 2008 Compliance Guide
- SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database
- SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - Group Photo
- SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - Day 3
- SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - Day 2
- SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - Day 1
- SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - Link List
- SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa
- SQLAuthority News - RML Utilities - Usage and Additional Help
-
-
Click Cloud
About Me Author Pinal Best Practices Database Data Warehousing DBA Main Outsourcing Technology Software Development SQL SQL Add-On SQLAuthority SQL Authority SQLAuthority Author Visit SQLAuthority Book Review SQLAuthority News SQLAuthority Website Review SQL Backup and Restore SQL Coding Standards SQL Constraint and Keys SQL Cursor SQL Data Storage SQL DateTime SQL DMV SQL Documentation SQL Download SQL Error Messages SQL Function SQL Humor SQL Index SQL Interview Questions and Answers SQL Joins SQL MVP SQL Performance SQL Puzzle SQL Query SQL Scripts SQL Security SQL Server SQL Server DBCC SQL Stored Procedure SQL Tips and Tricks SQL Trigger SQL User Group SQL Utility SQL XML Technology T SQL


