Generating SELECT INTO scripts for all user tables

Posted on by

Often on data recovery cases, we need to extract all the user table data into a new database. There are multiple options to perform this task:

1. Export/Import Wizard

2. Custom SSIS Task

3. Copy Database Wizard

4. BCP operations

5. SELECT INTO T-SQL commands

I find that the last option is the easiest. I use the following script to get all the T-SQL SELECT…INTO commands for all user tables in the old database:

 select 'select * into <new db>.'+b.name+'.['+a.name+'] from <old db>.'+b.name+'.['+a.name+']' from sys.objects a inner join sys.schemas b on a.schema_id = b.schema_id where a.type = 'U'

Note: You will have to replace the new database and old database names with the appropriate names. The above script only works for versions of SQL Server 2005 and above.

How to convert a LiteSpeed backup into a native SQL backup

Posted on by

I know all you folks out there are probably more proficient at playing around with LiteSpeed backups than I am. Recently, I had to play around with LiteSpeed backups on a regular basis while troubleshooting a particular case for a customer. I knew that there was a utility called Extractor for LiteSpeed which can convert the LiteSpeed backups into native SQL backups. 

Parameters: 

-F|–BackupFile {path}        Path of the backup or restore file devices 

-N|–BackupIndex  {n}       File number (If multiple backups appended within BackupFile(s))

-E|–MTFFile {path}            Path of the Microsoft Tape Format file(s) to be written.  Specify either one or the correct number of files.  If only one file is named, set will be created with index suffixes. If no files listed, Extractor will print number of MTF files to be written.

-K|–Key {key}                 String used to decrypt database backup file(s) Only required if backup is encrypted.
-I|–Overwrite                   Overwrite MTFFile(s). Equivalent of INIT in native SQLServer backups

-L|–LogLevel {n}              Logging option

Example: 

Extractor.exe -FC:\LiteSpeedTran.BKP -EC:\NativeBackup.BKP -N1  

NOTE: To use Extractor utility, you do not need LiteSpeed to be installed on the box. (But the licensing policies and EULA still apply if you are using Extractor) 

How to get backup header information for LiteSpeed backups 

If you have LiteSpeed installed on your box, then you can executed the following XSP command to get a RESTORE HEADERONLY output for a LiteSpeed backup file. This will give a similar output that the T-SQL command RESTORE HEADERONLY provides: 


exec master.dbo.xp_restore_headeronly @filename =  'C:\LiteSpeedTran.BKP'

Hope this information is helpful for individuals whose working knowledge is at not an EXPERT level with this product. I am a LiteSpeed noob!

Did you know about the –m startup parameter

Posted on by

I am sure –m is startup parameter that most number of DBAs have used at one point of time or the other during a disaster recovery scenario involving an issue with system databases or even while moving system database files.

There is a hidden gem about the –m startup parameter which most of us are not aware of or do not use often when required. That is the Client Application Name filter along with the –m parameter. I have gotten frustrated multiple times when I started the SQL instance in single user mode and a sysadmin connection latched onto the only connection (read: SQL Performance monitoring app, CRM apps, or any other 24X7 LOB apps).

If you want to start SQL Server in Single User mode using –m startup parameter, then you can now prevent other applications from connecting to the instance by specifying the restricted Client Application Name which can only connect to the instance. Note that this is a case sensitive parameter. Before specifying the Client Application Name filter, check what the program_name is showing your application name in the sys.dm_exec_sessions DMV output.

If you are doing this for SQL instance maintenance operations, then specify the filter as “SQLCMD” or “Microsoft SQL Server Management Studio – Query” as you should be able to run your scripts from either of the two applications SQLCMD or SSMS.

No more tearing your hair out to figure out who took up the sole connection to the SQL instance when you don’t want that lone wolf to be in demand by other applications.

[Blog Update] June posts on SQLServerFAQ

Posted on by

I recently worked on a soft-NUMA configuration issue for SQL Server 2008. I decided to document the steps to configure soft-NUMA in a blog post on SQLServerFAQ on MSDN. Here it is:

How to set Soft-NUMA for SQL Server 2008 R2

Happy NUMA configuration! 🙂 It can be quite daunting if you have multiple CPUs.

SQL Server Version Numbers

Posted on by

Understanding SQL version numbers can be a bit confusing at times and remembering the builds an even bigger hassle. I just replied with the following post on MSDN recently. I thought I would convert the post into a blog post for other people to see and use as appropriate.

SQL Server has the following components:

1. Database engine (Engine, Replication, Full-text)

2. SSIS – Integration Services (SQL Server 2005 and above)

3. Reporting Services

4. Tools and Shared Components (Eg. SSMS)

5. Notification Services (SQL Server 2005 and above)

6. Analysis Services

When you install a SQL Server component, the RTM version of that component is installed. The SQL Server release version has a version major number which will stay constant for a release. The 4 digit number that keeps varying is the build number of the current version of your component. This number changes whenever you apply a hotfix/security fix/cumulative update/service pack for that particular component of SQL Server. Very rarely do the build numbers remain constant across patches (Seen to happen in Reporting Services for certain builds).

Version major numbers: This is the first number in the version output:

SQL Server 7.0 – 7.00.XXXX

SQL Server 2000 – 8.00.XXXX

SQL Server 2005 – 9.00.XXXX

SQL Server 2008 – 10.00.XXXX

SQL Server 2008 R2- 10.50.XXXX

The above article has all the build numbers that were released for SQL Server.

Additional reference:

http://support.microsoft.com/kb/321185

http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx