Feeds:
Posts
Comments

Archive for the ‘SQL Backup and Restore’ Category

SQL SERVER – Effect of Compressed Backup Setting at Server Level on Database Backup

Posted in PostADay, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on December 28, 2011 | 11 Comments »

I recently received following question from reader. I would like to share the complete story in few short sentences with you to give you complete idea. Let us call the reader Margie. Our long email conversation is converted into chat like conversation

Margie: Hi Pinal – I am seeing strange behavior with regards to my database backup.
Pinal: What is the exact issue?
Margie: I am taking database backup with following script for more than an year and my database is of always certain size. From last six days the size of the database backup is reduced big times. There is absolutely no change in the T-SQL code. It has been the same and part of the backup job for ever.

BACKUP DATABASE [AdventureWorks] TO DISK = N'D:\Backup\ad2'
GO

Pinal: Have you deleted any data from your database or moved data to different file-group which is not backed up?
Margie: No. It is same. The amount of the data is ever increasing.
Pinal: Have you restored the database back and checked the .MDF and .LDF file size?
Margie: Well, I tried it now, it is the same.
Pinal: Have you enabled database compression?
Margie: No, I have already mentioned that I have not changed T-SQL code from ages.
Pinal: Have you touched server level settings for compression?
Margie: What is that?
Pinal: Ahha… Gotcha!

Well, after a quick research we figured out what was changed in the server. Their new Admin has changed following settings on the server level, which resulted in the compressed backup.

Their new Admin had read my following articles.

After reading the articles, he checked all the backup settings and realize that there is no database backup configured. After running necessary checks, he decided that compressed backup is the recommended option for all the databases. As it would take time to identify and figure out all the place where the backup code is used and enable compression, he went ahead and configured the server level settings for backup compression. This resulted that all the backup on the server without any T-SQL backup code change were compressed backup. As he had not communicated this with Margie, she was confused with situation.

Well, there are two learning we all adopt from this scenario.

  • Server level backup compression settings affect all the default backup database scripts
  • Database Admin should communicate all of their changes to relevant team member and proper documentations should be done
  • Bonus Learning: Ask around!

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

Read Full Post »

SQL SERVER – Target Recovery Time of a Database – Advance Option in SQL Server 2012

Posted in PostADay, SQL, SQL Authority, SQL Backup and Restore, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on December 27, 2011 | 10 Comments »

Recently I was going over few advanced options of SQL Server 2012 in database properties and I found a new option in the property screen.

Properties screen of SQL Server 2008 R2

Properties screen of SQL Server 2012

I got little curious and decided to learn what does this new feature indicates. When I started to learn more about this subject, I had excellent learning experience.

The default value of this option is 0. This value is directly related to Checkpoint. When it is set to greater than 0 (zero) it uses indirect-checkpoints and establishes an upper-bound on recovery time for this database.

NOTE: Do not attempt this right away on your production database. Make sure that you try this out on your development server and validate before making it any changes on your production server. Make sure that you do not change this option unless you are 100% sure about what you are doing and understand the risk as well implications.

Indirect Checkpoints are alternative to automatic checkpoints (which should be good for most of the system and should be left alone if you are not sure about implications). If system crashes accidentally, indirect checkpoints provides potentially faster recovery time than automatic checkpoints.

Reasons for enabling indirect checkpoint

  • It may improve database recovery time
  • It may reduce checkpoint I/O as it writes continuously pages to the disk in the background

Reasons for NOT enabling indirect checkpoint

  • In OLTP workload it can increase overall writes on server by writing continuously pages to the disk in the background which may reduce the performance.

I have now learned the pros and cons of this feature and I am going to test this feature out on various workload oriented servers and see what is the effect of this feature. You can read further on MSDN about this feature.

