How to Delete Maintenance Plan in SQL 2005
Step 1:
DELETE FROM MSDB..SYSMAINTPLAN_PLANS
Step 2:
DELETE FROM SYSMAINTPLAN_LOG
Step 3:
DELETE FROM SYSMAINTPLAN_SUBPLANS
Step 4:
DELETE FROM SYSMAINTPLAN_PLANS
Backup SQL Server database accross more than one file
–====================================================
–= BACKUP database accross four files
–====================================================
declare @filedate nvarchar(100)
declare @disk1 nvarchar(100)
declare @disk2 nvarchar(100)
declare @disk3 nvarchar(100)
declare @disk4 nvarchar(100)
declare @dbname nvarchar(100)
declare @backupname nvarchar(100)
declare @backupfolder nvarchar(100)
set @dbname = ‘DBA’
set @backupfolder = ‘F:\SQLBackup\’ + @dbname + ‘\’
select @filedate = replace(replace(replace(replace(convert(nvarchar,getdate(),121),’-’,’_’),’ ‘,’_’),’:’,”),’.’,’_’)
select @backupname = @dbname + ‘_backup’ + @filedate
print @filedate
print @backupname
–select @filedate
select @disk1 = @backupfolder + @dbname + ‘_backup_File1_’ + [...]
How to create a corrupt database using BULK INSERT/ UPDATE and BCP - SQL Server as a HEX editor.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/10/how-to-create-a-corrupt-database-using-bulk-insert-update-and-bcp-sql-server-as-a-hex-editor.aspx
SQL Free Tools
http://www.spiceworks.com/free-sql-server-monitoring-tool/
How to find all the FILESTREAM directory names - for both levels of directory
You have to run this query through the DAC as it’s accessing undocumented, hidden system tables.
How to: Use the Dedicated Administrator Connection with SQL Server Management Studio
Microsoft SQL Server provides a dedicated administrator connection (DAC). The DAC allows an administrator to access a running instance of SQL Server Database Engine to troubleshoot problems on [...]
Using a Dedicated Administrator Connection
SQL Server provides a special diagnostic connection for administrators when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.
By default, the connection is only allowed from a client [...]
Starting SQL Server
From Command prompt:
Start an instance of SQL Server or the SQL Server Agent service from a command prompt by typing:
net start mssqlserver or sqlservr, or net start SQLServerAgent or by running SQLSERVR.EXE. If you are referring to a named instance of SQL Server, you must specify mssql$instancename or SQLAgent$instancename.
Run Windows Installer CleanUp Utility after reinstalling SQL Server
having problem with reinstalling SQL Server? Can’t move data file during installation? Use Windows Installer CleanUp Utility.
http://support.microsoft.com/default.aspx/kb/290301
Moving model system database on SQL Server 2005
I moved model system database today from default location to E:\SQLData using following steps:
1. NET START MSSQL$LIVE /f /T3608
2. ALTER DATABASE model MODIFY FILE (NAME = modellog, FILENAME = ‘E:\SQLData\modellog.ldf’);
3. ALTER DATABASE model MODIFY FILE (NAME = modellog, FILENAME = ‘E:\SQLData\modellog.ldf’);
4. NET STOP MSSQL$LIVE
5. NET START MSSQL$LIVE
Everything working fine.
