I need to get a list of database sizes for a SQL server migration project. The SQL Server server we're migrating from has three installed instances, one default and and two named. It's running SQL Server 2008 R2, on Server 2008 R2, if it matters.
To get this list, I dusted off the old T-SQL "script" I used at my last position as the defacto/accidental DBA, and it works as expected on the default instance, but returns no data on either of the named instances.
SELECT [Database Name] = DB_NAME(database_id),
[Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE Type_Desc END,
[Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )
FROM sys.master_files
GROUP BY GROUPING SETS
(
(DB_NAME(database_id), Type_Desc),
(DB_NAME(database_id))
)
ORDER BY DB_NAME(database_id), Type_Desc DESC
GO
Can someone tell me why (and how to fix it), or suggest a better solution?
(It's gotta be the FROM sys.master_files
bit, because what else could it be, but I'm not sure what I should be using instead on a named instance.)
EDIT: Below is what I'm seeing on my named instances, anyway… so if this code does/should work on named instances, thoughts on why it doesn't work on these would be much appreciated.
Best Answer
If you just need the size of the entire database then below script will help you :
If you want to automate your migration of databases from one server to another, I highly recommend using sqlmigration - a powershell script that will migrate your databases, jobs, logins, etc from one server to another. Also, be aware of the pre and post migration steps if you are moving from one version of sql server to higher (2012 or 2014) version.
Depending on your size of databases and the amount of downtime, there are ways that you can implement to minimize the downtime for your entire migration - e.g. using logshipping or mirroring and then during cutover, just failover to the new server, etc.
Always prepare and test your migration and rollback procedures.
Edit: From your screenshot, it seems that you do not have permissions. Can you just do a
select * from sys.master_files