|
|
Alla Taggar
All Tags » Backup (RSS)
-
Ola has a set of stored procedures to do maint operations, see http://ola.hallengren.com/ . Ola has now updated them to support exclusions or inclusions of tables, indexes or even whole schemas from index rebuild/reorg. Check out http://ola.hallengren.com/Documentation.html#SelectingIndexes...( read more) 
|
-
All my attempts so far to rebuild the system databases in 2008 R2 has failed. I first tried to run setup from below path: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release But above turns out to be the 2008 setup program, not 2008R2 setup;...( read more) 
|
-
Short answer seems to be "yes". I got this from a forum post today, so I thought I'd test it out. Basically, the discussion is whether we can somehow cut down backup sizes for filestream data (assumption is that filestream data isn't modified very frequently)....( read more) 
|
-
This is really basic, but so often overlooked and misunderstood. Basically, we have a database, and something goes south. Can we restore all the way up to that point? I.e., even if the last backup (db or log) is earlier than the disaster? Yes, of course...( read more) 
|
-
Seems like a simple enough question, right? This question (but more targeted, read on) was raised in an MCT forum. While the discussion was on-going and and I tried to come up with answers, I realized that this question are really several questions. First,...( read more) 
|
-
Are you stupid, you might think... But stop and think for a while. Model is no different from other databases. And by default it is in full recovery model. So as soon as you do your first database backup (you do backup your system databases, right?) the...( read more) 
|
-
To many, this is a non-issue, since SSIS is installed anyhow. But not everyody installs SSIS. I for instance prefer to keep my production systems as clean as possible and only have what I really need (a principle which is harder and harder to live after...( read more) 
|
-
The story usually goes something like: Q - How can I restore only this table? A - Put it on its own filegroup and you can do filegroup level backup and restore. The problem with above answer is that it most likely misses the point. We need to ask ourselves:...( read more) 
|
-
I was replying to a newsgroup post today, explaining the restore process. I find some confusion in various places about what actually happens during restore, and hopefully below can help a bit:
Fact: All backups contains log records. A log backup contains only of log records (more later on bulk-logged recovery). The different types of database backup contain the log records that was produced while the backup was running - in addition to data pages.
The restore command can perform several things:
- Delete the existing database. This happens if you specify the REPLACE option.
- Create the existing database. This happens if the database name you specify in the RESTORE command doesn't exist.
- Copy data pages to the same address in each database file as they were when the backup was produced. And of course also copy log records to ldf file. Source for these copy operations is the backup file that you restore from.
- REDO. Roll forward transactions based on the log records copied in above step.
- UNDO. Rollback all open transaction. Not performed if you specify NORECOVERY (more later on STANDBY option). Database is now accessible, and no further backups can be restored (diff or log backups).
A couple of special cases:
If you are in bulk-logged recovery model, then a log backup performed if you did minimally logged operations since last log backup will contain also data pages (in addition to log records). This type of backup cannot be performed is any of the data files are lost. When you restore from this type of backup, you cannot do point-in-time restore.
The STANDBY option of the RESTORE command does perform UNDO but saves that work to a file you specify. This so that the UNDO work can be undone when you restore a subsequent backup.
I think I managed to compress the topic pretty well, so unless you worked a bit with backup and restore in SQL Server, you might want to read above a couple of times. :-)
 
