SQLServerPedia

Restore your full backup, then run a few queries before you keep restoring transaction logs. Brent shows how to use the Standby option for restores.

In this five minute video, Brent demonstrates how to do it by creating a database, populating data, and then showing what disaster recovery is like by doing RESTORE WITH STANDBY.

The scripts and related links for this podcast are:

• Restore With Standby Tutorial – article in the wiki with the demo scripts.
• SQL Server Backup & Restore Basics – explains full backups, transaction logs, and the different recovery options.

If you have an MP3 player or a portable video player and you want to download our videos automatically, you can subscribe to our podcast feeds here:

• MP4 (Apple) Video Feed
• WMV (Microsoft) Video Feed
• MP3 Audio-Only Feed
• Zune One-Click Subscribe for Video
• Zune One-Click Subscribe for MP3

You can also download this video to watch it later:

• MP4 (Apple) Video Download
• WMV (Microsoft) Video Download
• MP3 Audio-Only Download

When disaster strikes and you need to get your databases back online as fast as possible, filegroup restores are a way to bring your databases back in portions.  Get your most commonly used tables restored first, let your users start querying, and get your boss off your back.  We show how with today’s demo tutorial.

The nine minute video talks about these wiki sections:

• Filegroup Restore Scripts – the demo scripts used in the tutorial.
• Restoring File and Filegroup Backups – the deeper explanation of the concepts of filegroup restores.

If you have an MP3 player or a portable video player and you want to download our videos automatically, you can subscribe to our podcast feeds here:

• MP4 (Apple) Video Feed
• WMV (Microsoft) Video Feed
• MP3 Audio-Only Feed
• Zune One-Click Subscribe for Video
• Zune One-Click Subscribe for MP3

You can also download this video to watch it later:

• MP4 (Apple) Video Download
• WMV (Microsoft) Video Download
• MP3 Audio-Only Download

The first part of SQL Server Disaster recovery planning, or DRP, is knowing what your options are to get a new SQL Server up and running as fast as possible after disaster strikes.  In this twelve minute video, Brent explains your options, including log shipping, database mirroring and SAN snapshot replication.

For more information, check out the SQL Server Backup & Recovery topic in the wiki.

If you have an MP3 player or a portable video player and you want to download our videos automatically, you can subscribe to our podcast feeds here:

• MP4 (Apple) Video Feed
• WMV (Microsoft) Video Feed
• MP3 Audio-Only Feed
• Zune One-Click Subscribe for Video
• Zune One-Click Subscribe for MP3

You can also download this video to watch it later:

• MP4 (Apple) Video Download
• WMV (Microsoft) Video Download
• MP3 Audio-Only Download

Here’s the question in its entirety, so you get to see all of the details:

I have two computers running sql server, sqlserver01 and sqlserver01. On sqlserver01 I backup the database locally to C:\SQLBackup\backupfile.bak (db size: 150GB) then I copy and paste the *.bak file to sqlserver02 via the network. It finishes copying but when I try to restore it on sqlserver02 it fails. I have used tools like Eseutil from exchange but no luck. It seems like the bak file corrupts on the transfer.

What is the best way to copy over a 150GB *.bak file to another server then restore the *.bak file in a timely manner?
-end of question-
OK, to answer this I’ll need to actually throw out some other questions about this scenario.

Since this is to be timely, I would argue that this situation would benefit greatly from a tool that gives offers compressed backups. Compression can reduce the 150GB backup file down to 15-30GB, greatly improving copy speed.

But that does not address the real issue here. In fact, there should be no problem copying the 150GB file across the network to another location. It may be time consuming copying the file, depending on network speed, but this should work fine.

One question I have is if this is occurring while performing a normal Windows COPY or are if something like FTP is being used. If FTP, make sure a binary transfer is being performed.

Specific error messages weren’t given, so I would wonder if any are received or if the two instances are running the same version of SQL Server. Verifying that you can restore the database to the same instance from where it was created would be good to find out.  You can restore it to a new database name to avoid overwriting your original.

