Below are some code snippets / resources for T-SQL that I have personally benefited from. If you do not find one that you are looking for or want more, just drop me a request or comment below, I’d try to address the request.
Other Useful resources:
|Pinal Dave’s Blog / Website|
SQLAuthority.com / http://www.pinaldave.com Question and Answers E-Book : PDF
|Find size of your database|
|Recently I came across this snippet
SELECT DB_NAME(dbid) AS DatabaseName ,Name as LogicalFileName ,CASE WHEN GroupID=1 THEN 'Data' ELSE 'Log' END AS FileType ,FileName as FilePath ,size as TotalPage ,(Size*8192E)/1048576 AS FileSizeInMB FROM master..SysAltFiles
|SQL Server Express 2014 : Server Authentication|
2014 Express drove me nuts to enable SQL Authentication, but after several failed attempts I figured SQL Server Express must have disabled SQL authentication by default, it turned out to be true. Once that was known it simply took several seconds to get the job done. Below were the steps I performed :
|Ba(c)king up large database ? Split it !|
|Recently I had to back up and restore a huge database. ( Well, I don’t usually tell 8 GB huge, but I’ll tell you why : SQL server was in Data-center, giving fastest speed up-to 25 KB/sec for FTP transfer for some reason, hence the adjective. ) So what I was doing was after I had .BAK file, I’d use 7-zip to compress and split my *.7z ( or .zip / .RAR – I found 7z better ) and then I’d transfer them so even if the transfer fails on 14th of 30-ish splits, I don’t have to start over – somewhat better. This was one way.
The SQL server did take it’s time to back up this large database. Let’s back up Northwind database on my local SQL Server 2012 Exp instance and see what happens :
(FYI : My, database is merely 4.25 MB in size, you might not see major difference as this database is small, but you can see major difference if you are having database with several GBs.)
SET STATISTICS TIME ON BACKUP DATABASE Northwind TO DISK = 'C:\Backups\NW_0803141030.bak' SET STATISTICS TIME OFF GO
Processed 424 pages for database 'Northwind', file 'Northwind' on file 1. Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 1. BACKUP DATABASE successfully processed 426 pages in 0.807 seconds (4.119 MB/sec). SQL Server Execution Times: CPU time = 15 ms, elapsed time = 1020 ms.
Well, not bad for 4.25 MB database approx. The size of .BAK file was shown 3.42 MB in Windows Explorer.
SET STATISTICS TIME ON BACKUP DATABASE Northwind TO DISK = 'C:\Backups\NW_0803141035_1.bak' ,DISK = 'C:\Backups\NW_0803141035_2.bak' ,DISK = 'C:\Backups\NW_0803141035_3.bak' --WITH INIT , NOUNLOAD , NAME = 'Northwind backup', NOSKIP , STATS = 10, NOFORMAT SET STATISTICS TIME OFF
Large database and do you want to see the progress in % ?
Processed 424 pages for database 'Northwind', file 'Northwind' on file 1. Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 1. BACKUP DATABASE successfully processed 426 pages in 0.794 seconds (4.186 MB/sec). SQL Server Execution Times: CPU time = 47 ms, elapsed time = 947 ms.
CPU time is greater, but disk write I/O operation speed is little higher ( 4.186 vs 4.119 mbps ) hence lesser elapsed time. I encourage you to try the later approach with different number of chunks ( 3 in this case ) and see if that makes difference.
After all you can always try the single file and multiple approach at least once and if splitting up makes difference, Yay ! If not you at least have 7 Zip 😀
Is there any way so we can take the backup of database of 100GB & we have three disks with different size of each file ?
D:/ 30GB free
Sadly, as far as I know SQL Server would simply split the files in equal or nearly equal files. But I think, there *might* be a workaround … at the time of splitting up specify different drives. Do your math and see if that works for you ! 🙂
In the case above I should try to get each split chunk < 20 GB, I’d make 17 GB to be safe [ Why 20 GB ? Min (30,20,80). ]
DISK = ‘D:\Backups\NW_0803141035_1.bak’
Change SQL Schema
Alright so you wanna change the schema for multiple tables from dbo ( or whatever ) to something else ? Chances are high that you might come across this ( undocumented ) piece of code that I personally DO NOT LIKE and DO NOT RECOMMAND :
exec sp_MSforeachtable "ALTER SCHEMA TargetSchema TRANSFER ?"
I am not saying it’s creepy but if you’re using diagrams and such in your database, it might also screw up your database’s sysdiagrams etc table ( and you might not realize ) I recommend this way :
USE [SomeDatabaseNameHere] SELECT 'ALTER SCHEMA new_schema TRANSFER dbo.' + t.NAME FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE s.NAME = N'dbo';
Not “WHAM & BAM” like the script above but still pretty straight forward and most importantly, it lets you review ( if you have a time to pause and scroll through the table names )
Secondly, at the end of the day it is still a select query with a WHERE clause ! You can have a granular control as you please ! For instance :
USE [SomeDatabaseNameHere] SELECT 'ALTER SCHEMA new_schema TRANSFER dbo.' + t.NAME FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE s.NAME = N'dbo' AND t.NAME LIKE '%configuration%'
… this way, you only change the schema for the tables that have configuration word in their name !
And finally this is how your outcome should look like :
ALTER SCHEMA app TRANSFER dbo.Table_1 ALTER SCHEMA app TRANSFER dbo.Table_2 ALTER SCHEMA app TRANSFER dbo.Table_3 . . . ALTER SCHEMA app TRANSFER dbo.Table_N
All you have to do now is to copy-paste them in query and F5 !
1. you don’t have to spend too much of your time to write a smart ass code
So you have been using “IF … EXISTS” pattern in your TDD scenario or wherever you want to re-run a script … and if you have not specifically encountered a situation where you’ve to do it with schema, something might make you pull up your hairs !
USE [SomeDatabaseNameHere] IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'My_New_Schema') begin CREATE SCHEMA [My_New_Schema] AUTHORIZATION [dbo] end
… and Error : “Incorrect syntax near the keyword ‘My_New_Schema’.” !
Why in its infinite wisdom does SQL Server (2008 R2) give you error ? Thanks to Phil Steffek’s post ( quoted below ) for writing a shot and sweet post !