|
-
You might have read an earlier blog entry about my problems to remove a Data Collector (DC) information in SQL Server 2008. I still haven't found any info on how to do this, but my questions in the MVP group triggered some activity.
Bob Ward looked into how to rebuild the system databases. This was on my list to try (after removing DC config and if that didn't work rebuilding msdb). But Books Online had very little information on how to actually do the rebuild. Since there were quite many changes in setup between 2005 and 2008, I didn't feel like trial and error based on how we did this in 2005.
Bob helped me out with some information on how to do the rebuild and it is very easy! I now have a bat file which does rebuild of three instances - and it only takes 5 minutes. And even better: no installation media is required - and it also remembers the collation for my system databases!
Enough said. Check out Bob's blog post at: http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx
|
-
Because of the problems I had removing Data Collector I decided to rebuild msdb. You probably heard about instmsdb.sql, but it was a long time since I actually used it. I asked about rebuilding in the MVP group and Dan Jones (MS) pointed me to a Blog post from Paul Randal on how to do this on SQL Server 2005. Here's Paul's blog post:
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx
Since above is for SQL Server 2005 I realized that it might not work smoothly on 2008. And It didn't. Below are some of the things I discovered (also posted as a comment on Paul's blog). Read below in light of Paul's blog. I should also say that nowhere does Paul states that his instructions work on 2008. It was me taking a chance. :-)
You need to add startup parameter -s <instancename> if it is a named instance. Now, this I knew, but for the sake of other potential readers...
I initially started the instance from the Windows services applet by adding -T3608. That didn't allow for detaching msdb. So I started from an OS command prompt and also added -c. This allowed me to detach msdb.
I now ran instmsdb, but that produced a number of errors. Here are a few comments about some of them:
* Complaints on xp_cmdshell. I did try enabling this first and then ran instmsdb again but same result.
* Bunch of errors when creating various Data Collector objects. This wasn't good, because cleaning up DC was the reason to rebuild msdb in the frist place.
* 3 errors about sp_configure and -1 wasn't allowed value (two for Agent Xps and one for xp_cmdshell).
Just for the sake of trying, I now tried to connect to the instance using SSMS Object Explorer. But I now got some error regarding Agent Xp's when connecting. I tried to explicitly enabling Agent XP's using sp_configure but same error. When connected there's no node in Objects Explorer for Agent.
I took this as an indication that Agent isn't healthy. Whether it was me doing something fishy or it isn't as easy as just running insmsdb.sql for SQL Server 2008 - I don't know. But I'm in for a rebuild of system databases. This isn't that bad since it is a just a test machine. But these issues might serve as example why you want to follow Paul's initial advice: always backup msdb (also on test machines).
|
-
I finally got to spend some time on how to backup my machines, my home machines and laptop. This was triggered from when I got back from a week in Egypt and my LaCie NAS wouldn't start. I finally did manage to get it started by connecting through USB and whacking it with a hammer. However this triggered something I've been thinking about for a while - to buy a backup disk (NAS) with RAID 1 and implement a "proper" backup stragegy.
I decided for QNAP TS-209 Pro II which is Linux based. This device supports loads of stuff (DLNA, FTP, web server, DDNS, printers, mySQL etc), but I will at least initially only use the file server bit (Samba). I do not want a fullblown PC for this, since I want something simple, which is running all the time with low energy usage, doesn't produce too much heat, doesn't expect a monitor etc. So, a NAS seems just about the right thing for me. This unit was also simple to install and setup. Here's a simplification of my environment:
- My "main" machine is my desktop machine.
- I also have a laptop, which is what I'm using when I'm at customer sites and when I do training.
- I have an USB drive mainly used on above laptop.
- And of course the NAS.
- There are other machines as well (including USB disks), but I won't discuss them here since they don't change the basic principles.
I've already a long time ago decided on the concept of ownership. I.e., some folder is owned by some particular machine.
Some 3-4 years ago, I had a disk crash which made me lose 2 weeks worth of email. I decided that I cannot rely on manually copying folders around. Perhaps my most important folder is a "document" folder (where I among other things have the outlook PST file) on my desktop machine. I wrote a .NET console program which reads some config info from a local SQL Server and then creates a folder with current date as name and copies the content of my "document" folder to this new folder. After this is done, it removes every folder older than 1 week - except for folders created on day 1 in the month. I put this in the startup group. This has served me well for this "document" folder - mainly because it is small in size (some 200 MB). But over time, things has become a bit more complicated. A few examples:
- I have a "courseFiles" folder on my laptop and this should be owned by the laptop. I might for instance do some modification for a demo-file when I'm doing training.
- I have over time realized that some stuff are too large to have in the "documents" folder, like SQL Server videos I've produced.
- I have virtual machines. I can't have them copied every time I start my machine including many many generations of them.
- I have ghost images which includes virtual machines. Same applies as above.
So it was time to expand on my simple "documents" backup solution. I have some very important aspects:
- I can reinstall OS and applications. I already have a small script which produces a file with what apps are installed (autostarted) so I know what to reinstall if I have to whack a machine. So, no backup of binary files.
- I don't want to virtualize everyting. I don't feel like paying the penalty for it. I'm too pedantic, so I know I can spend days just to get this little thing working in virtual environment - and I don't have that time. Also, I don't see how virtualization will change anything. I will still have "productivity OS's" where I have important files which I need to backup.
- A backup need work with pure files, same folder structure as source. I.e., I don't want to rely on some backup app whenever I need to restore. Nor do I want some "diff" strategy only to realize that my base is corrupted.
- A folder has an ownership. This is the machine which owns the folder. For instance, the "documents" folder is owned by my desktop machine - any changes done in that folder on my laptop should be discarded and dissapear.
- The NAS is the backup station. This is where all backups go. Some folders I also want on some other machine ("documents" on laptop, "courseFiles" on desktop) but such s folder should be treated as read-only.
- I want my backup files distributed and independent of each other. I.e., I do NOT want a distributed system which fails if one machine is lost (think RAID0). What I DO want is a distributed system which can surive several failures - like house burning down and several machines lost (think distributed RAID 1 with several mirrors). Now, don't mistake my RAID analogy for some real-time replication solution - since I want to be able to find an older versone of a file on some backup machine if I happen to destroy the owning file.
- Having only one machine (laptop) is not an option for me.
I found a backup program which suit my needs: SyncBack from http://www.2brightsparks.com/. This has a lot of features and functionality, and it allow the level of customization that I need. Here's how I have created my backup definitions (one per folder):
- I have three root folders on my NAS "backup" share. One per source (desktop, laptop and laptop USB disk).
- For each folder, I create a backup job on each owning machine, per folder that the machine owns.
- For some folders, I also create a "downstream" backup. Say for instance the "courseFiles" folder. This is owned by the laptop, but I want to have a copy available on my desktop machine as well. Of course I have an "upstream" backup definition from my laptop to the NAS. But I also have a "downstream" job from the NAS to the desktop machine.
- All backup definitions are defined as source always win (not newest file), and delete file if exist on target but not source. This is important since it implements pure one-way "replication".
It took me a couple of hours to learn the backup app and setup the jobs. (It took even longer time to do the initial copying of folders to my NAS (something I did before setting up the backup jobs) - but this was only because sheer volume.) But thaks to SyncBack (complemented with my own small applet for generation type backups) I now have a backup solution which is easy to understand and hopefully can survive multiple failures.
|
-
-
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 compared backup file size and backup time between compressing and not compressing. I ran below after priming the cache (not that it should matter since backup read pages from disk, see http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/03/does-backup-utilize-pages-in-cache.aspx). I also deleted the backup files, if they exist, before execution.
DECLARE @dt datetime
SET @dt = GETDATE()
BACKUP DATABASE Adventureworks
TO DISK = 'C:\Adv.bak'
WITH INIT
SELECT DATEDIFF(ms, @dt, GETDATE())
SET @dt = GETDATE()
BACKUP DATABASE Adventureworks
TO DISK = 'C:\Advc.bak'
WITH INIT, COMPRESSION
SELECT DATEDIFF(ms, @dt, GETDATE())
Size of backup file is 129 MB vs. 35 MB. Of course, compression ratio varies depending on what type of data there is in the database (string data tend to compress better than other data,. for instance). Time to execute commands were 7.5 seconds vs. 3.8 seconds. Now, this is a virtual machine on VPC and Adventureworks is a tiny database. But at least we can see clear potential for savings here.
So how do we know if a database backup was compressed? If you've used RESTORE HEADERONLY, you probably noticed this annoying column called "Compressed". Well, this is the first time we see a value of "1" in that column.
It seems we cannot mix compressed and non-compressed backups on the same file, though. (Not that I often have multiple backups on the same file.) If I append a backup using COMPRESSION on a file were there already are non-compressed backup, I get an error. Or I do a backup without COMPRESSION on a file where there are compressed backups, the new backup will be compressed (even when not saying COMPRESSION). Something to look out for if you have several backups on the backup files.
So what about the RESTORE command? Well, there's nothing to say, really. You don't have to specify in the RESTORE command that the backup was compressed. Time for RESTORE was 10.4 vs 6.3 seconds (with the destination database already existing). I expect the difference to be bigger on real installation and realistic db size.
|
|
|
|