Category Archives: Script Generator

Side categories to list scripts that make other scripts using dynamic SQL

Shrink All Transaction Logs

InsideDatabases, Script Generator

The following script loops through all the Tlogs and attempts to shrink them. Remember that a Tlog can be shrunk only if there is empty space at the end. In order to tell where the empty space is, run: 


use [database]
DBCC LOGINFO

If the Status column is 0, then it's empty. If you do not have empty space at the end, it will not shrink, so you need to run Tlog backups. Sometimes you need to run Tlog backups several times for the portion that's empty to move to the end of the file. More on that here:

http://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

Now, for the script: 

/*
Get a list of Databases and its Tlogs, and shrink them
Do the following.
1- Run Tlog Backups
2- Run this script

Author: Miguel Quintana

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

Does not work:
MSSQL2000

*/




DECLARE @numRec INT
DECLARE @counter INT
DECLARE @DBName SYSNAME
DECLARE @Tlog SYSNAME
DECLARE @CMD NVARCHAR(MAX)

DECLARE @table TABLE
(RowID INT IDENTITY(1, 1), dbname SYSNAME, tlogname SYSNAME)

-- create a table with the DBname and its Tlog file
-- skip offline and system DBs
INSERT INTO @table 
SELECT db_name(database_id), name FROM sys.master_files 
WHERE type_desc = 'LOG' and state_desc ='ONLINE' and database_id > 4

-- Set and initialize counters
SELECT @numRec=count(*), @counter=1 FROM @table

WHILE  @counter <= @NumRec
BEGIN 

SELECT @DBName = dbname, @Tlog = tlogname FROM @table WHERE RowID = @counter

SET @CMD='USE ' + quotename(@DBName, N'[') + 
                        N'
CHECKPOINT
DBCC SHRINKFILE (['+@Tlog+'], 0, TRUNCATEONLY)'

print @CMD
-- EXECUTE (@CMD)

set @counter=@counter+1
END

 

Ok.. well… what happen if you are forced to shrink the Tlog and you don't have space for backups? Now what?

There is a way where you can take a backup "into the ether"… tricking the database into dumping all pages and forcing to mark the Tlog as empty pages.

Doing this will not cause data loss, however it will invalidate the backup chain, and a full backup should be taken as soon as possible.

 

  


BACKUP LOG [AdventureWorks2012] TO  DISK = N'NUL'

 

Enforce Password Policies on SQL Logins

Changing-Defaults, Script Generator

Make sure all SQL Logins have the “enforced password” setting check 

/*
DESCRIPTION:
The following code Enables the CHECK POLICY ON on all logins who needs it, No need to run the output, it just does it.
 
CONFIGURATION
None

Author: Miguel Quintana

Compatibility list:
MSSQL2005
MSSQL2008

DOES NOT WORK
MSSQL2000
*/

DECLARE @LoginName sysname
DECLARE @SQL NVARCHAR(1000)

DECLARE DBLOGINS CURSOR FOR
    SELECT name  FROM master.sys.sql_logins
    WHERE is_policy_checked = 0

OPEN DBLOGINS

FETCH NEXT FROM DBLOGINS INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'ALTER LOGIN [' + @LoginName + '] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;'
-- print @SQL
    EXEC sp_executesql @SQL
    PRINT 'Fixing Login for ['+@LoginName+']'
    FETCH NEXT FROM DBLOGINS INTO @LoginName
END

CLOSE DBLOGINS
DEALLOCATE DBLOGINS
PRINT 'Done'

Restore Script Generator one DB

restore, Script Generator

Planning restore operations ahead of time is important to determine the space needed and in automation task Here, we provide the actual BAK file and its path, we read the backup file using the RESTORE HEADERONLY and FILELISTONLY operations.

