Restoring MSSQL database files to different names, location.

Sometimes you’ll want to restore a database to a different name.

Here’s now you can get the filelist and also change the destination location.

Get the DB File List

RESTORE FILELISTONLY FROM DISK= N'path of backup file'

Restore to a different location

ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

RESTORE DATABASE databasename

FROM DISK= 'path of backup file'

WITH REPLACE,

MOVE 'Logical DB Name' TO 'new DB file location (with filename).mdf',

MOVE 'Logical Log Name' TO 'new log file location (with filename).log';

GO

ALTER DATABASE databasename SET MULTI_USER;

GO

Getting class files from LinqPad

There’s no end of tools that can create C# class’s from a database. But it’s never as easy or as flexible as it should be.

Two tools that I trust are LinqPad and dotPeek. I use LinqPad to generate the dll that contains the required classes and dotPeek to extract the class files from the dll.

Going all the way to dotPeek is really only viable if there’s a large number of class files you want. If there’s just one or two, IPSpy is all you need.

Here’s how to do it.

  • Establish a database connection as normal in LinqPad

  • Then “Reflect Query in ILSpy”, at this stage, no query needs to be created, just the db connection, (or press Alt+Shift+R)

ReflectQuery.png

  • In ILSpy, navigate to “TypedDataContext_xxxxx

DataContext.png

  • Unfortunately, ILSpy doesn’t have a feature to export the class’s to cs files. However, you can use the free JetBrains dotPeek to decompile the dll created by Linqpad and create yourself a C# project,

    • From ILSpy, right click the required namespace and select open containing folder, this will show you where the dll is.

    • Using dotPeek, import the dll, right click the dll, and select “Create new Project”

T-SQL Size used by Table and Row Counts 

USE [YourDBName] -- replace your dbname
GO
SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY s.Name, t.Name
GO