Category: SQl server Administration

/*
Only use this script for SQL Server development servers!
Script must be executed as sysadmin

This script will execute the following actions on all databases
– set recovery model to [Simple]
– trucate log file
– shrink log file
*/

use [master]
go

— Declare container variabels for each column we select in the cursor
declare @databaseName nvarchar(128)

— Define the cursor name
declare databaseCursor cursor
— Define the dataset to loop
for
select [name] from sys.databases

— Start loop
open databaseCursor

— Get information from the first row
fetch next from databaseCursor into @databaseName

— Loop until there are no more rows
while @@fetch_status = 0
begin
print ‘Setting recovery model to Simple for database [‘ + @databaseName + ‘]’
exec(‘alter database [‘ + @databaseName + ‘] set recovery Simple’)

checkpoint

Print ‘checkpoint’

print ‘Shrinking logfile for database [‘ + @databaseName + ‘]’
exec(‘
use [‘ + @databaseName + ‘];’ +’

declare @logfileName nvarchar(128);
set @logfileName = (
select top 1 [name] from sys.database_files where [type] = 1
);
dbcc shrinkfile(@logfileName,1);
‘)

— Get information from next row
fetch next from databaseCursor into @databaseName
end

— End loop and clean up
close databaseCursor
deallocate databaseCursor
go

• Full Recovery Model
• Bulk-Logged Recovery Model
• Simple Recovery Model
• How To See What Recovery Model SQL Server 2005 is Using
• Database Recovery Models Comparison Chart

Full Recovery Model

The Full Recovery Model is the most resistant to data loss of all the recovery models. The Full Recovery Model makes full use of the transaction log – all database operations are written to the transaction log. This includes all DML statements, but also whenever BCP or bulk insert is used.

For heavy OLTP databases, there is overhead associated with logging all of the transactions, and the transaction log must be continually backed up to prevent it from getting too large.

Benefits:

• Most resistant to data loss
• Most flexible recovery options – including point in time recovery

Disadvantages:

• Can take up a lot of disk space
• Requires database administrator time and patience to be used properly

Bulk-Logged Recovery Model

The Bulk-Logged Recovery Model differs from the Full Recovery Model in that rows that are inserted during bulk operations aren’t logged – yet a full restore is still possible because the extents that have been changed are tracked.

The following transactions are minimally logged in a Bulk-Logged Recovery Model:

• SELECT INTO
• bcp and BULK INSERT
• CREATE INDEX
• Text and Image operations

Benefits:

• Transaction log stays small
• Easier from an administration standpoint (don’t have to worry about transaction logs)

Disadvantages:

• Not for production systems
• Point in time recovery not possible
• Least data resistant recovery model

Simple Recovery Model

The simple recovery model is the most open to data loss. The transaction log can’t be backed up and is automatically truncated at checkpoints. This potential loss of data is makes the simple recovery model a poor choice for production databases. This option can take up less disk space since the transaction log is constantly truncated.

Benefits:

• Transaction log stays small
• Easier from an administration standpoint (don’t have to worry about transaction logs)

Disadvantages:

• Not for production systems
• Point in time recovery not possible
• Least data resistant recovery model

How To See What Recovery Model SQL Server 2005 is Using

What recovery model is my SQL Server 2008 database using? It is easy enough to find out by following the steps below.

The recovery model can be determined in SQL Server 2005 by accessing the properties of the database. To do this, you can right click on the database in Object Explorer and select “properties”  After right clicking on properties, the SQL Server 2005 Database Properties window appears. Left click on options. The recovery model will be revealed to the right. In the screenshot below, you can see what is your recovery  model.

 

 

 

 

 

 

 

 

 

SQL Server 2005 Recovery Models Comparison Chart

 

 

 

 

 

 

 

 

introduction

This Article Explain how to make job to back up all database Day by day  on your PC, and put backup of Saturday in folder , Sunday in folder and so on , Then take backup from this backup file to anther place in anther PC.

in the first i use stored procedure to select all database like what i like then make job to EXEC this Stored then make job in windows to copy this backup from PC 1 to PC 2 lets see how can make this .