Make sure your account has privileges to read the backup if its stored on a network location.

 /*
DESCRIPTION Script Generator
It creates the RESTORE scripts for ONE backup (bak) file
and lists the space needed.
Configuration At the top:
Author: Miguel Quintana
Compatibility list: MSSQL2016 MSSQL2012 MSSQL2008 MSSQL2005
not tested on 2014
Does not work:
MSSQL2005 for SQL2005 to work, the two tables for restore fileheaderonly and filelistonly need to be modIFied.
*/
SET NOCOUNT ON
--************** CONFIGURATION **************
DECLARE @path VARCHAR(1000)
DECLARE @DataPath NVARCHAR(3000)
DECLARE @TlogPath NVARCHAR(3000)
DECLARE @RELOCATE INT
DECLARE @OVERRIDE INT
-- Enter ful path to the BACK file
SET @path = 'k:\MSSQL11.SQL2012\MSSQL\Backup\AMTENTSQL1202T$SQL2012\Clratsdb7\FULL\AMTENTSQL1202T$SQL2012_Clratsdb7_FULL_20150501_010000.bak'
-- Overrider: Say 1 IF the database already exist and needs to be over written
-- Overrider: Say 0 IF the database is new to this server
SET @OVERRIDE = 1
--RELOCATE: IF you want to relocate the data files, say 1 and fill in the following:
-- IF not, say 0 and skip.
SET @RELOCATE = 1
-- Data files must end wit ha backslash
SET @DataPath = 'E:\MSSQL\DATA\'
-- Tlog Files must end wit ha backslash
SET @TlogPath = 'F:\TlogFolder\'
--************** END CONFIGURATION **************
DECLARE @databasename NVARCHAR(100)
DECLARE @physicalName NVARCHAR(100)
DECLARE @filename NVARCHAR(100)
DECLARE @filepath NVARCHAR (1000)
DECLARE @temp1 NVARCHAR (100)
DECLARE @temp2 NVARCHAR (100)
DECLARE @rownum INT
DECLARE @move NVARCHAR (1000)
DECLARE @bksizeTotal BIGINT
DECLARE @DATAsizeTotal BIGINT
DECLARE @TLOGsizeTotal BIGINT
-- initialize size counters
SET @bksizeTotal = 0
SET @DATAsizeTotal = 0
SET @TLOGsizeTotal = 0
IF OBJECT_ID('tempdb..#filelist') IS NOT NULL
DROP TABLE #filelist
IF OBJECT_ID('tempdb..#header') IS NOT NULL
DROP TABLE #header
CREATE TABLE #filelist
(
LogicalName NVARCHAR(1024),PhysicalName NVARCHAR(1024),[Type] char(1),FileGroupName NVARCHAR(128),Size NUMERIC(20,0)
,MaxSize NUMERIC(20,0),Fileid BIGINT, CreateLSN numeric(25,0),
DropLSN numeric(25, 0),UniqueID UNIQUEIDENTIFIER,ReadOnlyLSN NUMERIC(25,0),
ReadWriteLSN NUMERIC(25,0),BackupSizeInBytes BIGINT,SourceBlocSize INT,FileGroupId INT,
LogGroupGUID UNIQUEIDENTIFIER,DIFferentialBaseLSN NUMERIC(25,0),DIFferentialBaseGUID UNIQUEIDENTIFIER,
IsReadOnly BIT,IsPresent BIT
)
CREATE TABLE #header
(
BackupName NVARCHAR(512),BackupDescription NVARCHAR(255),[BackupType] SMALLINT,ExpirationDate DATETIME,Compressed BIT,
Position SMALLINT,DeviceType TINYINT,UserName NVARCHAR (128)
,ServerName NVARCHAR (128),DatabaseName NVARCHAR(512),DatabaseVersion INT
,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN NUMERIC(25,0),CheckPointLSN NUMERIC (25,0),
DBBKLSN NUMERIC (25,0),BackupStartDate DATETIME,BackupFinishDate DATETIME
,SortOrder SMALLINT,[CodePage] INT,UnicodeLocal INT,UnicodeComp INT,CompLevl INT,softwVendId INT,SoftVersionMajor INT,
SoftVersionMinor INT,SoftVersionBuild INT,MachineName NVARCHAR(128)
,flag INT,BindingId UNIQUEIDENTIFIER,RecoveryForkID UNIQUEIDENTIFIER,collation Nvarchar(128)
,FamilyGUID UNIQUEIDENTIFIER,hasbulkloggedata BIT,issnapshot BIT,isreadonly BIT
,issingleuser bit,hadbackupchecksums bit, isdamaged bit, beginslogchain bit, HasIncomplMetaData bit
, isforceoffline INT,iscopyonly INT,FirstRecoveryForkID UNIQUEIDENTIFIER, forkpointlsn NUMERIC (25,0), RecoveryModel NVARCHAR(60),
dIFfBaseLSN NUMERIC (25,0), dIFfbaseGUID UNIQUEIDENTIFIER
,backupTypeDesc NVARCHAR (50),BKSETGUID UNIQUEIDENTIFIER
)


DECLARE @Version numeric(18,10)
SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))

-- If this is SQL2008 or  higher, add more columns to support compression
IF @VERSION >= 10
BEGIN
ALTER TABLE #filelist
ADD TDEThumbPRINT bit
 
ALTER TABLE #header
ADD CompressedBackupSize BIGINT 
END
-- If this is SQL2012 or  higher, add more columns to support contained database
IF @Version >= 11
BEGIN
ALTER TABLE #Header
ADD containmnet bit
END

