Tag Archives: t-sql

Generic Backups

I support developers.  In our dev/QA environment, people add, delete, and change the recovery model of databases all the time without telling me.  How do I keep track of which databases need to be backed up?

I don’t.  I back up everything.

Master has information on all databases.  That means master can give you a list of what needs to be backed up.  Of course, you don’t want to back up tempdb (because you can’t restore tempdb), and I haven’t customized model, so:

Declare @DBname varchar(255),
@RowsToProcess int,
@CurrentRow int,
@SQL nvarchar(max),
@today nvarchar(50),
@servername nvarchar(50)

set @today = convert(nvarchar,GETDATE(),12)
set @servername = REPLACE(@@SERVERNAME,’\’,’-‘)

CREATE TABLE #maintenancetemp (RowID int not null primary key identity(1,1), dbname nvarchar(max) )
INSERT into #maintenancetemp (dbname) select name from sys.databases where name <> ‘tempdb’ and name <> ‘model’
SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@DBname=dbname
FROM #maintenancetemp
WHERE RowID=@CurrentRow
set @SQL=’BACKUP DATABASE [‘ + @DBName + ‘] TO DISK = N”S:\backups\’ + @servername + ‘-‘ + @DBName + ‘-‘ + @today + ‘.bak” WITH FORMAT, INIT, NAME = N”Full ‘ + @DBName + ‘Database Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10’
–print @SQL
EXEC SP_EXECUTESQL @SQL
END

drop table #maintenancetemp

Here’s the full backup script that loads that into a temp table and loops through the rows and backs them up.  You can, of course, customize this script to suit your preferred file name convention and backup location.  You can, of course, also plop that into a job or, if you’re running something like Windows Internal Database or SQL Express, run it via sqlcmd and Task Manager.

What about log backups?  If it’s just dev/QA data, is it important?  Is QA going to jump out of a window if they lose data?

I don’t ask.  I just back it up.  Master knows what’s in full recovery, too:

select name from sys.databases where name <> ‘tempdb’ and name <> ‘model’ and recovery_model IN (1, 2)

This log backup script assumes hourly backups, which might be overkill for your environment and might not.  I’ve seen “dev” and “QA” environments that were treated like prod because they were actually for training and the company lost money if training couldn’t take place, so I’d rather err on the side of being backed up.  Again, customize to suit your environment.

I’ve started running these against any new internal server-based SQL Server instance I find out about.  We do have instances of SQL Server 2005 Express and the like, for testing, so sqlcmd continues to be my friend.

Comments Off on Generic Backups

Filed under scripting

Seekrit Message to a Friend

You didn’t get this from me.  You downloaded it off the internet.

In fact, why don’t I turn that bunch of scripts into a series of jobs and give it to you as one big lump?  (Download.)

How to use:

  1. Load file into SQL Server Management Studio.
  2. Replace all instances of BACKUP DATABASE [DBNAME] with the proper database name in the square brackets.
  3. Either create the directory f:\sql or edit the jobs that refer to it to go to a different path.
  4. Click execute.

And remember, I know nothing.  If I knew something, I’d have to charge.

Comments Off on Seekrit Message to a Friend

Filed under scripting, sysadmin

Windows Internal Database Maintenance

“Windows Internal Database,”  is basically SQL Server with network access and the maintenance tools turned off.  You might want to do maintenance on whatever’s in there, anyway.  I use Windows Task Scheduler for that, at least until I move the database to one of our full SQL installs, but you might be stuck with the free stuff.  If so, or if you’re a perfectionist like me, here’s a maintenance script for WSUS (as an example) running on Windows Internal Database:

sqlcmd -E -S\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i “C:\[path]\sseemaint.sql”
sqlcmd -E -S\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i “C:\[path]\wsusbackfull.sql”

(Download.)

You can probably tell that’s pulling SQL query files and passing them to sqlcmd, the SQL command line utility.  (You can download sqlcmd here.)  Here’s the maintenance script, which does an integrity check and reindexes every database that isn’t tempdb or model:

