SQL Stuff

Quick SQL Logic For Backups And Restores

Here’s some quick SQL Logic to create some backups, and do some restores. Nothing new, or crazy here. Just something quick and easy for people to throw into their automation logic. Sure; these are not hard to find, but it’s helpful none the less to have it out there.

use master;
set nocount on

/********  ABOUT BACKUPS ********/

-- backup single database (overwrite backup file if exists (format), verify backup (checksum)
backup database [MyDatabase] to disk = 'E:\SQLBACKUPS\MyDatabase.bak' with format, checksum

-- backup all databases  (overwrite backup file if exists (format), verify backup (checksum), the question mark(?) denotes @MyDatabaseName automatically.  this script does not require any changes.  just run.
exec master..sp_msforeachdb
'if (''?'') not in (''tempdb'')
		backup database [?] to disk = ''E:\SQLBACKUPS\?.bak'' with format, checksum;

-- verify backup date and location (occurred today)
	'database'		= upper(bs.database_name)
,	'backed up on'	= left(bs.backup_finish_date, 19)
,	'file location'	= upper(bmf.physical_device_name)
	msdb..backupset bs join msdb..backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
	-- check backups that occurred today
	bs.backup_finish_date > (select dateadd(day, datediff(day, 0, getdate()), 0))
	-- get full database backup history only
	and bs.type = 'D'
order by
	bs.backup_finish_date desc

/********  ABOUT RESTORES *******/

-- close open sessions against a database prior to restore.
declare       @close_open_sessions varchar(max)
set           @close_open_sessions = ''
select        @close_open_sessions = @close_open_sessions + 
'kill ' + cast(spid as varchar) + ';' + char(10)
from   	      sysprocesses where db_name(dbid) = 'MyDatabase'
exec   	      (@close_open_sessions)
-- restore database.  get logical and physical names from backup file with 'restore filelistonly from disk = 'E:\SQLBACKUPS\MyDatabase.bak' or from live database before the restore with 'select name, filename from [MyDatabase]..sysfiles'
restore database [MyDatabaseTest] from disk = 'E:\SQLBACKUPS\MyDatabase.bak'
,      recovery
,      move 'MyDatabase_Data' to 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDatabase_Data_01.MDF'
,      move 'MyDatabase_Log'  to 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDatabase_Log_01.MDF'
,      stats = 25


Categories: SQL Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s