Restore Sql Server Database with scripting

I’ve found this script which neatly restores an sql server database, saving you having to manually restore and trying to close off all connections. Note you have to run this against the master database.Hopefully the contents make sense.

 

ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE TestDB 
FROM DISK = ‘C:\TestDB.bak’
WITH MOVE ‘TestDB’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDB.mdf’,
MOVE ‘TestDB_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDB.ldf’

ALTER DATABASE TestDB SET MULTI_USER

Advertisements

SQL Server 2000 script for file space usage

script below taken from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058 shows physical file info for databases hosted on an SQL Server instance

use master
go
if exists ( select * from tempdb.dbo.sysobjects o
where o.xtype in ('U') and o.id = object_id( N'tempdb..#DB_FILE_INFO' ))
drop table #DB_FILE_INFO
go

if exists ( select * from tempdb.dbo.sysobjects o
where o.xtype in ('U') and o.id = object_id( N'tempdb..#DB_INFO' ))
drop table #DB_INFO
go
set nocount on
go
create table #DB_FILE_INFO (
[ID] int not null
identity (1, 1) primary key clustered ,
[DATABASE_NAME] sysname not null ,
[FILEGROUP_TYPE] nvarchar(4) not null ,
[FILEGROUP_ID] smallint not null ,
[FILEGROUP] sysname not null ,
[FILEID] smallint not null ,
[FILENAME] sysname not null ,
[DISK] nvarchar(1) not null ,
[FILEPATH] nvarchar(260) not null ,
[MAX_FILE_SIZE] int null ,
[FILE_SIZE] int not null ,
[FILE_SIZE_USED] int not null ,
[FILE_SIZE_UNUSED] int not null ,
[DATA_SIZE] int not null ,
[DATA_SIZE_USED] int not null ,
[DATA_SIZE_UNUSED] int not null ,
[LOG_SIZE] int not null ,
[LOG_SIZE_USED] int not null ,
[LOG_SIZE_UNUSED] int not null ,
)
go

declare @sql nvarchar(4000)
set @sql =
'use ['+'?'+'] ;
if db_name() N''?'' goto Error_Exit