Declare @DBname varchar(255),
@RowsToProcess int,
@CurrentRow int,
@SelectCol1 varchar(max),
@SQL nvarchar(max)

CREATE TABLE #maintenancetemp (RowID int not null primary key identity(1,1), dbname nvarchar(max) )
INSERT into #maintenancetemp (dbname) select name from sys.databases where name <> ‘tempdb’ and name <> ‘model’
SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@SelectCol1=dbname
FROM #maintenancetemp
WHERE RowID=@CurrentRow
set @DBname = @SelectCol1
set @SQL=’DBCC CHECKDB(N”’ + @DBName+ ”’)  WITH NO_INFOMSGS’
EXEC SP_EXECUTESQL @SQL
set @SQL=’USE [‘+@DBname+’]; exec sp_MSforeachtable @command1=”print ”?” DBCC DBREINDEX (”?”, ” ”, 80)”; EXEC sp_updatestats’
EXEC SP_EXECUTESQL @SQL
END

drop table #maintenancetemp

(Download.)

And here’s the SUSDB backup script.  It’s a full back up of SUSDB, but you could, obviously, use it for any DB:

Declare @today nvarchar(50),
@SQL nvarchar(4000)

set @today = convert(nvarchar,GETDATE(),12)

set @SQL=’BACKUP DATABASE [SUSDB] TO  DISK = N”F:\SQL\SUSDB-‘+ @today +’.bak” WITH FORMAT, INIT,  NAME = N”SUSDB-Full Database Backup”, SKIP, NOREWIND, NOUNLOAD,  STATS = 10′
EXEC SP_EXECUTESQL @SQL

(Download.)

You’ll also want a separate script to back up the logs:

Declare @today nvarchar(50),
@SQL nvarchar(4000)

set @today = convert(nvarchar,GETDATE(),12) + ‘-‘ + replace(CONVERT(VARCHAR(26), GETDATE(), 108),’:’,”)

set @SQL=’BACKUP LOG [SUSDB] TO  DISK = N”F:\Transaction Logs\susdb-‘+ @today +’.bak” WITH FORMAT, INIT,  MEDIANAME = N”SUSDB-Transaction Log Backup”,  NAME = N”SUSDB-Transaction Log  Backup”, SKIP, NOREWIND, NOUNLOAD,  STATS = 10′
EXEC SP_EXECUTESQL @SQL

(Download.)

These spit out date or datetime labelled backups, so you can get rid of the old ones as your storage fills up.  You can schedule these in task scheduler to run daily, weekly, hourly, whatever your pain point is for what’s in Windows Internal Database.  Good luck!

Speaking of WSUS, you can migrate that from Windows Internal Database to a full SQL install using these instructions, and can move the content to a new drive using these instructions.

3 Comments

Filed under scripting

A Script for Migrating SQL Data (and Log) Files

Suppose that you’ve been asked to migrate 166 databases’ .mdf files from one drive to another.  How would you do that?

I’d generate a list based on the .mdf files (the databases are all named the same thing as the .mdf.  If this wasn’t the case, we’d have to query SQL for it–“select name from sys.databases”–and get the file name out of the database’s sys.database_files table) and do this:

@ECHO OFF

set controlfile=test.txt

SETLOCAL DISABLEDELAYEDEXPANSION
FOR /F %%L IN (%controlfile%%) DO (
SET “line=%%L”
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO !line:.mdf=!
sqlcmd -E -S.\prod1 -Q”EXEC master.dbo.sp_detach_db @dbname = N’!line:.mdf=!'”
copy “C:\SQL Server Data\Prod1\Data\!line!” “D:\SQL Server Data\Prod1\Data”
sqlcmd -E -S.\prod1 -Q”CREATE DATABASE [!line:.mdf=!] ON ( FILENAME = N’D:\SQL Server Data\Prod1\Data\!line!’ ),( FILENAME = N’C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf’ ) FOR ATTACH”
move “C:\SQL Server Data\Prod1\Data\!line!” “C:\SQL Server Data\Prod1\Data\moved\”
ENDLOCAL
)
ENDLOCAL

