/***********************************************
* This gives users a dummy default database
* in order to prevent them from:
* 1) having ‘master’ as a default database
* 2) not being able to connect to the server
* because their default database has been
* dropped, recreated, or restored
***********************************************/

USE master
GO

IF NOT EXISTS ( SELECT TOP 1 1 FROM sysdatabases WHERE name = ‘uno’ )
BEGIN
CREATE DATABASE [uno]
END
GO

ALTER DATABASE [uno] SET READ_WRITE
GO

USE uno
GO

GRANT SELECT TO [public]
GO

DECLARE @name sysname
, @sql nvarchar(max)

DECLARE login_cur CURSOR FAST_FORWARD FOR
SELECT name
FROM master..syslogins WITH (NOLOCK)
WHERE upper(name) LIKE ‘%’

OPEN login_cur

FETCH login_cur INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘IF NOT EXISTS ( SELECT TOP 1 1 FROM sysusers WHERE name = ”’ + @name + ”’ ) ‘
SET @sql = @sql + ‘CREATE USER [‘ + @name + ‘] FOR LOGIN [‘ + @name + ‘] WITH DEFAULT_SCHEMA=[dbo];’
EXEC sp_executesql @sql
SET @sql = ‘ALTER LOGIN [‘ + @name + ‘] WITH DEFAULT_DATABASE=[uno];’
EXEC sp_executesql @sql
FETCH login_cur INTO @name
END

CLOSE login_cur
DEALLOCATE login_cur
GO

–ALTER DATABASE [uno] SET READ_ONLY
–GO

USE master
GO

DECLARE @tabname sysname
, @sql nvarchar(256)
DECLARE tab_cur CURSOR FAST_FORWARD FOR
SELECT name
FROM sysobjects WITH (NOLOCK)
WHERE type = ‘U’
AND ( name LIKE ‘L_%’ OR name LIKE ‘T_%’ )

OPEN tab_cur
FETCH tab_cur INTO @tabname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP TABLE ‘ + @tabname
EXEC sp_executesql @sql
FETCH tab_cur INTO @tabname
END

CLOSE tab_cur
DEALLOCATE tab_cur
GO

USE {DATABASE_NAME}
GO

——————————————————–
— Drop Procedures
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)

DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE type = ‘P’

OPEN obj_cur

FETCH obj_cur INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP PROCEDURE ‘ + @objname
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname
END

CLOSE obj_cur
DEALLOCATE obj_cur

GO

——————————————————–
— Drop Functions
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)
DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE type IN (‘FN’,’IF’,’TF’)

OPEN obj_cur

FETCH obj_cur INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP FUNCTION ‘ + @objname
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname
END

CLOSE obj_cur
DEALLOCATE obj_cur

GO

——————————————————–
— Drop Views
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)

DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE type = ‘V’

OPEN obj_cur

FETCH obj_cur INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP VIEW ‘ + @objname
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname
END

CLOSE obj_cur
DEALLOCATE obj_cur

GO

——————————————————–
— Drop Constraints
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)
DECLARE @tabname sysname

DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT A.name [const_name]
, B.name [table_name]
FROM sysobjects A
, sysobjects B
, sysconstraints C
WHERE A.id = C.constid
AND B.id = C.id
AND B.name NOT LIKE ‘sys%’
AND A.type IN ( ‘F’, ‘K’ )
ORDER BY A.type, 2 DESC, 1

OPEN obj_cur
FETCH obj_cur INTO @objname, @tabname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER TABLE ‘ + @tabname + ‘ NOCHECK CONSTRAINT ‘ + @objname
PRINT @sql
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname, @tabname
END

CLOSE obj_cur

OPEN obj_cur
FETCH obj_cur INTO @objname, @tabname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER TABLE ‘ + @tabname + ‘ DROP CONSTRAINT ‘ + @objname
PRINT @sql
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname, @tabname
END

CLOSE obj_cur
DEALLOCATE obj_cur
GO

——————————————————–
— Drop Tables
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)
DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE type = ‘U’

OPEN obj_cur

FETCH obj_cur INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP TABLE ‘ + @objname
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname
END

CLOSE obj_cur
DEALLOCATE obj_cur
GO

USE [DATABASE_NAME]
GO

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT table_name
FROM information_schema.tables WITH (NOLOCK)
WHERE table_type = ‘base table’

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor
GO

USE [dbname]
GO

SET NOCOUNT ON
DECLARE @tabname sysname
DECLARE @sql nvarchar(max)
DECLARE @rows int
DECLARE table_cur CURSOR FAST_FORWARD FOR

SELECT name
FROM sysobjects
WHERE type = ‘U’

CREATE TABLE #results_tmp ( tabname sysname, cnt int )

OPEN table_cur
FETCH table_cur INTO @tabname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘INSERT INTO #results_tmp SELECT ”’ + @tabname + ”’, count(1) FROM ‘ + @tabname
EXEC sp_executesql @sql
FETCH table_cur INTO @tabname
END

CLOSE table_cur
DEALLOCATE table_cur

SELECT cnt, tabname FROM #results_tmp ORDER BY cnt DESC
DROP TABLE #results_tmp
GO

USE [db]
GO

–delcare variables
DECLARE @i int
DECLARE @lastrow int
DECLARE @curPoc int
DECLARE @curDir int
DECLARE @lastDir int
DECLARE @order int