insert into #DB_FILE_INFO
(
[DATABASE_NAME],
[FILEGROUP_TYPE],
[FILEGROUP_ID],
[FILEGROUP],
[FILEID],
[FILENAME],
[DISK],
[FILEPATH],
[MAX_FILE_SIZE],
[FILE_SIZE],
[FILE_SIZE_USED],
[FILE_SIZE_UNUSED],
[DATA_SIZE],
[DATA_SIZE_USED],
[DATA_SIZE_UNUSED],
[LOG_SIZE],
[LOG_SIZE_USED],
[LOG_SIZE_UNUSED]
)
select top 100 percent
[DATABASE_NAME] = db_name(),
[FILEGROUP_TYPE] = case when a.groupid = 0 then ''Log'' else ''Data'' end,
[FILEGROUP_ID] = a.groupid,
a.[FILEGROUP],
[FILEID] = a.fileid,
[FILENAME] = a.name,
[DISK] = upper(substring(a.filename,1,1)),
[FILEPATH] = a.filename,
[MAX_FILE_SIZE] =
convert(int,round(
(case a.maxsize when -1 then null else a.maxsize end*1.000)/128.000
,0)),
[FILE_SIZE] = a.[fl_size],
[FILE_SIZE_USED] = a.[fl_used],
[FILE_SIZE_UNUSED] = a.[fl_unused],
[DATA_SIZE] = case when a.groupid 0 then a.[fl_size] else 0 end,
[DATA_SIZE_USED] = case when a.groupid 0 then a.[fl_used] else 0 end,
[DATA_SIZE_UNUSED] = case when a.groupid 0 then a.[fl_unused] else 0 end,
[LOG_SIZE] = case when a.groupid = 0 then a.[fl_size] else 0 end,
[LOG_SIZE_USED] = case when a.groupid = 0 then a.[fl_used] else 0 end,
[LOG_SIZE_UNUSED] = case when a.groupid = 0 then a.[fl_unused] else 0 end
from
(
Select
aa.*,
[FILEGROUP] = isnull(bb.groupname,''''),
-- All sizes are calculated in MB
[fl_size] =
convert(int,round((aa.size*1.000)/128.000,0)),
[fl_used] =
convert(int,round(fileproperty(aa.name,''SpaceUsed'')/128.000,0)),
[fl_unused] =
convert(int,round((aa.size-fileproperty(aa.name,''SpaceUsed''))/128.000,0))
from
dbo.sysfiles aa
left join
dbo.sysfilegroups bb
on ( aa.groupid = bb.groupid )
) a
order by
case when a.groupid = 0 then 0 else 1 end,
a.[FILEGROUP],
a.name

Error_Exit:

'

--print @sql

exec sp_msforeachdb @sql

--select * from #DB_FILE_INFO

declare @DATABASE_NAME_LEN varchar(20)
declare @FILEGROUP_LEN varchar(20)
declare @FILENAME_LEN varchar(20)
declare @FILEPATH_LEN varchar(20)

select
@DATABASE_NAME_LEN = convert(varchar(20),max(len(rtrim(DATABASE_NAME)))),
@FILEGROUP_LEN = convert(varchar(20),max(len(rtrim(FILEGROUP)))),
@FILENAME_LEN = convert(varchar(20),max(len(rtrim(FILENAME)))),
@FILEPATH_LEN = convert(varchar(20),max(len(rtrim(FILEPATH))))
from
#DB_FILE_INFO

if object_id('tempdb..##DB_Size_Info_D115CA380E2B4538B6CBBB51') is not null
begin
drop table ##DB_Size_Info_D115CA380E2B4538B6CBBB51
end

-- Setup code to reduce column sizes to max used
set @sql =
'
select
[DATABASE_NAME] = convert(varchar('+@DATABASE_NAME_LEN+'), a.[DATABASE_NAME] ),
a.[FILEGROUP_TYPE],
[FILEGROUP_ID],
[FILEGROUP] = convert(varchar('+@FILEGROUP_LEN+'), a.[FILEGROUP]),
[FILEID],
[FILENAME] = convert(varchar('+@FILENAME_LEN+'), a.[FILENAME] ),
a.[DISK],
[FILEPATH] = convert(varchar('+@FILEPATH_LEN+'), a.[FILEPATH] ),
a.[MAX_FILE_SIZE],
a.[FILE_SIZE],
a.[FILE_SIZE_USED],
a.[FILE_SIZE_UNUSED],
FILE_USED_PCT =
convert(numeric(5,1),round(
case
when a.[FILE_SIZE] is null or a.[FILE_SIZE] = 0
then NULL
else (100.00000*a.[FILE_SIZE_USED])/(1.00000*a.[FILE_SIZE])
end ,1)) ,
a.[DATA_SIZE],
a.[DATA_SIZE_USED],
a.[DATA_SIZE_UNUSED],
a.[LOG_SIZE],
a.[LOG_SIZE_USED],
a.[LOG_SIZE_UNUSED]
into
##DB_Size_Info_D115CA380E2B4538B6CBBB51
from
#DB_FILE_INFO a
order by
a.[DATABASE_NAME],
case a.[FILEGROUP_ID] when 0 then 0 else 1 end,
a.[FILENAME]
'

--print @sql

exec ( @sql )

select top 100 percent
*
into
#DB_INFO
from
##DB_Size_Info_D115CA380E2B4538B6CBBB51 a
order by
a.[DATABASE_NAME],
case a.[FILEGROUP_ID] when 0 then 0 else 1 end,
a.[FILENAME]

drop table ##DB_Size_Info_D115CA380E2B4538B6CBBB51

set nocount off

print 'Show Details'
select * from #DB_INFO

print 'Total by Database and File'
select
[DATABASE_NAME] = isnull([DATABASE_NAME],' All Databases'),
[FILENAME] = isnull([FILENAME],''),
FILE_SIZE = sum(FILE_SIZE),
FILE_SIZE_USED = sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED = sum(FILE_SIZE_UNUSED),
FILE_USED_PCT =
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
DATA_SIZE = sum(DATA_SIZE),
DATA_SIZE_USED = sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED = sum(DATA_SIZE_UNUSED),
LOG_SIZE = sum(LOG_SIZE),
LOG_SIZE_USED = sum(LOG_SIZE_USED),
LOG_SIZE_UNUSED = sum(LOG_SIZE_UNUSED)
from
#DB_INFO a
group by
[DATABASE_NAME],
[FILENAME]
with rollup
order by
case when [DATABASE_NAME] is null then 1 else 0 end ,
[DATABASE_NAME],
case when [FILENAME] is null then 1 else 0 end ,
[FILENAME]

print 'Total by Database and Filegroup'

select
--[Server] = convert(varchar(15),@@servername),
[DATABASE_NAME] = isnull([DATABASE_NAME],'** Total **'),
[FILEGROUP] =
case when [FILEGROUP] is null then '' when [FILEGROUP] = '' then 'LOG' else [FILEGROUP] end,
FILE_SIZE = sum(FILE_SIZE),
FILE_SIZE_USED = sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED = sum(FILE_SIZE_UNUSED),
FILE_USED_PCT =
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
--MAX_SIZE = SUM([MAX_FILE_SIZE]),
DATA_SIZE = sum(DATA_SIZE),
DATA_SIZE_USED = sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED = sum(DATA_SIZE_UNUSED),
LOG_SIZE = sum(LOG_SIZE),
LOG_SIZE_USED = sum(LOG_SIZE_USED),
LOG_SIZE_USED = sum(LOG_SIZE_UNUSED)
from
#DB_INFO A
group by
[DATABASE_NAME],
[FILEGROUP]
with rollup
order by
case when [DATABASE_NAME] is null then 1 else 0 end ,
[DATABASE_NAME],
case when [FILEGROUP] is null then 10 when [FILEGROUP] = '' then 0 else 1 end ,
[FILEGROUP]

print 'Total by Database and Filegroup Type'

select
--[Server] = convert(varchar(15),@@servername),
[DATABASE_NAME] = isnull([DATABASE_NAME],'** Total **'),
[FILEGROUP_TYPE] = isnull([FILEGROUP_TYPE],''),
FILE_SIZE = sum(FILE_SIZE),
FILE_SIZE_USED = sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED = sum(FILE_SIZE_UNUSED),
FILE_USED_PCT =
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
DATA_SIZE = sum(DATA_SIZE),
DATA_SIZE_USED = sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED = sum(DATA_SIZE_UNUSED),
LOG_SIZE = sum(LOG_SIZE),
LOG_SIZE_USED = sum(LOG_SIZE_USED),
LOG_SIZE_USED = sum(LOG_SIZE_UNUSED)
from
#DB_INFO A
group by
[DATABASE_NAME],
[FILEGROUP_TYPE]
with rollup
order by
case when [DATABASE_NAME] is null then 1 else 0 end ,
[DATABASE_NAME],
case when [FILEGROUP_TYPE] is null then 10 when [FILEGROUP_TYPE] = 'Log' then 0 else 1 end

print 'Total by Disk, Database, and Filepath'
select
[DISK] = isnull([DISK],''),
[DATABASE_NAME] = isnull([DATABASE_NAME],''),
[FILEPATH] = isnull([FILEPATH],''),
FILE_SIZE = sum(FILE_SIZE),
FILE_SIZE_USED = sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED = sum(FILE_SIZE_UNUSED),
FILE_USED_PCT =
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
DATA_SIZE = sum(DATA_SIZE),
DATA_SIZE_USED = sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED = sum(DATA_SIZE_UNUSED),
LOG_SIZE = sum(LOG_SIZE),
LOG_SIZE_USED = sum(LOG_SIZE_USED),
LOG_SIZE_UNUSED = sum(LOG_SIZE_UNUSED)
from
#DB_INFO a
group by
[DISK],
[DATABASE_NAME],
[FILEPATH]
with rollup
order by
case when [DISK] is null then 1 else 0 end ,
[DISK],
case when [DATABASE_NAME] is null then 1 else 0 end ,
[DATABASE_NAME],
case when [FILEPATH] is null then 1 else 0 end ,
[FILEPATH]

print 'Total by Disk and Database'
select
[DISK] = isnull([DISK],''),
[DATABASE_NAME] = isnull([DATABASE_NAME],''),
FILE_SIZE = sum(FILE_SIZE),
FILE_SIZE_USED = sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED = sum(FILE_SIZE_UNUSED),
FILE_USED_PCT =
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
DATA_SIZE = sum(DATA_SIZE),
DATA_SIZE_USED = sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED = sum(DATA_SIZE_UNUSED),
LOG_SIZE = sum(LOG_SIZE),
LOG_SIZE_USED = sum(LOG_SIZE_USED),
LOG_SIZE_USED = sum(LOG_SIZE_UNUSED)
from
#DB_INFO a
group by
[DISK],
[DATABASE_NAME]
with rollup
order by
case when [DISK] is null then 1 else 0 end ,
[DISK],
case when [DATABASE_NAME] is null then 1 else 0 end ,
[DATABASE_NAME]

print 'Total by Disk'
select
[DISK] = isnull([DISK],''),
FILE_SIZE = sum(FILE_SIZE),
FILE_SIZE_USED = sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED = sum(FILE_SIZE_UNUSED),
FILE_USED_PCT =
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
DATA_SIZE = sum(DATA_SIZE),
DATA_SIZE_USED = sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED = sum(DATA_SIZE_UNUSED),
LOG_SIZE = sum(LOG_SIZE),
LOG_SIZE_USED = sum(LOG_SIZE_USED),
LOG_SIZE_USED = sum(LOG_SIZE_UNUSED)
from
#DB_INFO a
group by
[DISK]
with rollup
order by
case when [DISK] is null then 1 else 0 end ,
[DISK]

print 'Total by Database'
select
--[Server] = convert(varchar(20),@@servername),
[DATABASE_NAME] = isnull([DATABASE_NAME],'** Total **'),
FILE_SIZE = sum(FILE_SIZE),
FILE_SIZE_USED = sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED = sum(FILE_SIZE_UNUSED),
FILE_USED_PCT =
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
DATA_SIZE = sum(DATA_SIZE),
DATA_SIZE_USED = sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED = sum(DATA_SIZE_UNUSED),
LOG_SIZE = sum(LOG_SIZE),
LOG_SIZE_USED = sum(LOG_SIZE_USED),
LOG_SIZE_UNUSED = sum(LOG_SIZE_UNUSED)
from
#DB_INFO A
group by
[DATABASE_NAME]
with rollup
order by
case when [DATABASE_NAME] is null then 1 else 0 end ,
[DATABASE_NAME]

Analyzing SQL Server Log Files

At work we have had issues with a SQL Server 2000 log file increasing its size and causing an error when trying to interact with the database:

[Microsoft][ODBC SQLServer Driver][SQL Server] The log file for database ‘db’ if full. Backup the transaction log for the database to free up some log space.

We had not had this error before so we ran some scripts to help work out what was going on.

The first thing we did was run DBCC SQLPERF(LOGSPACE). This gives you basic physical file size information. The example below puts the results into a table to query further.

CREATE TABLE #LogFileMonitor
(

[DatabaseName] varchar(100)
,[LogSize(MB)] real
,[LogSpaceUsed(%)] real
,[Status] int

)
INSERT INTO #LogFileMonitor
EXEC ('DBCC SQLPERF(LOGSPACE)')

SELECT * FROM #LogFileMonitor ORDER BY [LogSpaceUsed(%)] DESC

DROP TABLE #LogFileMonitor

Running this confirmed the log file in question was up to 100% capacity. To shrink the log file, you need to execute these commands:


Use db
BACKUP LOG db with truncate_only
DBCC SHRINKFILE (db_Log,2)

Once the log file was shrunk down, we explored the issues of why the log file was growing to the extent that it was causing a problem, as up until this point everything was ok. The only recent change to the database was a trigger, so I have dropped this. I will then use DBCC SQLPERF(LOGSPACE) to monitor the database as I execute SQL from the trigger to see what is causing the issue.

Some more useful commands:

-- ANLAYZE LOG FILES

use db

SELECT
*
FROM dbo.sysfiles
WHERE name = 'db_Log'

-- CHECK RECOVERY MODEL FOR Database- simple, full, bulk logged

select databasepropertyex('db','Recovery')

Using perfmon to monitor database problems with applications

At work we have had a major problem with one of our web applications. It was erroring frequently for one client, but not for others. Viewing the application log in event viewer, numerous database timeouts were listed, but what was causing this? After much deliberation, we suddenly recalled that perfmon may shine more of a light on this. This post gives instructions on how to use perfmon. Once set up and counters were running, I logged into the site and mointored perfmon whilst doing a few things. Suddenly connections rose dramatically as I began using a certain bit of fucntionality. Now all thats left to do is to fix it, but I think this will be the easier part of the job…

SQL Server merge 2 DateTime fields

At work we need to merge a datetime field with no time element and a datetime field with no time element – there are two solutions on this stack overflow post depending on what is there in the original data. Thes easiest is the first solution. You can add the fields together as DateTime are stored as floating point values. I used the second as I could not guarantee the conditions required for the first:

SELECT DATEADD(day, 0, DATEDIFF(day, 0, your_date_column)) + DATEADD(day, 0 - DATEDIFF(day, 0, your_time_column), your_time_column) FROM your_table

Use the result set of a stored procedure

To pipe the result set of a stored procedure just use exec command in an INSERT statement…

CREATE TABLE #Table1 (

ID int
,[Value] varchar(250)

)
GO
INSERT INTO #Table1 VALUES(1, 'one')
INSERT INTO #Table1 VALUES(2, 'two')
INSERT INTO #Table1 VALUES(3, 'three')
INSERT INTO #Table1 VALUES(4, 'four')
INSERT INTO #Table1 VALUES(5, 'five')
INSERT INTO #Table1 VALUES(6, 'six')
GO
CREATE PROCEDURE spTestInsert
AS
BEGIN
SELECT * FROM #Table1
END
GO
CREATE TABLE #Table2 (

ID int
,[Value] varchar(250)

)
GO
insert #Table2 exec spTestInsert

select * from #Table2

DROP TABLE #Table1
DROP TABLE #Table2
DROP PROCEDURE spTestInsert

SQLServer udf: Converting a US date to a UK date manually

CREATE FUNCTION fnConvertDate
(
-- Add the parameters for the function here
@Date varchar(200)
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @ReturnDate datetime

DECLARE @ConvertedDate varchar(200)

DECLARE @DD char(2)
DECLARE @MM char(2)
DECLARE @YYYY varchar(10)

-- date day
SET @DD = SUBSTRING(@Date,0,CHARINDEX('/',@Date))
-- date month
SET @MM = SUBSTRING(@Date,CHARINDEX('/',@Date)+1,(CHARINDEX('/', @Date, CHARINDEX('/', @Date)+1)) - CHARINDEX('/',@Date)-1)
-- date year and time
SET @YYYY = SUBSTRING(@Date,(CHARINDEX('/', @Date, CHARINDEX('/', @Date)+2))+1,LEN(@Date))

SET @ConvertedDate = (CAST((@MM +'/' + @DD+ '/' + @YYYY) As datetime))

SET @ReturnDate = (CAST(@ConvertedDate as datetime))

-- Return the result of the function
RETURN @ReturnDate

END
GO