The other option available, again depending on network speed, is to back up the database to the network rather than to the local drive. A compressed backup would be a lot faster here as well.

If you want to, please provide some additional details of the error and your environments – Operation System versions and SQL Server versions.

Thanks.

–
David

When taking over a new server or a server you haven’t worked with in a while, never assume anything on the server.

Start by looking at the Windows system event log.  Look for any red or yellow errors, read the error details, and think about how that might impact SQL Server performance.

Next, look at the Windows application event log.  Look for errors again, especially SQL Server errors.

This morning, I was working with a DBA who was having performance problems on his SQL Server, and the application event log showed transaction backup log failure warnings twice a day.  Even more interestingly, it didn’t show any transaction backup log messages – success or failure – the rest of the day.

This indicated two possible problems:

Scenario 1: His server was only set up to do transaction backup logs twice a day.  If so, the server would slow to a crawl during those t-log backups because there was so much to back up.  Regardless of the performance impact, that’s still a disaster recovery problem because if the server crashed, he was likely to lose hours of data.  Fixing that takes precedence over any performance problems.

Scenario 2: The transaction backups were set to run more often, but because of performance problems, the transaction log backups couldn’t keep up with the amount of transaction logging going on.  The backups will run until something goes wrong – I’ve seen t-log backups run on for hours.  If that was the issue, we needed to find out what was making the log backups run slowly – could be writing over a slow network connection, targeting a slow drive, or maybe the box just needed more oomph.

So how could I tell which scenario was the problem without asking him any questions, and just by looking at his event logs?  Post your guess in the comments, and I’ll circle back in a few days with the right answer – unless somebody guesses it first!

This is a very general question that someone could certainly write a book on but I’ll go at answering it by providing a few options and explaining the pro’s and con’s of each solution.  The following list is by no means comprehensive.

The first question you need to ask yourself before going on this journey is where does the encryption need to occur and who are you protecting the data from.  These two questions will drive the amount of effort and complexity required in building a solution.

1)  In database encryption.  The database is encrypted in the database and is viewable to no-one unless they have access to the encryption key.  The data could be encrypted at the application level by first running the data through an encryption algorithm before inserting into the database or the data can be encrypted using SQL Server 2005’s column level encryption or SQL Server 2008’s transparent data encryption.  Using in database encryption is the most secure option in that it can be leveraged to protect sensitive data from anyone that gains access to the database engine itself (whether valid or invalid access) however it has significant performance and scalability caveats.  Encrypting at the application level basically ensures that SQL Server will not be able properly index the data and therefore query perofmance may be dreadful with large data sets.  Leveraging column level encryption or TDE also will introduce query processing overhead and adversely affects the performance of your queries but not to the same extent.   Another drawback of In Database encrption is that it renders backup compression (whether through SQL Server 2008 or a third party backup product or winzip) obsolete.  Encrypted data is not compressible, therefore the size of your encrypted database will roughly equal the size of your backup files regardless of the compression technology used.

2)  If you do not need to secure data inside of the database but want to secure data once it leaves the database, a good solution would be to leverage a third party backup solution such as LiteSpeed.  These solutions will compress and also encrypt backup data in memory so that it is secured as soon as it leaves the database.  This is an attractive option to companies that store backups on network devices and are worried about internal or external users gaining access to network resources and therefore having direct access to backup files.  If you think that native backup files are secured, try opening a small database backup file in notepad.  You will see that all of the data is stored in plain text.  If you have a copy of pubs lying around from the SQL 2000 days you can back it up, open the backup file in notepad, search for “Smith” and you will see Smith’s social security number right there.  This method of database encryption will not secure the data in the database, so anyone who gains database access has free reign on your data, but thats what DBA’s are for right???