Reference : Pinal Dave (http://blog.sqlauthority.com)

Read Full Post »

SQL SERVER – A Quick Script for Point in Time Recovery – Back Up and Restore

Posted in Pinal Dave, PostADay, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology on December 23, 2011 | 17 Comments »

Blogging is like writing a big novel in parts. It has its own mood and it has its own colors. Someday I feel like writing philosophy and some day I like writing theory and some day just a script. Today is one of the day when I just feel like providing working script for user requested frequently. Here is one of the script which I refer whenever I faced situation about restoring the database at point in time.

In this demo we will see three step operations:

  1. Set up script and backup database
  2. Restore the database in point in time
  3. Clean up database

Let us see each step with simple script:

1. Set up Script and backup database

------------------------------------------------
-- Creating environment
------------------------------------------------
-- Create Database
CREATE DATABASE SQLAuthority
GO
-- Make sure database is in full recovery
ALTER DATABASE SQLAuthority
SET RECOVERY FULL
GO
USE SQLAuthority
GO
-- Create Table
CREATE TABLE TestTable (ID INT)
GO
-- Taking full backup
BACKUP DATABASE [SQLAuthority]
TO DISK = N'D:\SQLAuthority.bak'
GO
INSERT INTO TestTable (ID)
VALUES (1)
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (2)
GO
-- Taking log backup
BACKUP LOG [SQLAuthority] TO
DISK = N'D:\SQLAuthority1.trn'
GO
INSERT INTO TestTable (ID)
VALUES (3)
GO
INSERT INTO TestTable (ID)
VALUES (4)
GO
BACKUP LOG [SQLAuthority] TO
DISK = N'D:\SQLAuthority2.trn'
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (5)
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (6)
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (7)
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (8)
GO
-- Marking Time Stamp
SELECT GETDATE() BeforeTruncateTime;
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
-- Quick Delay before Truncate
WAITFOR DELAY '00:00:01'
GO
TRUNCATE TABLE TestTable
GO
-- Quick Delay after Truncate
WAITFOR DELAY '00:00:01'
GO
-- Marking Time Stamp
SELECT GETDATE() AfterTruncateTime;
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (9)
GO
-- Taking log backup
BACKUP LOG [SQLAuthority] TO
DISK = N'D:\SQLAuthority3.trn'
GO
-- Marking Time Stamp
SELECT GETDATE() CurrentTime;
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
USE MASTER
GO

2. Restore the database in point in time

-----------------------------------------------
-- Restoring Database
------------------------------------------------
USE [master]
GO
-- Taking tail log
BACKUP LOG [SQLAuthority] TO
DISK = N'D:\SQLAuthority5.trn'
WITH NORECOVERY
GO
-- Restore full backup
RESTORE DATABASE [SQLAuthority]
FROM DISK = N'D:\SQLAuthority.bak'
WITH
STANDBY = N'D:\SQLAuthority11.bak'
GO
-- Restore transaction backup
RESTORE LOG [SQLAuthority]
FROM DISK = N'D:\SQLAuthority1.trn'
WITH STANDBY = N'D:\SQLAuthority11.trn'
GO
-- Selecting the data from TestTable
SELECT *
FROM SQLAuthority.dbo.TestTable
GO
-- Restore transaction backup
RESTORE LOG [SQLAuthority]
FROM DISK = N'D:\SQLAuthority2.trn'
WITH STANDBY = N'D:\SQLAuthority21.trn'
GO
-- Selecting the data from TestTable
SELECT *
FROM SQLAuthority.dbo.TestTable
GO
-- Restore transaction backup
RESTORE LOG [SQLAuthority]
FROM DISK = N'D:\SQLAuthority3.trn'
WITH STOPAT = '2011-12-21 11:12:18.797', -- Insert Your Time
STANDBY = N'D:\SQLAuthority33.trn'
GO
-- Rolling database forward
RESTORE LOG [SQLAuthority]
WITH RECOVERY
GO
-- Selecting the data from TestTable
SELECT *
FROM SQLAuthority.dbo.TestTable
GO

3. Clean up database

------------------------------------------------
-- Clean up
------------------------------------------------
USE MASTER
GO
ALTER DATABASE [SQLAuthority]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [SQLAuthority]
GO

Few quick points to note: Database has to be either in full recovery or bulk recovery mode. While restoring the database it should be done either WITH NORECOVERY (Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored) or WITH STANDBY (Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted).

Watch a 60 second video on this subject

Lots of scripts today but if you are in need of Point in Time Recovery script, this script is for you. I am open for suggestion, enhancements etc. Let me know your comments.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Read Full Post »

SQL SERVER – FIX – ERROR : Msg 3201, Level 16 Cannot open backup device.Operating system error 3 (The system cannot find the path specified.)

Posted in Pinal Dave, PostADay, SQL, SQL Authority, SQL Backup and Restore, SQL Error Messages, SQL Query, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology on December 17, 2011 | 11 Comments »

I had very interesting and frustrating experience. Recently I was attempting to backup one of my database and I end up on following error.

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘D:\Backup\SQLAuthority.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Solution:

Go to your drive and create the missing folder. In my case I went to Drive D and created Backup Folder there.

Additional Story:

If you read the first line of the blog post you will read there that I mentioned that I had frustrating experience. It was not frustrating that I do not know the solution – it was totally different reason. When I encountered the above error I quickly went and created the folder in my machine. Once I created the folder, I still keep on getting the error. After a while I realize what I was doing. I was creating the folder in the machine where I had installed SQL Server Management Studio. To resolve this error one has to create the folder in the machine where SQL Server is installed. Once I created the folder to the location where I was attempting to take backup the issue was resolved.

How many of you have been in the same situation as me as mentioned in additional story?

Reference : Pinal Dave (http://blog.sqlauthority.com)

Read Full Post »

SQL SERVER – SQL Backup and FTP – A Quick and Handy Tool

Posted in PostADay, SQL, SQL Authority, SQL Backup and Restore, SQL Documentation, SQL Download, SQL Query, SQL Server, SQL Tips and Tricks, SQL Utility, SQLAuthority News, T SQL, Technology on October 3, 2011 | 18 Comments »

Scroll down at the end of this post to win my SQL Wait Stats Book.

I have used this tool extensively since 2009 at numerous occasion and found it to be very impressive. What separates it from the crowd the most – it is it’s apparent simplicity and speed. When I install SQLBackupAndFTP and configure backups – all in 1 or 2 minutes, my clients are always impressed.

To put it simply, SQLBackupAndFTP is MS SQL Server backup software that performs these tasks:

  • Backup SQL Server Database
  • Zip the backups
  • Encrypt the backups
  • FTP the backups to remote FTP server
  • Move file to local area network
  • Send final status of tasks in email.
  • Allows to monitor the history of jobs through the web

SQLBackupAndFTP comes in Free and Paid versions (starting from $29) – see this version comparison chart, but even Free version is fully functional for unlimited ad hoc backups or for scheduled backups of up to two databases – it will be sufficient for many small customers.

But enough theory, let us now see how we can take backup and move it to the desired folder.

Image 1: You start from the main form and  all the settings are very easy as described in the following image. From database selection to job scheduling, you can configure everything on one screen.

On this form you would usually do the following:

  1. Press “Connect to SQL Server” button to connect to your SQL Server instance
  2. Check databases in the list to select databases to backup
  3. If you need to store backups in a Local/Network folder – fill out the path to this folder
  4. If you need to send backups to FTP – fill out your FTP server settings
  5. Enter you email to receive job notification e-mails
  6. Fill out the time when a scheduled daily backup job should run
  7. Press “Run Now” button to test your backups

Note that the above covers just the basic scenario when you need full backups once a day. If you need Differential or Transaction Log backups or a schedule more complicated than once per day – press “Advanced Schedule”.

Image 2: Advanced Backup Schedule. Again, what I love about this program – it is how simply it allows you to resolve even quite complicated tasks. Your can click the sample scenario link that matches you needs the most – like “Daily 4 times” and everything is set – you’ll get one full and 3 Differential backups per day. Or select any other scenario that suits you.

Image 3: Advanced Settings screen is optional and allows you set encryption, compression levels, backup file name, temporary folders, if you want to use web logs (more about it below) and more

Image 4: If you checked “Use web log” in Advanced Settings, the log of your jobs is being saved on the web on sqlbackupandftp.com site (see weblog demo) and you can see the history of these jobs (even collected from multiple servers) in a single page – very interesting feature that can be very handy for large organizations

A detailed configuration tutorial is available here if you are interested.

Now, a question that might baffle your mind is – how this tool is better than maintenance tasks available in SQL Server. Well, here goes the answer. Maintenance tasks are easy to set up for backups; however, SQLBackupAndFTP have integrated solution for encryption, FTP and email which make it superior to maintenance tasks in every aspect.

I would like to present my unbiased view about this tool. At the same time, I want to give my readers a broad perspective about backup tools in general.  On comparing this tool with other backup tools I have experienced that it is not the fanciest tool to manage backups or check their health. This is one tool that smoothly performs the job in a very few steps compared to native SQL Server backup, which takes numerous steps to execute the similar job.

Image 5: Along with SQLBackupAndFTP setup gives you the option to install “One-Click SQL Restore” (you can install it stand-alone too) – a basic tool for restoring just Full backups.

However basic, you can drag-and-drop on it the zip file created by SQLBackupAndFTP, it unzips the BAK file if necessary, connects to the SQL server on the start, selects the right database, it is smart enough to restart the server to drop open connections if necessary – very handy for developers who need to restore databases often.

While testing SQLBackupAndFTP I noticed some limitations. It only works reliably on local SQL Server instances. In other words it has to be installed on the SQL server itself. For remote servers it uses scripting which is less reliable. This limitations is actually inherent in SQL server itself as BACKUP DATABASE command  creates backup not on the client, but on the server itself.

This tool is compatible with almost all the known SQL Server versions. It works with SQL Server 2008 (all versions) and many of the previous versions. It is especially useful for SQL Server Express 2005 and SQL Server Express 2008, as they lack built in tools for backup.

SQLBackupAndFTP is rapidly evolving since I first started using it two years ago. I spoke to developers and it seems that their immediate goal is to include cloud storage support – users will be able to backup directly to Microsoft SkyDrive, Amazon S3, DropBox, Box.net and others – very exciting development.

I strongly recommend this tool to all the DBAs. They must absolutely try it as it is free and does exactly what it promises. You can download your free copy of the tool from here.

Please share your experience about using this tool. I am eager to receive your feedback regarding this article. I will also give print book of my SQL Server Wait Stats book to two individual who share their feedback about this product. The book will be sent to any country where Amazon delivers the book.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Read Full Post »

« Newer Posts - Older Posts »