>I was asked a question about how you make sure that your SQL Server database backups are secured and I ended up saying, “go get a third-party tool like LiteSpeed for SQL Server from Quest Software.” While it is true that third-party tools can provide additional layer of security on your database backups, budget is always a constraint. Good thing SQL Server 2008 has included a feature called transparent data encryption which you can use to secure your database backups. I have a written an article on MSSQLTips.com and created a video at BlogCastRepository.com about this feature which is worth having a look at.
>I woke up from a call from our support engineers telling me that a drive has less than 10% free space and needs to be maintained. This drive happens to be hosting my WSUS server together with the database used in the backend. There’s only one thing for me to do: move the database to a new location. This approach works for any SQL Server/MSDE database you want to move to a different location.
Step 1: Stop any service that is accessing the database
Since this is a WSUS database, we need to stop several services like the Update Service and the WWW service. You can do so using the Services applet or by using the NET STOP command
Since we do not have Enterprise Manager by default in using MSDE, we will stick to our command-line tool, osql. The sp_detach_db command detaches the specified database from SQL Server/MSDE. If you didn’t stop any service or application accessing this database, this command will fail. Below is the syntax for the sp_detach_db command(see MSDN as well)sp_detach_db [ @dbname= ] ‘database_name’
[ , [ @skipchecks= ] ‘skipchecks’ ]
To use the sp_detach_db in osql for the SUSDB database, execute this in the command line
osql -E -S %computername%\wsus -Q “exec sp_detach_db ‘SUSDB'”
I am more comfortable with executing TSQL scripts while logged in that’s why I make it a point to secure a logged in connection first before I execute them.
Step 3: Move the database files to a new location
Now that you have disconnected the database from the server, you can now treat it as any other file in your file system. Move the SUSDB.mdf and SUSDB_Log.LDF files (for any database, be sure you know which files correspond to which database by executing the sp_helpdb command)
Step 4: Re-attach the database using the sp_attach_db command
Execute the sp_attach_db in osql to re-attach the database files you have moved to a new location.
osql -E -S %computername%\wsus -Q “exec sp_attach_db @dbname=N’SUSDB’, @filename1=N’E:\WSUS\MSSQL$SUS\Data\SUSDB.mdf’, @filename2=N’E:\WSUS\MSSQL$SUS\Data\SUSDB_log.ldf'”
Step 5: Start the services which you stopped in Step 1
Resume the services or applications you stopped. Check whether the application still works fine and that it can still access the database.
This is a generic approach which can be used for any database running on MSDE or SQL Server, whether it’s WSUS or not. Note that if you are dealing with WSUS 3.0 which, by default, uses the Windows Internal Database (SQL Server 2005 Embedded Edition), check out this blog post to connect to this instance.
This post was originally posted ni this blog entry
It’s quite shocking to hear the word “undocumented” in SQL Server. This simply means that you can break your neck trying to find these topics in both the Books Online or Microsoft.com and you won’t find anything. But there’s always Google to take your chances. As I was trying to search for a VBScript to check for an existence of a file in a file system (it would be a lot easier in .NET but I’m limited to what I just have) when I chanced upon this extended stored procedure – xp_fileexist. DBAs do file system checks whenever they do data transformation or data loading from a delimited text file, an Excel spreadsheet or anything similar. If this was done by a batch job you need to check first whether a file exists based on a specific filename format (such as Test_yyyymmdd.xls). You can create a simple stored procedure to check for the formatting of the filename but to check whether the file exists before you to do your task is not something that you can do with SQL Server. Good thing there is such a stored procedure – xp_fileexist. This verifies whether a specified file exist. This stored procedure is located in the master database where you can execute it in the following syntax:
This will return three columns indicating if the file exists, if the file is a directory and if the file’s parent directory exists as a result set. You may want to include this in a script to do functional checks such as the example below
DECLARE @result INT
EXEC xp_fileexist ‘c:\autoexec.bat’, @result OUTPUT
IF (@result = 1) –@result will return 1 if the specified file exists, if it doesn’t, it will return 0
PRINT ‘file exists’
PRINT ‘file does NOT exist’
This is also helpful when you want to check for the existence of a database backup file as it has been recorded in the msdb database. One of the challenges I have is checking if a database transaction log backup still exists if a log shipping job starts failing.
Just remember that when using this stored procedure is a bit risky as Microsoft reserves the right to change or even remove this in future versions or even service packs without having any documentation (well, it really doesn’t have any after all) so make sure to test your scripts after applying service packs or upgrading.
And so I found out.
I was deploying an image to a server and wanted to try out VMWare Player instead. I am using version 2.0.3. Since the server has 4 processors, I was thinkng of configuring the image to use as much processors as are available. Now there are two ways to do this. First, you can use VMWare Workstation or Server to change the configuration of the VMX file. You can use the graphical user interface for this. In my case, since my laptop does not have multi-processors, I did encounter an error stating that the image will not start if I use more than one CPU. But it doesn’t matter since I am not going to start the image on my laptop anyway. I just need to change the settings so I can use those settings on a different machine.
Second, similar to how you do it in Microsoft Virtual PC where you change the VMC file, you can change the VMX file by searching for the numvcpus field and changing the value of the number, let’s say from 1 to 2.
Oh, and there’s one more thing: VMWare Player does not support more than 2 processors. And so I found out