3)  A third option, and the last that we will discuss here, is to leverage encryption at the tape level.  Most organizations take their disk based backups and eventually migrate them to tape, where the backups files are then encrypted and eventually moved to offsite storage.  This should be a bare minimum for any SQL Server as once a tape leaves offsite anything could happen to it.  There have been many major news stories recently discussing companies that have had backup tapes lost or stolen and without leveraging at a minimum the tape systems encryption, that data is available to anyone that may “find” a backup tape.  Tape level encryption does nothing to protect the database info inside of the database or the backup data inside of a companies network, but it does protect the data once it is transported offsite.  The nice advantage of this as well as the previous option is that they impose no overhead on standard database workload, they simply may add some overhead to your backup time depending on the level of encyption that you are performing.

Hopefully this brief rundown of database encryption options has given you enough info to get started decideing which implentation strategy is best for you, and as always if you have any further questions, feel free to submit them.

The full original question is:  How do you change the default location of a mdb file? Currently when I detach it is set for C: Drive path and I need to change it to D: Drive path. How can I change it?

First, thanks for the question submission!  You can detach the database, move the file(s), and reattach using the CREATE DATABASE… FOR ATTACH command.

This is a SQL Server 2005/2008 example. For SQL Server 2000, you would use sp_attach_db/sp_attach_single_file_db to perform the attach.

For example:

Detach the database during a maintenance window – make sure you have a backup just in case something goes wrong:

USE master;
GO
EXEC sp_detach_db <db_name>;
GO

Now move the database file to the D: drive and reattach:

– Execute CREATE DATABASE FOR ATTACH statement
CREATE DATABASE
ON (FILENAME = ‘D:\\’) FOR ATTACH;
GO

Keep the questions coming!  We get a large volume of submissions and love seeing what the community is thinking about. 

David

No you haven’t.  I know you haven’t, because I was a DBA, and I hardly ever did it either.  Now that I work for the makers of LiteSpeed, I hear the horror stories from our support folks.  DBAs call up screaming in pain, and we can hear the boss loading the shotgun in the background.  The DBAs wrote backups to tape and the tapes got hosed, or maybe the backups never actually made it to tape because the DBAs wrote the backups to disk but the network staff never backed up that disk share.

Do yourself a favor – pick your three most critical databases, the ones that’d really give you heartburn if they disappeared, and go try to restore them onto a testbed server that has decent performance.  Time how long it takes, and mention it to your boss.

Mention it to your manager in casual conversation.  “Hey, Pointy-Headed Boss, I tested the restores today just like I’ve been doing every week (cough) and they worked great.  I never mentioned it to you before, but in case you’re curious, it’d probably take us about X minutes in order to recover in case the production server barfed.”

Your boss will probably say, “Why are you telling me this?”

You say, “I just thought you might want to know.  If that server dies, I want you to kinda have a feeling for how long it’ll take me to restore, because in the heat of the moment, you know, people kinda stand around the desk waiting for it to happen.  If the box explodes, I want you to be able to answer when your manager and your manager’s manager ask us how long it’s going to take.”

At that point, the fidgeting will start, and maybe they’ll ask you to make it happen faster.

Why am I telling you this?  Because budgeting time is coming up, and that’s a good way to segue into a conversation about getting more storage performance for your servers or getting a faster, dedicated backup share.

Good luck.  Let me know how it goes.  (And start testing your backups regularly.  Please.)

Update 10/2 – Funny Ha Ha – Jason Massie aka StatisticsIO.com posted a funny comic about testing your database backups.

Yes, this is bad because it can lead to your log shipped database getting out of sync and will probably result in Error 4305; error 4305 states that the log in a backup set is too late to apply, which means your log shipping jobs will fail until you can synchronize the databases. You should schedule your transaction log backup job to stop before your full backup job begins and then to restart once it has completed. See the Microsoft TechNet article on setting up Log Shipping.

When you perform a a full database backup SQL Server stores the ending log sequence number (LSN), which becomes the starting LSN for the next transaction log backup. So, if you are performing a transaction log backup while your full database backup is executing, and that transaction log backup is successful, once it is shipped and applied to the subscriber it will have a different starting LSN than the ending LSN of the full backup. This is not allowed and SQL Server will throw error 4305.

