Category Archives: SQL Scripts

Script to get data file size, used space and free space

While troubleshooting the disk space issue, you can use below script to check the data file size, used space and free size.

select 
		DBName,
		name,
		[filename],
		size as 'Size(MB)',
		usedspace as 'UsedSpace(MB)',
		(size - usedspace) as 'AvailableFreeSpace(MB)'
from		
(	
SELECT
db_name(s.database_id) as DBName,
s.name AS [Name],
s.physical_name AS [FileName],
(s.size * CONVERT(float,8))/1024 AS [Size],
(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
) DBFileSizeInfo


Advertisements

Stripping SQL Server Database Backup to Multiple Files

Stripping Database backup to multiple files and on different drives will make the backup speed faster and will reduce the backup duration.

Check the below sample script for the backup and restore. You can perform the same task using SSMS GUI as well.
Backup Script

BACKUP DATABASE [SQLDBPool] TO  
DISK = N'C:\JSpace\Backup\SQLDBPool1.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool2.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool3.bak'
WITH NOFORMAT, 
NOINIT,  
NAME = N'SQLDBPool-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Restore Script

RESTORE DATABASE [SQLDBPool] FROM  
DISK = N'C:\JSpace\Backup\SQLDBPool1.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool2.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool3.bak'
WITH  FILE = 1,  NOUNLOAD,  STATS = 10,replace
GO

Script to get the SQL Server Installation Date and Authentication Mode

Recently I got a request where I have to check the SQL Server installation date and SQL Server authentication mode. I have write the below script and execute it against all the servers registered in CMS.

Here is the script

select createdate as InstallationDate ,
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
WHEN 1 THEN 'Windows Authentication'   
WHEN 0 THEN 'Windows and SQL Server Authentication'   
END as [AuthenticationMode],
SERVERPROPERTY('servername') as svrName 
from master..syslogins where name like 'NT AUTHORITY\SYSTEM'

How to attach a SQL Server database with a missing transaction log file?

There may be situation where the transaction log file is corrupted or delete due to some issue, there is still hope to recover the database using data file. In the below script we will create the scenario and recover the database using data file only.

-- create the below sample database
create database [SQLDBPool_5] 

-- execute the command to get the data log file location
sp_helpdb sqlhelpdesk

-- Copy the location of log file and data file from the output of the above command
-- C:\JSpace\sqldbpool_5_log.ldf, C:\JSpace\sqldbpool_5.mdf

-- Detatch the database
USE [master]
GO
ALTER DATABASE [SQLDBPool_5] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'SQLDBPool_5'
GO

-- Delete the log file
xp_cmdshell 'del "c:\JSpace\SQLDBPool_5_log.ldf"'


-- attach the database using MDF file only SQL Server will create the log file its self
USE [master]
GO
CREATE DATABASE [SQLDBPool_5]  ON 
( FILENAME = N'C:\JSpace\sqldbpool_5.mdf' )
FOR ATTACH
GO

-- You will the below message on the execution of the above command
/*
File activation failure. The physical file name "c:\JSpace\SQLDBPool_5_log.ldf" may be incorrect.
New log file 'c:\JSpace\SQLDBPool_5_log.ldf' was created.
*/

LiteSpeed – Restore Script

Script to check the backup file – data and log file information

exec master.dbo.xp_restore_filelistonly
@filename ='<BackupFilePath>\<BackupFileName.bak>'

Script to check the backup file header information

exec master.dbo.xp_restore_headeronly
@filename ='<BackupFilePath>\<BackupFileName.bak>'

Script to verify the backup file if the backup file is valid or not

EXEC master.dbo.xp_restore_verifyonly @filename ='<BackupFilePath>\<BackupFileName.bak>'

Script to restore database using Full backup with the default options

exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>'

Script to restore database using Full backup with file move option

exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"'

Script to restore database using Full backup with replace option

exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'replace',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"'

Script to restore Full backup with no recovery

exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'replace',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"',
@with='NORECOVERY'

Script to restore log backup with no recovery

EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='NORecovery'

Script to restore log backup with recovery

EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='Recovery'

Script to do point in time recovery

EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='Recovery',
@with = 'STOPBEFOREMARK = <LogMark>'

LiteSpeed – Backup Scripts

full or complete database backup

execute master.dbo.xp_backup_database
  @database = '<database name>',
  @filename = '<path>\<backup file name>.bak',
  @init = 1,
  @compressionlevel = 4

differential backup

 
 execute master.dbo.xp_backup_database
  @database = '<database name>',
  @filename = '<path>\<backup file name>.bak',
  @init = 1,
  @compressionlevel = 4,
  @with =  differential

transaction log backup

 
 execute master.dbo.xp_backup_log
  @database = '<database name>',
  @filename = '<path>\<backup file name>.trn',
  @init = 1,
  @compressionlevel = 4

filegroup backup

   
 execute master.dbo.xp_backup_database 
  @database = '<database name>',
  @filename = '<path>\<backup file name>.bck',
  @init = 1,
  @compressionlevel = 4,
  @filegroup = 'filegroupname'