Search the Community

The most efficient way I have found to backup my databases on my Cpanel Linux system is using a MySQL dump via a cron job. Here is the syntax: /usr/bin/mysqldump -u username -ppassword databasename | gzip -c > /home/Digitize Design/public_html/_db_backups/`date "+\%Y\%M\%d\%H\%m"`.filename.sql.gz All you need to do is replace "username", "password", "databasename", and the location of where to save the file. As you can probably tell it will also gzip the backup to save disk space. Click here to view the article

This is a quick and easy way to change the owner of an object if needed with sp_changedobjectowner sp_changeobjectowner 'oldownername.nameofobject', 'newowner' example: sp_changeobjectowner 'nathan.p_Customer1', 'nriley' The following changes the owner from "nathan" to "nriley"

The following script will allow you to search all the data in all the tables within a certain database. This is useful for poorly labeled databases or when you have no data diagram to work off. If you know a unique piece of data that you see within the application itself you can then query the database for it. However, I wouldn't recommend running it on a production server during business hours. All you need to do is replace "DatabaseName" with you database name. Then execute it and it will be written into your database. Then just call it with the string of text you are looking for example: SearchAllTables '%Important Data%' USE [DATABASENAME] GO /****** Object: StoredProcedure [dbo].[searchAllTables] Script Date: 07/30/2010 10:37:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[searchAllTables] ( @SearchStr nvarchar(100) ) AS BEGIN CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results END