A few months ago, I blogged about a simple procedure to do backup (http://sqlblog.com/blogs/tibor_karaszi/archive/2007/09/25/tsql-script-to-do-backup-like-maintenance-plans.aspx). Just to let you know that Ola Hallengren has some of his own. Much more elaborate than mine, including things like reorg. Check it out at http://ola.hallengren.com/.

Having a few moments to spare, I decided to give this a spin. Specifying for the backup to be compressed is really simple. Just specify COMPRESSION in the WITH clause of the BACKUP command. For example: BACKUP DATABASE Adventureworks  TO DISK = 'C:\Advc.bak'  WITH INIT, COMPRESSION For fun, I ...

How many times have you asked for a way to turn off the behavior in SQL Server that logs successful backup messages to the SQL Server Logs and to the Windows Application Event Logs? If you are like most DBA’s probably a lot. I have been asking for this feature for ages. I have sent email to SQLWish, filed on LadyBug and most recently on Connect ...

I was at the PASS Conference in Denver last week, as were many of my fellow bloggers. Although I didn't post a day-by-day report, I did actually learn some new things at PASS, and not just from the developers on the SQL Server team at Microsoft. In addition to learning new things, I also had a chance to have fun. The SQL Server MVPs went ...

This is frequently asked question, both in forums, as well as in class: How can I though TSQL create new backup files for each backup and have date and time in the file names? The reason for above is most often that you want a higher degree of customization than what maint plans allow for. An obvious such in 2005 is to add the CHECKSUM option to ...

Here's a suprising one: SQL Server 2005 introduced the COPY_ONLY option of the BACKUP command. This is relevant for Database backups. The backup will not intervene with your differential backups (will not reset the DCM pages). Log backups. The backup will not break the log chain (empty the log). There was a post in the newsgroup where a ...

This post is inspired from a discussion about bulk logged recovery model and log backup. Some basics: In bulk logged recovery model, some operations can be minimally logged, like: SELECT INTO Index create, rebuild and drop Bulk loading of data The interesting thing is that you can do a log backup after such a minimally logged operation. ...

I'm having a conversation with Tony Rogerson (see his great blog at: http://sqlblogcasts.com/blogs/tonyrogerson/) about whether or not BACKUP will grab paged from cache (Buffer Pool, BP) or always read them from disk. It seems natural to grab the pages in BP to eliminate some physical I/O. But thinking a bit more, I realized that the data in BP ...

What if I told you to take your latest production backup, restore it on a different machine and try using the database? Are you comfortable with that task? Do you think it will work? When was the last time you tested your backups? Do you even have a backup? Why am I asking all these things? Because your data is as good as your last good backup. ...

Is data available for a partitioned table if one of the partitions isn't available? Instead of reading BOL, I wanted to test this. Here is an outline of what I did (you find TSQL code at the end of this post): Create a database over several file groups Create a partitioned table Inserted some rows Database backup Inserted some ...