-- If this is SQL2016 or  higher, add more columns to support contained database
IF @Version >= 13
BEGIN
ALTER TABLE #Header
ADD KeyAlgorithm nvarchar(32)
ALTER TABLE #Header
ADD EncryptorThumbprint varbinary(20)
ALTER TABLE #Header
ADD EncryptorType nvarchar(32)

ALTER TABLE #filelist
add SnapshotURL nvarchar(360)
END
 

 
-- Insert the data in our tables
INSERT #header
EXEC ('restore headeronly FROM disk = ''' + @path + '''')
INSERT #filelist
EXEC ('restore filelistonly FROM disk = ''' + @path + '''')
SELECT @databasename=DatabaseName FROM #header
SET @move = '
-- *************************************************
-- Restore Script For Database ' + @databasename + '
-- *************************************************
'
IF @OVERRIDE=1 -- if override, place database on single_user mode
SET @move=@move+'ALTER DATABASE ['+@databasename+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
 
SET @move=@move+
'
RESTORE DATABASE ['+@databasename+']
FROM DISK ='''+@path+'''
WITH FILE = 1, -- assumes only one database backup per media family
'
-- First WHILE for data files.
DECLARE @DataFileTable table
(rowcnt INT identity(1,1), LogicalName varchar(4000), physpath varchar(4000))
 
INSERT INTO @DataFileTable
SELECT LogicalName,
reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) FROM #filelist         --'
where [type]= 'D'
SET @rownum =1
WHILE @rownum < = (select count(*) from @DataFileTable)
BEGIN
SELECT @temp1=LogicalName, @temp2=physpath FROM @DataFileTable WHERE rowcnt = @rownum
-- start adding size info
SELECT @DATAsizeTotal=@DATAsizeTotal+size FROM #filelist where LogicalName =@temp1
-- SET the new path
SET @temp2 = @DataPath+@temp2
-- IF Relocate is 0, gets the current physical path and assign it to @temp2
IF @RELOCATE=0
SELECT @temp2 = PhysicalName FROM #filelist where LogicalName = @temp1
-- concatenante the move portion of the restore script.
SET @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''',
'
 
set @rownum = @rownum+1
END
-- Second cursor for Tlog files.
DECLARE @TlogFileTable table
(rowcnt INT identity(1,1), LogicalName varchar(4000), physpath varchar(4000))
 
INSERT INTO @TlogFileTable
SELECT LogicalName,
reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) FROM #filelist
where [type]= 'L'
SET @rownum =1
WHILE @rownum <= (select count(*) from @TlogFileTable)
BEGIN
SELECT @temp1=LogicalName, @temp2=physpath FROM @TlogFileTable WHERE rowcnt = @rownum
-- start adding size info
SELECT @TLOGsizeTotal=@TLOGsizeTotal+size FROM #filelist where LogicalName =@temp1
-- IF Relocate is 0, gets the current physical path and assign it to @temp2
IF @RELOCATE=0
SELECT @temp2 = PhysicalName FROM #filelist
where LogicalName = @temp1
SET @temp2 = @TlogPath+@temp2
SET @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''',
'
 
SET @rownum = @rownum+1
END
 
 
SET @move = @move +'
NOUNLOAD, REPLACE, STATS = 10
GO
'
IF @OVERRIDE=1
SET @move=@move+'ALTER DATABASE ['+@databasename+'] SET MULTI_USER
GO
SET @BKSizeTotal = @DATASizeTotal + @TLOGSizeTotal
'
PRINT @move
PRINT ' /* ***********************--'
PRINT 'Total Size Needed in all disks '+ cast (@BkSizeTotal/1024/1024/1024 as varchar(10))+ ' GB'
PRINT ' --'
PRINT 'Total Size Needed for DATA files '+ cast (@DATASizeTotal/1024/1024/1024 as varchar(10))+ ' GB'
PRINT ' --'
PRINT 'Total Size Needed for TLOG files '+ cast (@TLOGSizeTotal/1024/1024/1024 as varchar(10))+ ' GB'
PRINT ' --'
PRINT ' *********************** */'
DROP TABLE #filelist
DROP TABLE #headervers

Restore Generator for all BAK files in one folder

restore, Script Generator, ,

Planning restore operations ahead of time is important to determine the space needed and in automation task Here, we provide the actual path to the folder where the BAK files are kept, and we generate a resotre script using the RESTORE HEADERONLY and FILELISTONLY operations for all databases on that folder. 

/*
DESCRIPTION Script Generator
It creates the RESTORE scripts for all backup (bak) files on a directory
and lists the space needed AT THE BOTTOM of output.
 
Configuration At the top:
 
Compatibility list:
MSSQL2008
MSSQL2012
 
 
Does not work:
MSSQL2005 for SQL2005 to work, the two tables for restore fileheaderonly and filelistonly need to be modified.
 
*/
set nocount on
 
DECLARE @pathname VARCHAR(1024)
DECLARE @DataPath NVARCHAR(3000)
DECLARE @TlogPath NVARCHAR(3000)
DECLARE @RELOCATE INT
DECLARE @OVERRIDE INT
 
 
--************** CONFIGURATION **************
 
 
-- Set the pathname for the location of the backup files
-- must end wit ha backslash \
 
set @pathname = '"t:\bk"'

-- Overrider: Say 1 if the database already exist and needs to be over written
-- Overrider: Say 0 if the database is new to this server
 
set @OVERRIDE = 1
 
--RELOCATE: If you want to relocate the data files, say 1 and fill in the following:
-- if not, say 0 and skip.
 
set @RELOCATE = 1
-- Data files must end wit ha backslash
set @DataPath = 'E:\MSSQL\DATA\'
-- Tlog Files must end wit ha backslash
set @TlogPath = 'F:\TlogFolder\'
 
--************** END CONFIGURATION **************
 
 
/*
**************************************************
**************************************************
Declare Variables DO NOT MODIFY BELOW
**************************************************
**************************************************
*/
DECLARE @databasename NVARCHAR(1000)
DECLARE @physicalName NVARCHAR(1000)
DECLARE @filename NVARCHAR(1000)
DECLARE @filepath NVARCHAR (1000)
DECLARE @temp1 NVARCHAR (1000)
DECLARE @temp2 NVARCHAR (1000)
DECLARE @temp3 NVARCHAR (1000)
DECLARE @move NVARCHAR (4000)
DECLARE @CMD VARCHAR(512)
DECLARE @bksizeTotal BIGINT
DECLARE @DATAsizeTotal BIGINT
DECLARE @TLOGsizeTotal BIGINT
 
 
-- initialize size counters
set @bksizeTotal = 0
set @DATAsizeTotal = 0
set @TLOGsizeTotal = 0


-- Create Tables to store data
  IF OBJECT_ID('tempdb..#filelist') IS NOT NULL
DROP TABLE #filelist
IF OBJECT_ID('tempdb..#header') IS NOT NULL
DROP TABLE #header

CREATE TABLE #filelist
(
LogicalName NVARCHAR(1024),PhysicalName NVARCHAR(1024),[Type] char(1),FileGroupName NVARCHAR(128),Size NUMERIC(20,0)
,MaxSize NUMERIC(20,0),Fileid BIGINT, CreateLSN numeric(25,0),
DropLSN numeric(25, 0),UniqueID UNIQUEIDENTIFIER,ReadOnlyLSN NUMERIC(25,0),
ReadWriteLSN NUMERIC(25,0),BackupSizeInBytes BIGINT,SourceBlocSize INT,FileGroupId INT,
LogGroupGUID UNIQUEIDENTIFIER,DIFferentialBaseLSN NUMERIC(25,0),DIFferentialBaseGUID UNIQUEIDENTIFIER,
IsReadOnly BIT,IsPresent BIT
)
CREATE TABLE #header
(
BackupName NVARCHAR(512),BackupDescription NVARCHAR(255),[BackupType] SMALLINT,ExpirationDate DATETIME,Compressed BIT,
Position SMALLINT,DeviceType TINYINT,UserName NVARCHAR (128)
,ServerName NVARCHAR (128),DatabaseName NVARCHAR(512),DatabaseVersion INT
,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN NUMERIC(25,0),CheckPointLSN NUMERIC (25,0),
DBBKLSN NUMERIC (25,0),BackupStartDate DATETIME,BackupFinishDate DATETIME
,SortOrder SMALLINT,[CodePage] INT,UnicodeLocal INT,UnicodeComp INT,CompLevl INT,softwVendId INT,SoftVersionMajor INT,
SoftVersionMinor INT,SoftVersionBuild INT,MachineName NVARCHAR(128)
,flag INT,BindingId UNIQUEIDENTIFIER,RecoveryForkID UNIQUEIDENTIFIER,collation Nvarchar(128)
,FamilyGUID UNIQUEIDENTIFIER,hasbulkloggedata BIT,issnapshot BIT,isreadonly BIT
,issingleuser bit,hadbackupchecksums bit, isdamaged bit, beginslogchain bit, HasIncomplMetaData bit
, isforceoffline INT,iscopyonly INT,FirstRecoveryForkID UNIQUEIDENTIFIER, forkpointlsn NUMERIC (25,0), RecoveryModel NVARCHAR(60),
dIFfBaseLSN NUMERIC (25,0), dIFfbaseGUID UNIQUEIDENTIFIER
,backupTypeDesc NVARCHAR (50),BKSETGUID UNIQUEIDENTIFIER
)

 
DECLARE @Version numeric(18,10)
SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))

-- If this is SQL2008 or  higher, add more columns to support compression
IF @VERSION >= 10
BEGIN
ALTER TABLE #filelist
ADD TDEThumbPRINT bit
 
ALTER TABLE #header
ADD CompressedBackupSize BIGINT 
END
-- If this is SQL2012 or  higher, add more columns to support contained database
IF @Version >= 11
BEGIN
ALTER TABLE #Header
ADD containmnet bit
END

IF OBJECT_ID('tempdb..#CommandShell') IS NOT NULL
DROP TABLE #CommandShell


CREATE TABLE #CommandShell
(	Line VARCHAR(1024)	)
-- List all files in a directory - T-SQL parse string for date and filename
-- Microsoft SQL Server command shell statement - xp_cmdshell
 
-- these variables keep track if we changed these options, we'll change them back before finishing
 

DECLARE @chkOLE as sql_variant
DECLARE @adv_opt as sql_variant
DECLARE @op_ch1 as INT
DECLARE @op_ch2 as INT
print '/* 
Ignore this commented block'
set @op_ch1=0
set @op_ch2=0
select @adv_opt = value from sys.configurations where name = 'show advanced options'
if @adv_opt = 0
begin
EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE;
set @op_ch1 = 1
end
select @chkOLE = value from sys.configurations where name = 'xp_cmdshell'
if @chkOLE = 0
begin
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE;
set @op_ch2 = 1
end


-- Adds CMD compatible quotation marks if there is none
IF ((SELECT CHARINDEX('"',@pathname))=0)
SET @pathname = '"'+@pathname+'"'

-- Set the DIRECTORY to search for backups
SET @CMD = 'DIR ' + @PathName + ' *.bak /A /B'
 
-- Populate table with the files in directory
 
INSERT INTO #CommandShell
EXEC MASTER..xp_cmdshell @CMD
-- Delete lines not containing a filename
DELETE FROM #CommandShell WHERE Line is null
 
-- checking if we change settings, and reverting them
 
if @op_ch2 = 1
begin
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE;
end
if @op_ch1 = 1
begin
EXEC sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE;
end
print '
Ignore this commented block*/ 
'

IF EXISTS (select TOP 1 (Line) from #CommandShell where Line = 'Access is denied.')
BEGIN
Print '
Either the SQL Server does not have access to the path, or it does not exist'
print @pathname
GOTO EOF
END
--- end CMDshell

-- Removing double quotations from pathname
set @pathname = REPLACE(@pathname,'"','')

-- Makes sure path ends with a slash, and adds one if needed
IF ((SELECT CHARINDEX('\',reverse(@pathname)))>1)
SET @pathname = @pathname+'\'

-- Removing Last line of table
delete from #CommandShell where Line = 'File Not Found' 

-- First Cursor, grabs a file, and extracts the database name
-- **********************************************
-- **********************************************
-- ********************************************** 
DECLARE LIST CURSOR FOR
SELECT @pathname+Line FROM #CommandShell
 
 
OPEN LIST
FETCH NEXT from LIST INTo @temp3
WHILE (@@fetch_status = 0)
BEGIN
 
-- populate the tables with the BAK file info
insert #header
exec ('restore headeronly from disk = ''' + @temp3 + '''')
 
insert #filelist
exec ('restore filelistonly from disk = ''' + @temp3 + '''')
 
-- Grabs DATABASE name for the BAK file
 
select @databasename=DatabaseName from #header
 
-- start adding size info
select @bksizeTotal=@bksizeTotal+backupsize from #header
 
set @move = '
-- *************************************************
-- Restore Script For Database ' + @databasename + '
-- *************************************************
'
if @OVERRIDE=1
set @move=@move+'ALTER DATABASE ['+@databasename+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
set @move=@move+
'
RESTORE DATABASE ['+@databasename+']
FROM DISK ='''+@temp3+'''
WITH FILE = 1,
'
 
-- Second Cursor for data files, nested.
 
DECLARE cur CURSOR FOR
SELECT LogicalName,
reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) from #filelist
where [type]= 'D'
 
OPEN cur
FETCH NEXT from cur INTO @temp1 ,@temp2
WHILE (@@fetch_status = 0)
BEGIN
 
-- start adding size info
SELECT @DATAsizeTotal=@DATAsizeTotal+size FROM #filelist WHERE LogicalName =@temp1
 
-- Set the new path
SET @temp2 = @DataPath+@temp2
 
-- If Relocate is 0, gets the current physical path and assign it to @temp2
IF @RELOCATE=0
SELECT @temp2 = PhysicalName FROM #filelist
WHERE LogicalName = @temp1
 
-- concatenante the move portion of the restore script.
SET @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''',
'
FETCH NEXT from cur INTo @temp1 ,@temp2
END
CLOSE cur
DEALLOCATE cur
 
