Tag Archives: MSSQL

How to backup vCenter running SQL Express

Posted on by

I found a great way to backup the vCenter SQL database running on SQL Express. The downside of running vCenter on SQL Express is the lack of the SQL agent. So running a scheduled backup of the SQL database is a bit tricky.

ExpressMaint is a small application set of scripts/stored procedures that enables you to run backup and maintenance jobs from commandline.(You could run the application ExpressMaint as well, but I installed the stored procedure version.)

I installed and configured it by doing the following:

  1. I created c:\backups to store my backups
  2. I downloaded the ExpressMaint TSQL file.
  3. I opened a new cmd.exe (with administrative rights) and installed the Stored Procedure by running:
    sqlcmd -S .\SQLEXP_VIM -i c:\expressmaint.sql
  4. I created c:\backups\database to store my database backups
  5. I created c:\backups\reports to store my reports
  6. I created c:\backups\backupscript.sql (SQL file that contains the backup settings, note that my script performs the backup and rebuilds the indexes on the vCenter database):
    exec expressmaint
    @database = 'ALL_USER',
    @optype = 'DB',
    @backupfldr = 'c:\backups\databases',
    @reportfldr = 'c:\backups\reports',
    @verify = 1,
    @dbretainunit = 'days',
    @dbretainval = 5,
    @rptretainunit = 'weeks',
    @rptretainval = 1,
    @report = 1
    exec expressmaint
    @database = 'VIM_VCDB',
    @optype = 'REINDEX',
    @reportfldr = 'c:\backups\reports',
    @rptretainunit = 'days',
    @rptretainval = 1,
    @report = 1
  7. I created c:\backups\PerformBackup.cmd (this is the batch script that executes the backup):
    sqlcmd -S .\SQLEXP_VIM -i c:\backups\backupscript.sql
  8. I schedule c:\backups\PerformBackup.cmd using the ordinary Windows scheduled task manager.

Linked: Help! My SQL Server Log File is too big!!! | TechRepublic

Posted on by

Great post about shrinking SQL server 2005 log files.

Shrinking the File

Once you have identified your problem and have been able to truncate your log file,  you may need to shrink the file back to a manageable size.  You should avoid shrinking your files on a consistent basis as it can lead to fragmentation issues.  However, if you’ve performed a log truncation and need your log file to be smaller, you’re going to need to shrink your log file.  You can do it through management studio by right clicking the database, selecting All Tasks, Shrink, then choose Database or Files.  If I am using the Management Studio interface, I generally select Files and shrink only the log file.

This can also be done using TSQL.  The following query will find the name of my log file.  I’ll need this to pass to the DBCC SHRINKFILE command.

SELECT name FROM sys.database_files WHERE type_desc = 'LOG'

Once I have my log file name, I can use the DBCC command to shrink the file.  In the following command I try to shrink my log file down to 1GB.

DBCC SHRINKFILE ('SalesHistory_Log', 1000)

Also, make sure that your databases are NOT set to auto-shrink.  Databases that are shrank at continuous intervals can encounter real performance problems.

Help! My SQL Server Log File is too big!!! | TechRepublic.