SQL Server Mailbag: Data restoration and DB property management

In this edition of the SQL Server Mailbag, database expert Denny Cherry tackles reader questions on recovering data after a disc crash (and no backup), exporting reports to Microsoft Excel with autofit columns and more.

Have a question of your own? Send it along to editor@SearchSQLServer.com.

I am trying to restore a 7 GB database in SQL Server 2005 (full restore) and the action complains that it needs 10 times more disk space to allow me to do this. Is there any way to work around this problem? I don't have the disk space in my server and it's crucial to do the restore (I also don't have access to the original database). -- Pedro

I assume the database has either a large transaction log or a lot of white space within the database. Without having access to the original database, you'll need enough space to restore all of the white space or log space to the server. If you had access to the original database, you could truncate the log and then shrink the files.

Are database properties -- like data integrity, isolation etc. -- maintained in the database management system (DBMS) architecture or does a programmer have to maintain them in the code? -- Ansul

Data integrity is stored within the database as foreign key constraints, while transaction isolation is handled via the database. However, you can select the transaction isolation level that is used by either creating a transaction in the client software, or within the T-SQL code or stored procedure.

Can the SQL Server 2005 Native Client can be deployed via Windows Server Update Services (WSUS)? -- Iain

I don't see a way to import the SQL Native Client into WSUS. However, you can use a Group Policy Object (GPO) to deploy the SQL Native Client drivers via software distribution.

I have a problem: I ran SQL Server with no backup. After a disc crash, I could restore the .MDF file but the file appears corrupt. How can I recover the data in a new database? Are tools like Nucleus Data Recovery good? -- Sascha

More answers from Denny How do I write a stored procedure for an INSERT query? Can I downgrade from Enterprise to Standard Edition?

You can try attaching the database using sp_attach_db or sp_attach_single_file_db to get the database back into the SQL Server; Then run DBCC CHECKDB on the database. You may have to put the database in emergency mode before running DBCC CHECKDB on it.

If the database can be recovered, it will tell you what repair level to use. The DBCC CHECKDB tool may have to be run several times to correct the corruption.

Since I've never used third-party tools to recover a database file, I can't vouch for their effectiveness.

I am trying to open a .BAK file in order to import it to another database file. I do not know whether to use Microsoft Excel or Access. My main problem, however, is that I am not able to open the .BAK file! How would I do this? -- May

The only way to open the .BAK file is to restore it to a SQL Server instance using SQL Server Management Studio or the RESTORE DATABASE command. You can then query for -- and import the data with – Microsoft Access, Excel, etc.

I am trying to execute a .bat file on the SQL Server 2005 server root drive to move a file from C: to the mapped network drive J:.

The code is within a procedure:
EXEC master..xp_cmdshell
'c:move_ViewWise_file.bat'

The .bat file:
copy c:ViewWise_Files*.* J:"CSV Files"Archive /Y move /Y c:ViewWise_Files*.* J:"CSV Files"

Error message from Proc:
The system cannot find the drive specified.

I have no trouble running the .bat file from the command prompt on the server. What do I need to add to the proc so it can see the mapped drive? -- Jeff

Since the batch file runs under the context of the SQL Server account, it can't see the mapped network drive. The best practice is to always use UNC network paths instead of mapped drives because you can't be sure that the mapped drive will be available.

How I can export SQL Server reports to Microsoft Excel with autofit columns? -- Zafar

If you set the column sizes to autosize in the blank Microsoft Excel spreadsheet you plan on exporting to, the columns should be autosized when the spreadsheet is opened. Since there is no guarantee this will work, you may want to create a macro which autosizes the columns when the spreadsheet is opened.

I wrote a VB.net using Visual Studio 2008 that will load a combo box in the client with all the SQL Servers on the network (similar to SQL Server Management Studio). The client has SQL installed, and it won't discover the SQL Servers on the network. I tried installing the client components but it didn't work.

My question is how do I include the SQL Server Browser on my installation? I don't want the whole 400 MB install, just the minimum to have the browser on the client running. I used ClickOnce to install my program from a webpage. It's OK if I can't use ClickOnce, I just need to know what MSI to install. My program is designed to help DBAs with their daily work. It has predefined scripts and can also run user scripts and a few small utilities. -- Oscar

To do this, you'll need to have the SMO library installed on the machine -- the SQL Browser isn't required.

You'll want to reference these libraries:

Microsoft.SqlServer.Management.Smo
Microsoft.SqlServer.ConnectionInfo

Then, use this code to get the server list:

Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
     'True lists the local servers, set to false if you want to see all servers
 Dim dataTable = SmoApplication.EnumAvailableSqlServers(False)
    lstServers.ValueMember = "Name"
    lstServers.DataSource = dataTable
End Sub

I swiped the above code from the article Programmatically List All SQL Servers And Their Databases Using SMO on DevPinoy.org.

Have a burning SQL Server question of your own? Let us know at editor@SearchSQLServer.com, or pose it to our IT Knowledge Exchange forum for fast responses from your peers.

ABOUT THE AUTHOR:

Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP. Check out his blog: SQL Server with Mr. Denny