DECLARE @pocs AS TABLE
( rownum int IDENTITY(1, 1) PRIMARY KEY NOT NULL
, pocID int NOT NULL
, fk_directorateID int NOT NULL
)

— this builds a var table that is pre-sorted according to criteria
INSERT INTO @pocs ( pocID, fk_directorateID )
SELECT A.pocId
, A.fk_directorateID
FROM t_poc A (NOLOCK)
WHERE A.isArchived = 0
ORDER BY A.fk_directorateID
, A.orderBy
, A.pocID

SELECT @lastrow = max(A.rownum)
FROM @pocs A

SET @i = 1
SET @lastDir = 0
SET @order = 0

–loop through all POCs in POC table
WHILE @i

To prevent the C: drive from filling up, it is a good idea to set the database default location. To do this:

1. Open SQL Server Management Studio
2. Right click the server instance
3. Select “Properties”
4. In the Server Properties window, select “Database Settings”
5. Under “Database default locations”, specify path for “Data:” and “Log:”, for example: “D:SQLDATA”

Additionally, if space on the C: drive is limited, check the properties of the TEMPDB.

This can be found under the “Databases” –> “System Databases” branches in the server’s tree-view.

First, since the tempdb does not autoshrink, you can manually shrink it by right-clicking tempdb and selecting “Task”–>”Shrink”–>”Database”.

Next, right-click the tempdb database and select “Properties”. Then select “Files”. You can set the “tempdev.mdf” file to be restricted growth and add an additional database file that is unrestricted on another drive.

To rebuild a SQL 2005 master database:

start /wait setup.exe /qn
INSTANCENAME= REINSTALL=SQL_Engine
REBUILDDATABASE=1 SAPWD=

In SQL 2008 use:

setup.exe
/QUIET
/ACTION=REBUILDDATABASE
/INSTANCENAME=instance_name
/SQLSYSADMINACCOUNTS= accounts
[/SAPWD=password]
[/SQLCOLLATION=collation_name]

Then start in single user mode:

sqlservr.exe -m -s

Then restore the latest backup of master and user databases, bringing the instance to the latest point possible.

More Info:

http://www.sqlservercentral.com/articles/Administration/73207/

http://www.networkworld.com/community/node/39292

If the transaction log is not being backed up, it will eventually grow to its maximum size or run out of disk space. The first thing to try when the transaction log is full is to perform a backup of it.

USE MASTER
GO

ALTER DATABASE myproj SET RECOVERY FULL;
GO

BACKUP DATABASE myproj TO DISK = ‘server01data$myprojData.bak’;
GO

BACKUP LOG myproj TO DISK = ‘server01data$myprojLog.bak’;
GO

ALTER DATABASE myproj SET RECOVERY SIMPLE;
GO

Transaction log
A record of modifications performed to a database. The amount of information logged in the transaction log depends on the recovery model for a database.

Transaction Log Backup
Backs up the active portion and truncates the inactive portion of the transaction log.

http://technet.microsoft.com/en-us/library/cc966495.aspx

Recovery Models
Simple
The simple recovery model does what it implies, it gives you a simple backup that can be used to replace your entire database in the event of a failure or if you have the need to restore your database to another server. With this recovery model you have the ability to do complete backups (an entire copy) or differential backups (any changes since the last complete backup). With this recovery model you are exposed to any failures since the last backup completed. Here are some reasons why you may choose this recovery model:

Your data is not critical and can easily be recreated
The database is only used for test or development
Data is static and does not change
Losing any or all transactions since the last backup is not a problem
Data is derived and can easily be recreated
Type of backups you can run:

Complete backups
Differential backups
File and/or Filegroup backups
Partial backups
Copy-Only backups
Under the simple recovery model, the transaction log is automatically truncated to remove any inactive virtual log files. Truncation usually occurs after each CHECKPOINT but can be delayed under some conditions, such as a long-running transaction, which can keep large portions of the transaction log in an “active” state.

http://msdn.microsoft.com/en-us/library/ms345414.aspx

Bulk_Logged
The bulk logged recovery sort of does what it implies. With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc… that are not fully logged in the transaction log and therefore do not take as much space in the transaction log. The advantage of using this recovery model is that your transaction logs will not get that large if you are doing bulk operations and you have the ability to do point in time recovery as long as your last transaction log backup does not include a bulk operation as mentioned above. If no bulk operations are run this recovery model works the same as the Full recovery model. One thing to note is that if you use this recovery model you also need to issue transaction log backups otherwise your database transaction log will continue to grow. Here are some reasons why you may choose this recovery model:

Data is critical, but you do not want to log large bulk operations
Bulk operations are done at different times versus normal processing.
You still want to be able to recover to a point in time
Type of backups you can run:

Complete backups
Differential backups
File and/or Filegroup backups
Partial backups
Copy-Only backups
Transaction log backups
Full
The full recovery model is the most complete recovery model and allows you to recover all of your data to any point in time as long as all backup files are useable. With this model all operations are fully logged which means that you can recover your database to any point. In addition, if the database is set to the full recovery model you need to also issue transaction log backups otherwise your database transaction log will continue to grow forever. Here are some reasons why you may choose this recovery model:

Data is critical and data can not be lost.
You always need the ability to do a point-in-time recovery.
You are using database mirroring
Type of backups you can run:

Complete backups
Differential backups
File and/or Filegroup backups
Partial backups
Copy-Only backups
Transaction log backups