Bahramov’s Personal Blog

The following T-SQL statements will print the logical name and physical location of system database files:

USE master;
GO
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'master')

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'model')

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'msdb')

Result:

In this post I will describe how to create a compressed backup of any SQL Server database in Windows. This is especially useful if you have Express or MSDE edition of SQL Server where there’s no SQL Agent. Let’s look a this example:

1. First we will create a folder, where we want to keep or database backups. Let’s call it C:\DbBackup.
2. Now, we’ll create a sql script that will backup our database. I will use Northwind database in this example. The created backup file will have the name Northwind_[current date]. bak Open a Notepad and paste the following T-SQL code:

-- Create backup of NorthWind Database
-- add current date to the file name and
-- save it inside C:\DbBackup folder
--
DECLARE @str NVARCHAR(255)
SET @str=N'C:\DbBackup\Northwind_'+CONVERT(VARCHAR, GETDATE(), 105)+'.bak'
BACKUP DATABASE Northwind TO DISK=@str

3. Save file as NorthwindBackup.sql

Read more…

From: www.mssqltips.com

Solution
Checking for free disk space before proceeding with a process is a wise move if disk space is tight or a high percentage of the drive is needed for the process. It is disconcerting to have a process run for hours only to fail towards the end of the process due to insufficient disk space. Although a few different options are available to check for disk space (CLR, WMI, PowerShell, etc.) in SQL Server, let’s see how we can use the xp_fixeddrives extended stored procedure which is available in SQL Server 2000 to 2008.

In the sample stored procedure below, it is accepting a parameter for the minimum amount of megabytes (MB) free on a specific disk drive, then executing the master.sys.xp_fixeddrives extended stored procedure into a temporary table. Once the data is in the temporary table the current amount of free disk space is compared to the minimum amount of free disk space to determine if the process should continue or raise an error.
Read more…