Step No 1

— Create Stored procedure {TakeDBsBackupDaily}

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create PROCEDURE [dbo].[TakeDBsBackupDaily]
@BackupPath nvarchar(500)
AS
BEGIN
declare  @d nvarchar(2)
,@m nvarchar(2)
,@y nvarchar(4)
,@CurDate nvarchar(8)
,@h nvarchar(2)
,@min nvarchar(2)
,@sec nvarchar(2)
,@CurTime nvarchar(8)
,@FName nvarchar(500)
,@DbName nvarchar(100)
,@BackupTypeName nvarchar(200)

Set @d = DATEPART(“day”,(GETDATE()))
Set @m = DATEPART(“month”,(GETDATE()))
Set @y = DATEPART(“year”,(GETDATE()))
set @CurDate = replicate(‘0’,2-len(@d))+@d —-+ replicate(‘0’,2-len(@m))+@m + replicate(‘0’,4-len(@y))+@y —– IN this statment i Sets the time will appear Beside database name like ‘Account22072011.bak’ but now i make the database of overwrite every month so i want the database show like this ‘Account07’
Set @h = DATEPART(“hour”,(GETDATE()))
Set @min = DATEPART(“minute”,(GETDATE()))
Set @sec = DATEPART(“second”,(GETDATE()))
set @CurTime = replicate(‘0’,2-len(@h))+@h + replicate(‘0’,2-len(@min))+@min + replicate(‘0’,2-len(@sec))+@sec

DECLARE RsTr CURSOR FOR
SELECT     name
FROM         sys.databases
WHERE     database_id >4 and state_desc = ‘ONLINE’

OPEN RsTr
FETCH NEXT FROM RsTr
INTO @DbName

WHILE @@FETCH_STATUS =0
BEGIN
—             +’_T_’+@CurTime
SET @FName = @BackupPath + ‘\’ + @DbName  + ‘_D_’+@curdate+’.bak’
print ‘Backing up Database : ‘ + @DbName + ‘ …’
set @BackupTypeName =  @DbName + ‘-Full Database Backup’
PRINT @FName
BACKUP DATABASE @DbName TO  DISK = @FName   WITH NOFORMAT, INIT,  NAME = @BackupTypeName, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

FETCH NEXT FROM RsTr
INTO @DbName
END

CLOSE RsTr
DEALLOCATE RsTr

END

———————————————————————————————-

STEP NO 2

In your pc make this folders

1- Folder Name (Auto_Backup) then in Auto_Backup make 7 folder (St-Su-Mo-Tu-We-Th-Fr)

STEP NO 3

—- Create Job (Backup Day By Day)To EXEC the SP (TakeDBsBackupDaily)

1- Open Sql Server Mangment Staduo

2- Open SQL Server Agent

3- Right Click on jobs , Select new job

4- Job Name [Backup Day By Day]

5- In Steps Select New , Step Name [Daily_Backup]

6- Put this Code in this step

Remarks : [Replace this path (‘D:\Backup\Auto\DayByDay\tu’) With your path]

USE [master]
GO
set dateformat dmy
declare @dayINDEX  as int
select @dayINDEX =(DATEPART(dw, getdate()) + @@DATEFIRST) % 7  — By this code i will get the index to the Current Day bigen from (0 to 6) [ST=0,Su=1,Mo=2,Tu=3,We=4,Th=5,Fr=6]

if @dayINDEX = 0
begin
exec   TakeDBsBackupDaily ‘D:\Backup\Auto\DayByDay\St’
end
if @dayINDEX = 1
begin
exec TakeDBsBackupDaily ‘D:\Backup\Auto\DayByDay\Su’
end
if @dayINDEX = 2
begin
exec  [TakeDBsBackupDaily] ‘D:\Backup\Auto\DayByDay\mo’
end
if @dayINDEX = 3
begin
exec [TakeDBsBackupDaily] ‘D:\Backup\Auto\DayByDay\tu’
end
if @dayINDEX = 4
begin
exec  [TakeDBsBackupDaily] ‘D:\Backup\Auto\DayByDay\we’
end
if @dayINDEX = 5
begin
exec [TakeDBsBackupDaily] ‘D:\Backup\Auto\DayByDay\th’
end
if @dayINDEX = 6
begin
exec [TakeDBsBackupDaily] ‘D:\Backup\Auto\DayByDay\fr’
END

