To start SSMS on Windows Server 2012 R2 Standard I …
1) Clicked in the bottom-left corner of the screen – which brought up a bunch of blue and green boxes.
2) Clicked on the little down-arrow in a circle near the bottom-left of the screen.
2b) If not visible I clicked a blank part of the screen – outside the boxes.
3) This is like an ‘All Programs’ screen.
4) Find and click “SQL Server Management Studio”.
Get good quickly
Better than training courses, reading, or a junior role. The fastest way to become great at your job is to do it every day.
Your future self will thank you for fearlessly diving in (so long as your current self can remain employed lol).
You can serve only one God.
Whatever you fall asleep thinking about, your sub-consious will continue to work on over-night.
If your last thoughts are about impressing your boss, your technical work the next day will be pedestrian.
The two groups I see perpetually falling foul of this rule are Manager\Dba’s and Developer\Dba’s
Simplicity is the ultimate complexity.
To get to a simple solution, keep working once you have a complex solution.
Wisdom it more important than knowledge.
For example. It takes at least ten years to progress from Junior DBA to Data Architect.
Not because it takes that long to amass the skills neccessary to do the job. But the experience to be successful at it.
Make changes singley.
It may be tempting to sort out that long standing problem during a migration, but the chances of the whole thing failing are increased exponentially.
Mitigate risk and aid troubleshooting by making changes one at a time.
Avoid Scripting.
A well crafted SQL statement is a thing on beauty, and writing one gives a great deal of satisfaction.
But creating art is not the job.
Script is fragile (To know how fragile, count the number of potential typo’s). Which is fine for those who’s output is intended to be constantly improved upon. But I feel DBA’s should favour robustness over the satisfaction of creativity, and implement for the long term.
If there is simply no other choice but using a script, avoid the near endless cycle of creating // debugging // augmenting your own, and download industrial-strength scripts from a trusted source (like Ola Hallengren). But read and understand them before using in production. 🙂
These are the (risk mitigating) rules I have worked-out for myself over the years …
Delete nothing
If you have to, make a copy first. And never automate deletes.
To run a SQL-Job on the same Server use …
EXECUTE msdb.dbo.sp_start_job 'JobName'
To run a SQL-Job on another Server use …
EXECUTE [ServerName].msdb.dbo.sp_start_job 'JobName'
(assuming LinkedServers etc are already set-up)
** Update ** I had an issue where the remote job-name could not be found. The cause (I saw in sys.servers) was that I had used the wrong version of SSMS to create the link. The fix was to amend a working scripted-out link.
I wanted to improve a backup / restore sql-job that populated a Reporting Server every night. I felt it would be quicker if it did a weekly Full backup and daily Diff backups.
The 3rd-party backup software was “SQL Safe Backup” from Idera.
I used this code on the Production Server …
--BackupDBA.sql -- FULL Backup if Friday IF (SELECT DATEPART(dw, GETDATE())) = 6 BEGIN EXEC [master].[dbo].[xp_ss_backup] @database = 'DBA', @filename = '\\ReportingServer\m$\DBA_Full.safe', @backuptype = 'Full', @overwrite = 1, @verify = 1; END; -- DIFF Backup EXEC [master].[dbo].[xp_ss_backup] @database = 'DBA', @filename = '\\ReportingServer\m$\DBA_Diff.safe', @backuptype = 'Diff', @overwrite = 1, @verify = 1;
… and this code I scheduled a few hours later on the Reporting Server …
--RestoreDBA.sql
-- First, kill any connections
DECLARE @kill VARCHAR(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(VARCHAR(5), spid) + ';'
FROM [master].[dbo].[sysprocesses]
WHERE dbid = DB_ID('DBA')
AND spid > 50;
EXEC (@kill);
-- Restore FULL
EXEC [master].[dbo].[xp_ss_restore]
@database = 'DBA',
@filename = 'M:\DBA_Full.safe',
@backuptype = 'Full',
@recoverymode = 'norecovery',
@replace = '1';
-- Restore DIFF
EXEC [master].[dbo].[xp_ss_restore]
@database = 'DBA',
@filename = 'M:\DBA_Diff.safe',
@backuptype = 'Diff',
@recoverymode = 'recovery',
@replace = '1';
Finally, I added a step on Reporting to repair the orphaned user-accounts …
USE [DBA] GO EXEC sp_change_users_login 'Auto_Fix', 'ReportingLogin'; EXEC sp_change_users_login 'Auto_Fix', 'LinkedServerLogin'; GO
After exhausting all the usual methods to get a database out of ‘Recovery Pending’ mode, I deleted it then reattached the mdf file.
(NB: I made a copy of mdf file first, but did not need it).
This is the logical conclusion of my reset_tempdb and reset_model scripts. It show all of the file sizes and autogrowth settings in the current instance and the code to change them.
The suggested sizes (128 MB for Logfiles and 256 MB for Datafiles) are reasonable for Model, but should probably be amended for other databases dependent on current size and autogrowth history.
--autogrowth_all.sql -- get current settings & create commands to change them select db.Name, case mf.[Type] when 0 then 'DATA' else 'LOG' end [FileType], convert(varchar(50), size*8/1024) + ' MB' [CurrentSize], case mf.is_percent_growth when 1 then convert(varchar(50), growth) + ' %' when 0 then convert(varchar(50), growth*8/1024) + ' MB' end [AutoGrowth], 'ALTER DATABASE [' + db.Name + '] MODIFY FILE (NAME = N''' + mf.Name + ''', SIZE = ' + case mf.[type] when 0 then '256' else '128' end + 'MB);' [ReSizeCommand], 'ALTER DATABASE [' + db.Name + '] MODIFY FILE (NAME = N''' + mf.Name + ''', FILEGROWTH = ' + case mf.[type] when 0 then '256' else '128' end + 'MB);' [AutogrowthCommand] from [master].[sys].[master_files] mf join [master].[sys].[databases] db on mf.database_id = db.database_id order by mf.database_id, mf.[type];
Database Consultant 