(Download.)

In this example, the logs were then moved, too.

@ECHO OFF

set controlfile=test.txt

SETLOCAL DISABLEDELAYEDEXPANSION
FOR /F %%L IN (%controlfile%%) DO (
SET “line=%%L”
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO !line:.mdf=!
sqlcmd -E -S.\prod1 -Q”EXEC master.dbo.sp_detach_db @dbname = N’!line:.mdf=!'”
copy “C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf” “F:\SQL Server Data\Prod1\Log\”
sqlcmd -E -S.\prod1 -Q”CREATE DATABASE [!line:.mdf=!] ON ( FILENAME = N’D:\SQL Server Data\Prod1\Data\!line!’ ),( FILENAME = N’F:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf’ ) FOR ATTACH”
move “C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf” “C:\SQL Server Data\Prod1\Log\moved\”
ENDLOCAL
)
ENDLOCAL

(Download.)

You could always combine both actions in the same script, too.  There was a reason to move the files separately, but we could move them together and add a backup to the script:

@ECHO OFF

set controlfile=test.txt

SETLOCAL DISABLEDELAYEDEXPANSION
FOR /F %%L IN (%controlfile%%) DO (
SET “line=%%L”
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO !line:.mdf=!
sqlcmd -E -S.\prod1 -Q”BACKUP DATABASE [!line:.mdf=!] TO DISK = N”F:\SQL backups\!line:.mdf=!.bak” WITH COPY_ONLY, WITH FORMAT, INIT, NAME = N”!line:.mdf=!-Full Database Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10″
sqlcmd -E -S.\prod1 -Q”EXEC master.dbo.sp_detach_db @dbname = N’!line:.mdf=!'”
copy “C:\SQL Server Data\Prod1\Data\!line!” “D:\SQL Server Data\Prod1\Data”
copy “C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf” “F:\SQL Server Data\Prod1\Log\”
sqlcmd -E -S.\prod1 -Q”CREATE DATABASE [!line:.mdf=!] ON ( FILENAME = N’D:\SQL Server Data\Prod1\Data\!line!’ ),( FILENAME = N’F:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf’ ) FOR ATTACH”
move “C:\SQL Server Data\Prod1\Data\!line!” “C:\SQL Server Data\Prod1\Data\moved\”
move “C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf” “C:\SQL Server Data\Prod1\Log\moved\”
ENDLOCAL
)
ENDLOCAL

(Download.)

How would you do it?

Comments Off on A Script for Migrating SQL Data (and Log) Files

Filed under scripting

Nagios Plugin – SQL Job Status

I checked out Nagios Exchange, and didn’t see anything that checked the status of a job and used Windows/AD credentials/trusted connection.  So I wrote this.

It’s intended to run as an NRPE script, and doesn’t require anything that doesn’t come with Windows and SQL Server.  There’s a stored procedure and a batch file, both of which can be modified to suit your purposes.

You also need to enable nrpe and external scripts in nsc.ini, and define the checks in the [External Scripts] section.  Run the script with the job name as an argument (“scripts\last_sql_job_run_status.cmd some_job_name”).  An optional second argument is an integer representing how many days ago the job ran before Something Is Wrong [TM] (“scripts\last_sql_job_run_status.cmd some_job_name 3”).  If no time period is specified, it assumes a week.  The plugin returns critical if the job failed and warning if it succeeded but is late.

For trusted connections, you can run nsclient++ as a service account and give that account read access to sysjobs and sysjobactivity in msdb and execute on the procedure itself, although it should work out of the box for a default nsclient++ install checking localhost.  You can also use sql server authentication if you edit the line invoking sqlcmd accordingly.

Enjoy.

Comments Off on Nagios Plugin – SQL Job Status

Filed under monitoring, scripting