List Row Count on All Tables in SQL Server Database

If you are using SQL Server 2008 or higher you can query the sys.partitions catalog view. The filed [rows] shows the number of records in each partition of the database table.

This query returns the schema and table names, and total number of rows. If you have only one partition for each table, you can wipe out SUM() and GROUP BY from the query.

SSDT Database Project. Problems with References to Another Database or Server

I’ve received a note that SQL Server Data Tools has some problems when working with references to another database or server in a database project. I wanted to find out what is really happens in SSDT when I import a database with dependencies. I’m using Visual Studio 2013 with Update 4.

I’ve created a database with the stored procedure that will use the following things in queries:

  1. linked server;
  2. four-part name;
  3. synonym.

The import of database to SSDT database project has finished successfully. I noticed that the linked server has not been imported. I think that’s because linked server is a server object, not a database one. But you can add a linked server to your database project manually.

When I open the stored procedure which queries the linked server, I see a warning about unresolved reference.

SSDT project. Reference to linked server

The same warning appears when I open a procedure with a four-part name reference. As for the last case with a synonym, SSDT does not show any warning. But in all cases we’ve got some difficulties/features such as:

  • Intellisense does not work on that objects;
  • Build passes with a lot of warnings SQL71502 or SQL71562 about unresolved references;
  • Build passes even if I’ve made an error in the name of a referenced table/field/function, etc.;
  • Errors can be found only in the deployment phase.

Summary:

  • we can use any form of references to another databases via linked server, four-part name notation and synonym;
  • linked server is not imported to database project automatically;
  • SSDT is unaware of objects through the references that will lead to a late discovering of errors.

SQL Server Data Tools. Max Files Per Folder Option

During my session on 24 Hours of PASS 2015 Russian Edition I was asked about the Import database wizard in SQL Server Data Tools. The particular option is “Maximum files per folder”.

SSDT. Import database. Max files per folder

This option allows you to split the whole number of database objects to separate folders with a specified quantity of these objects. The dropdown list contains three values: 100, 500 and 1000. You can enter the value manually, but the minimum is 20 (SSDT auto-corrects it if you enter a quantity less then 20).

As a result you will get the following structure in you database project.

SSDT. Import database. Project structure 100

Add a Native-Boot VHD to Windows Boot Menu

If you have installed Windows on virtual hard disk, you can copy this VHD to another machine and add it to boot menu.

This can be made with the following steps:

1.Mount VHD:

  • just double-click on it and the disk will be automatically (works on Windows 8.1 and Windows Server 2012);
  • if the first method does not work, open Command prompt and run commands:

You can choose any free disk letter to assign for your VHD, not only Z.
2.Add entry to boot menu.
Open Command prompt, if it was not opened before, and run commands:

3.Optionally reboot the operating system and you will see a new entry in boot menu.
Now you can select this OS and run it!

Installing Windows from USB Flash

This post is about how to prepare the USB Flash to install Windows from it.  It works fine with Windows 8.1 and Windows Server 2012. Maybe it could work with Windows 7 and Windows Server 2008 (I has not checked myself).

1) Mount Windows image. Just double-click on ISO image. Remember the letter of the drive that Windows assigned to this image. In my case it’s disk G:

2) Plug in USB flash and remember the letter of the drive. It’s H:

3) Open command prompt and do NOT close it until the end.

4) Run the following commands to create a NTFS partition on USB flash:

Command Description
diskpart starts Disk Partition tool
list disk shows a list of disks, find USB flash disk and remember its number
select disk # replace # with USB flash disk number
clean deletes everything on USB flask
create partition primary creates partition
select partition 1
active makes it active
format fs=ntfs quick quick formats with NTFS file system
exit

5) Run the following commands to create a boot sector on USB flash:

Command Description
G: change drive, G: is a drive letter of Windows image
cd boot go to a folder
bootsect /nt60 H: create a boot sector, H: is a drive letter of USB flash

6) Copy all files from all directories, including empty subdirectories, hidden and system files from Windows image (drive G:) to USB flash (disk H:):

cd .. go to a drive root
xcopy G: H: /E /F /H

7) Close command prompt

SQLSaturday #398 Krasnodar Precon: Managing files and backups in Microsoft SQL Server

sqlsat398_web

I’m glad to announce that I will hold a full-day training at SQLSaturday #398 on June, 5 2015 (Krasnodar, Russia). As a Microsoft trainer I’ve taught many courses for DBA but I have not seen a thorough education kit that can make a solid base for disaster recovery. So I’ve started to learn my self how to make it efficiently and now I want to share my knowledge with the students.

On my opinion, a good skills on disaster recovery are impossible without a good understanding of how SQL Server operates with files through the IO subsystem, what is an internal structure of SQL Server files and so on. That’s why I combine two topics into single course.

This training will be about managing database files and transaction log, how to make backup and restore – from simple cases such as full or log backup to a more complicated scenarios like piecemeal restore. We will talk about optimization both the file management and recovery process. Also we mention how to save OLAP databases in SSAS and packages in SSIS.

Short description

Each DBA meets sooner or later the situation when the database grows and it needs to be moved to another disk, the free space is running out unexpectedly, or that is even worse, the hard drive has gone away and it is necessary to restore the database immediately. You have to work very quickly and, that’s the most important, competently.
In this seminar we start from the fundamental basics of SQL Server file management, including data files and transaction log file, take a look how to optimize it.
Then we dive into the world of backups – from simple backup and restore to more complicated scenarios such as piecemeal restore. We reveal the secrets how to make backup/restore process faster.
In practice we will work with a number of “how-to” scenarios concerning file management and database recovery which DBA can meet in his job.

Contents

  • Fundamental basics of SQL Server file management (data files and transaction log file);
  • Internals of transaction log and optimization;
  • Managing file location and size;
  • Planning the recovery solution and choosing the recovery model;
  • Backup and restore basics;
  • Complicated recovery scenarios (file, filegroup and page backup/restore);
  • Optimization of backup and restore process;
  • Additional features of SQL Server Enterprise version (online restore, piecemeal restore, database snapshot);
  • Dealing with system databases;
  • Dealing with FileStream;
  • Working with the databases of SQL Server Analysis Services and Integration Services.

Extending VHD

I’ve installed Windows 7 on VHD a couple of years ago and now I need some extra free space to install program. So I have to extend my VHD disk and the partition.

This can be done from another Windows operating system with the diskpart command.

If needed we can check the statuses of disks and volumes by entering additional commands

I think it would be possible to run the Windows installation disk, open Command Prompt by pressing Shift+F10 and make all the steps listed above.

Installing Windows on VHD

You can install Windows 7 and above on hard disk or on virtual hard disk (VHD). In the last case you need to create a VHD file on hard disk and install Windows inside that VHD file. The boot loader is placed outside the VHD automatically during the installation process.

During Windows installation you will see a prompt to select a type of installation: upgrade or custom.

Press Shift + F10. The Command Prompt will appear. Type the following commands:

The size of the VHD will be 100Gb.

After that select the Custom option to make a clean installation of Windows.

Important! The type of VHD must be fixed because I’ve encountered problems to install Windows from DVD or USB flash on expandable disk. But when you install Windows inside Hyper-V virtual machine, you won’t have any problem with expandable VHD.