Backup and Restore

Changed SoundEx Disables Index While Upgrading to SQL 2012

by

Implementation of SoundEx function is changed in SQL Server 2012, described here. For an example, the result of SOUNDEX(‘Csomorova’) in SQL Server 2012 is C561 whereas the result of it from SQL Server 2008 is C256. It does not seem very significant to our life. But it gives you surprise when this function is used … Read more

Bring Offline File Online — Modify SQL Server Metadata

by

In my last post, I demonstrated how to mount a database with missing NDF files. In the end, we still have issues removing tables created over missing files, the files are taken offline. Be cautious of taking files offline since there is no (official) way to bring it back online. In either situation, you will need to modify/correct SQL Server metadata. This is allowed in SQL Server 2000 with “Allow ad hoc update” option turned on. However, it’s deprecated in SQL Server 2005 and removed in SQL Server 2008 and upper versions.

Read more

Attach Database with Missing NDF File

by

I wonder if you’ve had the situation that I had before where you have to attach a database with one or few missing .ndf files. It seems pretty common since I’ve just seen the same scenario at one of my clients. The reasons behind it are quite similar – They created a new file group with files to host some temporary data for data fixing or testing. New files were not created under the folder as usual and they thought they would remove them right after tasks complete. But the latter part got forgotten afterwards.

Read more

Restore Database With Partitions Excluded (or Selected Partitions)

by

Refreshing QA environment is not an easy work when your backup files are terabytes in size. The challenges are not only on space but also on time. Some tables are never envolved into QA phase. Restoring them will waste of your time and space. If you put tables in different file groups, then you would have the option to not restore them. If they are accessed by QA test, you can rename existing corrupted table (file groups are not restored) and create an empty table to make the logic correct. Now, if there is a huge table holding the historical data for the system, QA only need the latest 2 partitions of worth of data for testing, most of the DBAs in this case will restore entire database fist, switch useless partions out of the table, drop the table and file group. What if you have space limitation that only allow you restore part of the database? The solution would be

  • Restore database with primary file group and desired file groups
  • create table empty table which has the same structure as original, including the partition scheme
  • Swith the partition from existing table to the empty table
  • Rename tables

That’s a big work especially when you have hundreds of partitions across multiple tables. Here I post my script for that. It supports full, differential, log, and point in time retore, as well as multiple partitions over tables.

Read more

Cold and Hot Backup

by

There are always a chance databases suffering from severe system failure and hence causes database corruption, for instance, memory failure, hard drive failure, power failure and so on. Once the database gets corrupted, it’s almost hopeless to recover it without backup. So it’s very important to backup the databases for any database systems. There are two big categories in backup world – cold backup and hot backup.

Read more