-- Third cursor for Tlog files. Nested.
DECLARE cur cursor for
SELECT LogicalName, reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) FROM #filelist
WHERE [type]= 'L'
 
OPEN cur
FETCH NEXT FROM cur INTo @temp1 ,@temp2
WHILE (@@fetch_status = 0)
BEGIN
 
-- start adding size info
select @TLOGsizeTotal=@TLOGsizeTotal+size from #filelist where LogicalName =@temp1
 
-- If Relocate is 0, gets the current physical path and assign it to @temp2
IF @RELOCATE=0
SELECT @temp2 = PhysicalName from #filelist
where LogicalName = @temp1
 
 
set @temp2 = @TlogPath+@temp2
set @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''',
'
FETCH NEXT from cur INTo @temp1 ,@temp2
END
close cur
deallocate cur
print @move +'
NOUNLOAD, REPLACE, STATS = 10
GO'
if @OVERRIDE=1 set @move=@move+'ALTER DATABASE ['+@databasename+'] SET MULTI_USER
GO'
 
 
truncate table #filelist
truncate table #header
FETCH NEXT FROM LIST INTO @temp3
END
CLOSE LIST
DEALLOCATE LIST


-- **********************************************
-- **********************************************
-- **********************************************
 
print ' /* ***********************--'
 
Print 'Total Size Needed in all disks '+ cast (@BkSizeTotal/8/1024/1024/1024 as VARCHAR(10))+ ' GB'
print ' --'
Print 'Total Size Needed for DATA files '+ cast (@DATASizeTotal/8/1024/1024/1024 as VARCHAR(10))+ ' GB'
print ' --'
Print 'Total Size Needed for TLOG files '+ cast (@TLOGSizeTotal/8/1024/1024/1024 as VARCHAR(10))+ ' GB'
print ' --'
print ' *********************** */'
 
 


--Clean up
drop table #CommandShell
drop table #filelist
drop table #header


-- */

EOF:

Check When The Last Restore Was Performed

restore, Script Generator

This script returns information about the last time a database has been restored on this server.

You can either search all databases by leaving the parameter NULL  or search for a single specific database.

The other configurable item is the time frame to look for. It defaults to 30 days, and it should be entered as a negative number (i.e., -7)

This script queries the MSDB database, so it'll return what's on the history only

Source: MSSQL SQL Tips 

/*
DESCRIPTION:
Queries the RESTORE operation that have taken place in the last 30 days
CONFIGURATION
 
Compatibility list: MSSQL2012 MSSQL2008 MSSQL2005
DOES NOT WORK
UNKNOWN: MSSQL2000
*/
  
DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
SET @days = -30 --previous number of days, script will default to 30
SELECT
rsh.destination_database_name AS [Database],
rsh.user_name AS [Restored By],
CASE WHEN rsh.restore_type = 'D' THEN 'Database'
WHEN rsh.restore_type = 'F' THEN 'File'
WHEN rsh.restore_type = 'G' THEN 'Filegroup'
WHEN rsh.restore_type = 'I' THEN 'Differential'
WHEN rsh.restore_type = 'L' THEN 'Log'
WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
WHEN rsh.restore_type = 'R' THEN 'Revert'
ELSE rsh.restore_type
END AS [Restore Type],
rsh.restore_date AS [Restore Started],
bmf.physical_device_name AS [Restored From],
rf.destination_phys_name AS [Restored To],
bs.server_name AS [CameFromServer]
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name like ISNULL(@dbname, '%') --if no dbname, then return all
and rf.file_number = 1                     --- Only lists MDF
ORDER BY rsh.restore_history_id DESC
GO

Restore Full Backup and Tlog Generator

restore, Script Generator

This script is a classic dynamic TSQL.

It uses the MSDB information to build a Restore script with all the backups available on MSDB. 

/*
DESCRIPTION:
Looks inside MSDB history information for a particular Database.
Gets the newest FULL backup and Tlogs
and generates a script to restore the Full BK + Diff+ Tlogs
Assumes the backup files are still in original location.
 
Configuration:
@dbname = the DB that we want to restore
@newdbname = The name of the new DB. If no name change, make this values the same, or leave null.
 
Compatibility list: MSSQL2012 MSSQL2005 MSSQL2008 MSSQL2000
 
*/

SET NOCOUNT ON
DECLARE @dbname VARCHAR(500)
DECLARE @newdbname VARCHAR(500)
SET @dbname = 'SALES_ODS'
SET @newdbname = NULL			-- if target DB name will be the same, leave NULL


/*
do not modify below
*/

set @newdbname = coalesce(@newdbname,@dbname)


-- gets the latest FULL backup and all Tlog after that.

DECLARE @bkfiles table
( id int identity(1,1),physical_device_name nVARCHAR (512), [type] char)

INSERT @bkfiles (physical_device_name, [type])
	SELECT m.physical_device_name, s.[type]
	FROM msdb.dbo.backupSET s
	inner join msdb.dbo.backupmediafamily m ON s.media_SET_id = m.media_SET_id
	WHERE
	 s.database_name like @dbname
	 and s.[backup_finish_date] >=
		(SELECT MAX(msdb.dbo.backupSET.backup_finish_date)
		FROM msdb.dbo.backupmediafamily
		INNER JOIN msdb.dbo.backupSET ON msdb.dbo.backupmediafamily.media_SET_id = msdb.dbo.backupSET.media_SET_id
		WHERE msdb..backupSET.type = 'D' and msdb.dbo.backupSET.database_name = @dbname and is_copy_only = '0'
		GROUP BY msdb.dbo.backupSET.database_name
		)
and is_copy_only = '0' -- does not include copy_only backups
 
-- Remove Tlogs and Diff before very last Diff from the table
DELETE FROM @bkfiles
	WHERE ID < (SELECT TOP 1 (ID) FROM @bkfiles
				WHERE TYPE = 'I' ORDER BY ID DESC) 
		and type <> 'D'
-- creates table to hold our Tsql scripts

DECLARE @cmd table
(id int identity(1,1),script NVARCHAR (4000))

INSERT @cmd (script)
	Select 'RESTORE DATABASE ['+@newdbname+'] FROM DISK =N'''+physical_device_name+''''
	from @bkfiles
	where [type]='D'

INSERT @cmd (script)
SELECT 'WITH FILE = 1,'
 
-- Prepare the MOVE Statements

select 'MOVE N'''+name+''''+' to N'''+physical_name+''','  
FROM sys.master_files where database_id=db_id(@dbname) and type_desc = 'ROWS'
 
insert @cmd (script)
select ' NORECOVERY, NOUNLOAD, REPLACE, STATS = 10'

-- Restores the Differential
insert @cmd (script)
select 'RESTORE DATABASE ['+@newdbname+']
FROM DISK = N'''+ physical_device_name +'''
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
from @BKFiles
where [TYPE] = 'I'
 
 
-- Build the Tlog chain
insert @cmd (script)
select 'RESTORE LOG ['+@newdbname+']
FROM DISK = N'''+ physical_device_name +'''
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
from @BKFiles
where [TYPE] = 'L'

-- Set DB with recovery mode
insert @cmd (script)
select 'RESTORE DATABASE ['+@newdbname+'] WITH RECOVERY'
-- Display the info
select script from @cmd order by id

Rebuild – Reorganize All Indexes

index-fragmentation, Script Generator

This script gets the fragmentation level of your indexes with over 1,000 pages (that way, it skips small tables)

If the fragmentation level is less than 40%, it will reorganize the index.

If the fragmentation level is more than 40%, it will rebuild the index.

The script also presents an option to do a "dry-run", to only print the statements. 


/*
DESCRIPTION:
Reindex or Reorg all indexes based on fragmentation threshold
 
CONFIGURATION

Top: enter name of database



Compatibility list:
MSSQL2005
MSSQL2008

Does not work
MSSQL2000
 */
 
USE <database name>
GO

Declare @TestRun int;

Set @TestRun = 1   --- Set @TestRun = 1 to execute the rebuild  Set @TestRun = 0 to only print the statements.


 
SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(130);  
DECLARE @objectname nvarchar(130);  
DECLARE @indexname nvarchar(130);  
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);  
DECLARE @dbid smallint;  
 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function  
-- and convert object and index IDs to names.  
 
SET @dbid = DB_ID();  
 
SELECT  
    [object_id] AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag, page_count  
 
INTO #work_to_do  
 
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')  
 
WHERE avg_fragmentation_in_percent > 10.0  -- Allow limited fragmentation  
AND index_id > 0 -- Ignore heaps  
AND page_count > 1000; -- Ignore small tables  
 
-- Declare the cursor for the list of partitions to be processed.  
 
DECLARE partitions CURSOR FOR

SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;  
 
-- Open the cursor.  
 
OPEN partitions;  
 
-- Loop through the partitions.  
 
WHILE (1=1)  
BEGIN  
FETCH NEXT  
FROM partitions  
INTO @objectid, @indexid, @partitionnum, @frag;  
 
IF @@FETCH_STATUS < 0 BREAK;  
 
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
FROM sys.objects AS o  
JOIN sys.schemas as s ON s.schema_id = o.schema_id  
WHERE o.object_id = @objectid;  
 
SELECT @indexname = QUOTENAME(name)  
FROM sys.indexes  
WHERE object_id = @objectid AND index_id = @indexid;  
 
SELECT @partitioncount = count (*)  
FROM sys.partitions  
WHERE object_id = @objectid AND index_id = @indexid;  
 
-- 40 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
 
IF @frag < 40.0  
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
 
IF @frag >= 40.0  
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ONLINE=ON)';  
 
---IF @partitioncount > 1  
---SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  

IF @TestRun = 1
EXEC (@command);  

PRINT N'Executed: ' + @command;  
END  
 
-- Close and deallocate the cursor.  
 
CLOSE partitions;  
DEALLOCATE partitions;  
 
-- Drop the temporary table.  
 
DROP TABLE #work_to_do;  
GO

 

Generate script to backup all databases

Backups, Script Generator

This is a SCRIPT generator. It does not execute code.

The output can be used as a script to backup databases, enter into a SQL job to run backups, etc.

  

 /*
DESCRIPTION:
The following code generates a SQL script to do a FULL backup ALL databases to the specified location. It also creates a folder for each database on that location
CONFIGURATION
Just change the value for @LOCATION. example: SET @LOCATION = 'c:\temp'
If @EachFolder = 1 it makes a subfolder for each databases
If @EachFolder = 0 it does not. all BAK in same folder
Compatibility list: MSSQL2012 MSSQL2008 MSSQL2005
DOES NOT WORK MSSQL2000
*/
DECLARE @LOCATION VARCHAR(2000)
SET @LOCATION = 'F:\SQL Backups\BeforeUpgrade'
DECLARE @EachFolder BIT
-- If @EachFolder = 1 it makes a subfolder for each databases
-- If @EachFolder = 0 it does not. all BAK in same folder
SET @EachFolder = 0
-- ***** DO NOT MODIFY BELOW THIS LINE ********
DECLARE @mydb VARCHAR(2000)
DECLARE @rowcount INT
DECLARE @table table (rownum int identity(1,1),name sysname)
 
 
USE MASTER
PRINT 'DECLARE @loc VARCHAR(2000)'
PRINT 'set @loc = '''+@LOCATION+''''
PRINT 'DECLARE @mydb VARCHAR(2000)'
PRINT 'DECLARE @subdir VARCHAR(2000)'
PRINT 'DECLARE @sufixbk VARCHAR(20)'
PRINT 'DECLARE @full_bk_name VARCHAR(2000)'
PRINT 'DECLARE @bk_name VARCHAR(2000)'
PRINT 'DECLARE @msg VARCHAR(2000)'
PRINT 'DECLARE @backupSetId AS INT'
PRINT ''
 
-- Optional : you can modify the SELECT list for the while loop
-- This SELECT statement determines what databases to generate the backup scripts
 
INSERT INTO @table
SELECT name FROM sysdatabases WHERE VERSION is not null
and databasepropertyex(name,'Status') = 'online'
and name not in ('tempdb')
-- and name in ('database1','database2','database3')
 
SET @rowcount =1
WHILE @rowcount < = (select count(name) from @table)
BEGIN
 
SELECT @mydb = name from @table where rownum =@rowcount
 
 
PRINT '--*************** processing '+@mydb+' ************'
PRINT ' '
PRINT 'set @subdir = '''+ @mydb+''''
IF (@EachFolder=1) PRINT 'set @loc = @loc +''\'' +@subdir + ''\'''
PRINT 'set @sufixbk = CONVERT(VARCHAR(20),GETDATE(),112)'
PRINT 'set @bk_name = '''+@mydb+ '''+''_'' + @sufixbk + ''.bak'''
PRINT 'set @full_bk_name = @loc + ''\'' + @bk_name'
IF (@EachFolder=1) PRINT 'EXECUTE master.dbo.xp_create_subdir @loc'
PRINT 'BACKUP DATABASE ['+@mydb+'] TO DISK = @full_bk_name WITH NOFORMAT, NOINIT, NAME =@bk_name, SKIP, REWIND, NOUNLOAD, STATS = 10'
PRINT ',BUFFERCOUNT = 2200, BLOCKSIZE = 65536, MAXTRANSFERSIZE=2097152'
PRINT 'select @backupSetId = position from msdb..backupset where database_name='''+@mydb+''' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='''+@mydb+''')'
PRINT 'set @msg =''Verify failed. Backup information for database ['+@mydb+'] not found.'''
PRINT 'if @backupSetId is null begin raiserror(@msg, 16, 1) end'
PRINT 'RESTORE VERIFYONLY FROM DISK = @full_bk_name WITH FILE = @backupSetId, NOUNLOAD, NOREWIND'
PRINT ' '
 
SET @rowcount = @rowcount+1
END