Here’s the actual question in its entirety:

We are trying to implement log shipping… We implement a transactional backup hourly. If our nightly full backup takes 1 hour and 45 minutes, what is our recovery process? Is it a bad thing to have a transactional backup in the middle of a full backup? 

Now, your question, whether you realize it or not, is pretty involved and has three major elements.  First, you want to know about implementing log shipping.  Second, you want to know about the recovery process considering your current backup process of hourly transaction log dumps and nightly full database dumps.  And finally, you want to know if a transaction log backup can cause problems if it occurs while a database backup is already processing. 

FULL DISCLOSURE - I’ll go ahead and say now that I’m going to include verbiage around a Quest product in this response – not a blatant advert, but I will be mentioning one of our products.

So, to your first question – remember that log shipping is a high-availability technique used to speed recovery of a database should you ever have a crash.  You don’t say which version of SQL Server you’re running.  But I assume that you must be running on SQL Server 2000, otherwise you would have mentioned other (and better, IMO) alternatives such as database mirroring.  Assuming you’re on SQL2000, then log shipping is not a big deal to implement and is widely discussed elsewhere on the internet, such as Microsoft’s TechNet (www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx) and MSDN (msdn.microsoft.com/en-us/library/ms187103.aspx).  I won’t rehash an elementary concept of SQL Server when others have already done an excellent job of telling you how to implement it.  Since you might still be in the planning stages of your high-availability solution, here are two caveats that are often overlooked by newbies.  One, don’t forget that the database that is the target of log shipping is going to be in recovery mode all the time until it becomes the primary database.  You pretty much cannot use it unless the source database fails.  Two, log shipping works great with LiteSpeed.  So, if you’re a Quest Software customer using LiteSpeed, then you can enjoy all the benefits of speed, reduced disk consumption, and encryption that LiteSpeed offers.

To your second question, your current backup process, in which you backup the transaction logs every hour and the database nightly, is pretty sound.  Keep in mind that you could potentially lose up to 59 minutes of data with any given database failure.  If 59 minutes does not represent a significant amount of work on the database in question, then great!  However, I rarely had a production database where I was comfortable making users re-key up to 59 minutes of work.  Fifteen minute intervals for a transaction log dump were more in my range of comfort.  Additionally, consider that most applications do NOT have users working around the clock.  Because of that very natural business cycle, you can often lessen or even completely discontinue transaction log backups during the wee hours.  For example, you might run the transaction log backups every 15 minutes from 6:00 AM EST to 8:00 PM EST every day, since you have few if any users after that time.  Alternately, you might run the log dumps ever 15 minutes from 6:00 AM to 8:00 PM EST, run them hourly from 8:00 – 10:00 PM EST, and then only do one more full database backup until the next morning.  Frequent log backups are important also because that’s the primary means by which you keep the transaction log from growing too large.

Finally, with your last question, starting a transaction log backup while a full database backup is still running will behave differently on different versions of SQL Server.  For SQL2000, SQL Server doesn’t allow you to run the transaction log backup while the full database backup is running (at least that’s what my fragile memory is telling me).  For SQL2005 and later, transaction log backups and full database backups can run concurrently though the transaction log backup will certainly be slower because of it and the database backup will likely be slower too.  The full database backup will contain all data in the state of the database at the time it completed, while the transaction log backup will contain all of the transactions that have run since the last full database backup OR transaction log backup.  Since the transaction log backup will probably finish before the 90 minute long database backup, you should think of that transaction log backup as the last log backup before the new full database backup starts the transaction log backup process all over again.  Thus, in an emergency restore situation, you could apply the previous day’s full database backup plus all transaction logs up to the time that the transaction log ran concurrently with tonight’s full database backup to get a recovery that included all transactions up to the most recent point in time.  Once the full database backup completes, however, the clock is started over and you would have to start applying transaction log backups that were subsequent to the completion of the full database backup.

Hope this helps,

-Kevin