——————————————————————————————————–

STEP NO 4

—Copy The backup file from the server to pc 2

In the PC2

1- Create new folder in make this folder is share to every one and give it all permision

2- the user in your pc must be administrator and must be has a paasword

In the Server

1-In folder Auto Backup make new text Document ,The name of this text [MoveDB_Backup] but make this text not .TXT make it .bat (icone must be cahnge when you make this)

2- then make Edit in this File bat and write

3- xcopy /y /s F:\backupdaybyday\*.*  \\192.168.0.9\daybydayback

4- Remarks [Replace this path (F:\backupdaybyday) by your backup folder path like E:\Auto backup not like E:\Auto Backup\St if you make this you will copy the backup in this folder only then Replace this path (\\192.168.0.9\daybydayback) by the Folder Share in PC 2] this path [\\192.168.0.9\daybydayback] must be write like this not like this ‘E:\daybydayback’ to make the server access the pc2

5- so you can open the RUN and write the IP of PC 2 and try to open the share folder then thake tha path to the folder

6- in your windows in the server make job to EXCE this file .bat Every day

 ——————————————————————————————————-

At the end of my words I hope I estimated I tell you

My best Regards

Mostafa Elmasry

This stored Procedure to search in all tables

/****** Object:  StoredProcedure [dbo].[SearchAllTables]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create 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

Execute the following Microsoft SQL
Server T-SQL script to create a table-valued function and a stored procedure to
list all drives with total disk space and free disk space. Note that the
table-valued function “join”-ed with a CROSS APPLY in the sproc to obtain the
final results.

*

— Turn on OLE automation if not on

exec sp_configure ‘show advanced options’, 1

go

RECONFIGURE

GO

exec sp_configure ‘Ole Automation Procedures’, 1

GO

RECONFIGURE

GO

*/

CREATE FUNCTION fnTotalDriveSpace

(@DriveLetter CHAR(1))

RETURNS @Total TABLE (MaxSpaceGB money)

BEGIN

DECLARE  @return INT, @fso INT, @GetDrive VARCHAR(16)

DECLARE @drv INT, @DriveSizeinBytes VARCHAR(32)

SET @GetDrive = ‘GetDrive(“‘ + @DriveLetter + ‘”)’

EXEC @return = sp_OACreate ‘Scripting.FileSystemObject’, @fso OUTPUT

SET @DriveSizeinBytes = NULL

IF @return = 0

EXEC @return = sp_OAMethod @fso, @GetDrive, @drv OUTPUT

IF @return = 0

EXEC @return = sp_OAGetProperty @drv,’TotalSize’, @DriveSizeinBytes OUTPUT

EXEC sp_OADestroy @drv

EXEC sp_OADestroy @fso

INSERT @Total values (

(((convert(bigint,@DriveSizeinBytes)/  1024)/ 1024)/1024) )

RETURN

END

GO

— select * from dbo.fnTotalDriveSpace(‘C’)

CREATE PROC sprocDriveSpaceInfo

AS

BEGIN

DECLARE @Drives TABLE ( DriveLetter char(1), FreeGB money)

INSERT @Drives (DriveLetter, FreeGB)

EXEC xp_fixeddrives

UPDATE @Drives SET FreeGB = Floor(FreeGB/1024)

SELECT

DriveLetter,

FreeGB=convert(int,FreeGB),

MaxSpaceGB=convert(int,MaxSpaceGB)

FROM @Drives d

CROSS APPLY dbo.fnTotalDriveSpace (d.DriveLetter)

ORDER BY DriveLetter

END

GO

EXEC sprocDriveSpaceInfo

GO