Category Archives: sql

Generate a Change Script to Move System Databases

I set this up to be modular and reusable, because I like reusing things.  This doesn’t actually move your system databases.  It just outputs a change script and a revert script.  You still have to run the generated script, then log on to your server and stop the services and move the files yourself.  In my example, I’m moving TempDB from C (ew) onto better storage.
declare @dbname sysname,
@oldpath varchar(255),
@newpath varchar(255)

set @dbname='tempdb'
set @oldpath='C:\Program Files\Microsoft SQL Server\MSSQLSERVER\MSSQL\DATA' --no trailing slash
set @newpath='H:\HappyStorage' --no trailing slash

/*  Generates your change script */
SELECT 'alter database ' + @dbname + ' modify file (name=' + name + ', filename = ''' + replace(physical_name,@oldpath,@newpath) + ''');'
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);

/* Generates a revert script */
SELECT 'alter database ' + @dbname + ' modify file (name=' + name + ', filename = ''' + physical_name + ''');'
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);

/* Tells you where the files are now */
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);
GO

Sample output would  include, in the top window:

alter database tempdb modify file (name=tempdev, filename = 'H:\HappyStorage\Data\tempdb.mdf');
alter database tempdb modify file (name=templog, filename = 'H:\HappyStorage\Log\templog.ldf');

Copy and paste that into a new window, and run it if you’re sure it’s right.

You can, of course, use this for MSDB or any other database you want to move.

Comments Off on Generate a Change Script to Move System Databases

Filed under scripting, sql

Pull the restore chain from MSDB

What files do I need to restore this database to a point in time?

You might know this off the top of your head, especially if you set up the backups or if you don’t have a lot of backups, but just in case you’re panicking, ask MSDB.  MSDB knows all… unless that data has been purged.  (Sorry.)

declare @dbname varchar(80),
@lastfull datetime,
@lastdiff datetime,
@fullback varchar(1024),
@diffback varchar(1024),
@RowsToProcess int,
@CurrentRow int,
@restorediff varchar(max),
@logback varchar(1024),
@restoreday datetime

set @dbname = ‘YourDB’
set @restoreday = ‘1/1/2016 8:05am’ –Make sure the time zone is your server’s time zone.

select @lastfull = max(backup_finish_date)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = ‘D’
WHERE d.database_id NOT IN (2, 3) and d.name=@dbname
and description like ‘Backup on%’
and backup_finish_date < @restoreday

SELECT    @fullback = m.physical_device_name
FROM         msdb.dbo.backupmediafamily AS m INNER JOIN
msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id
and b.type=’D’ and b.database_name=@dbname
AND b.backup_finish_date=@lastfull

print @fullback

SELECT @lastdiff = b.backup_finish_date FROM msdb.dbo.backupmediafamily AS m INNER JOIN msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id and b.type=’I’ and b.database_name=@dbname AND b.backup_finish_date>@lastfull AND b.backup_finish_date < @restoreday

If @@ROWCOUNT = 0
begin
set @lastdiff = @lastfull
end

select @diffback = m.physical_device_name FROM msdb.dbo.backupmediafamily AS m INNER JOIN msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id and b.type=’I’ and b.database_name=@dbname AND b.backup_finish_date=@lastdiff

print @diffback

CREATE TABLE #logs (RowID int not null primary key identity(1,1), logback nvarchar(255),finishdate datetime)
insert into #logs SELECT distinct m.physical_device_name, b.backup_finish_date FROM msdb.dbo.backupmediafamily AS m INNER JOIN msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id and b.type=’L’ and b.database_name=@dbname AND b.backup_finish_date>@lastdiff AND b.backup_finish_date<@restoreday order by b.backup_finish_date

SELECT @RowsToProcess=COUNT(logback) from #logs

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@logback=logback
FROM #logs
WHERE RowID=@CurrentRow
print @logback
END

drop table #logs

Comments Off on Pull the restore chain from MSDB

Filed under scripting, sql

Windows Internal Database/SQL Express backups and maintenance

Google informs me that my post on Windows Internal Database Maintenance is popular.  It’s not what I’m currently using, however.

I’ve switched over to Ola Hallengren’s scripts.  If you download and run the installer script, it creates a series of agent jobs, which, well.  Neither Windows Internal Database nor SQL Express has SQL Server Agent.  However, the jobs run as cmdexec scripts that look like

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'f:\sql_backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 672, @CheckSum = 'Y', @LogToTable = 'Y'" -b

It’s easy to run these through Task Scheduler.  You’ll want to replace “$(ESCAPE_SQUOTE(SRVR))” with your actual server.

(The connection string for the version of Windows Internal Database based on 2008 is \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query.  The connection string for the version of Windows Internal Database based on 2012 is \\.\pipe\MICROSOFT##WID\tsql\query.  SQLExpress on your current host is .\SQLExpress by default.)

Continue reading

Comments Off on Windows Internal Database/SQL Express backups and maintenance

Filed under scripting, sql

Katherine’s Excellent Log-Shipping Adventure

Or, Log-Shipping over 1200 databases automagically.

Picture1

Continue reading

Comments Off on Katherine’s Excellent Log-Shipping Adventure

Filed under monitoring, powershell, sql

SQL Server Express Maintenance

Don’t you love looking at your old scripts?

I’ve rewritten all of our maintenance jobs, including the jobs in task manager that handle SBSMonitoring.  I’ve rid myself of Windows Internal Database, but was still using the script I wrote for it.  Not any more!

I decided to use and abuse Ola Hallengren’s scripts, so what I’m using for SBSMonitoring is:

Download and install CommandExecute.sql, DatabaseIntegrityCheck.sql, and IndexOptimize.sql. Then, create the following .sql file:

Declare @4wks datetime
set @4wks = DateAdd(hh, DateDiff(hh, 0, GETDATE()) – 672, 0)

EXECUTE [dbo].[IndexOptimize] @Databases = ‘ALL_DATABASES’, @OnlyModifiedStatistics = ‘Y’
EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = ‘ALL_DATABASES’

exec msdb.dbo.sp_delete_backuphistory @oldest_date = @4wks

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ AND TABLE_NAME = ‘CommandLog’))
BEGIN
DELETE FROM [dbo].[CommandLog] WHERE DATEDIFF(dd,StartTime,GETDATE()) > 30
END

IF @@SERVERNAME = ‘MYSBSSERVER\SBSMONITORING’
BEGIN
EXECUTE [SBSMonitoring].[dbo].[CleanupDatabase]
END

This can be called with sqlcmd in Task Scheduler.  (“sqlcmd -E -SmyServer -i c:\path\to\scriptname.sql”)

I’ve also started using Microsoft’s script to reindex SUSDB.  I migrated that into SQL Server, so it’s an actual job, but SBS doesn’t support migrating the SBSMonitoring database out of the SBSMonitoring 2005 Express instance that’s installed by default, hence the scripty maintenance.

Comments Off on SQL Server Express Maintenance

Filed under sql