Backup Database Commands

SQL Server Administration: Backup database to one file or multiple files. Use these commands to back your databases up without using a maintenance plan. Backing up to multiple files is good to use for large databases and will break up a single large backup file to several smaller files. Good to use when large backups are hard to move across the network.

–
– Backup a database to a single backup file
–
DECLARE @BackupFile NVARCHAR(200)
DECLARE @Timestamp VARCHAR(50)
– create a timestamp for a unique name
SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),’-',”),’:',”),’ ‘,”)
– create backup file string
SET @BackupFile = ‘D:\SQLBackups\AdventureWorks2012_Full1_’+@Timestamp+’.bak’
– backup the database
BACKUP DATABASE AdventureWorks2012
TO DISK = @BackupFile
WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2012-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

–
– Backup a database to multiple backup files
–
DECLARE @BackupFile1 NVARCHAR(200)
DECLARE @BackupFile2 NVARCHAR(200)
DECLARE @BackupFile3 NVARCHAR(200)
DECLARE @BackupFile4 NVARCHAR(200)
DECLARE @Timestamp VARCHAR(50)
– create a timestamp for a unique name
SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),’-',”),’:',”),’ ‘,”)
– create backup file strings
SET @BackupFile1 = ‘D:\SQLBackups\AdventureWorks2012_Full1_’+@Timestamp+’.bak’
SET @BackupFile2 = ‘D:\SQLBackups\AdventureWorks2012_Full2_’+@Timestamp+’.bak’
SET @BackupFile3 = ‘D:\SQLBackups\AdventureWorks2012_Full3_’+@Timestamp+’.bak’
SET @BackupFile4 = ‘D:\SQLBackups\AdventureWorks2012_Full4_’+@Timestamp+’.bak’
– backup the database
BACKUP DATABASE [AdventureWorks2012]
TO DISK = @BackupFile1,
DISK = @BackupFile2,
DISK = @BackupFile3,
DISK = @BackupFile4
WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2012-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

New DMV for Cached Stored Procedures (SQL 2008 – 2012)

SQL Server Administration: (SQL 2008 – SQL 2012) Use the new dynamic management view sys.dm_exec_procedure_stats to view performance statistics about cached stored procedures. This dmv will only show information on the stored procedures that are still in the cache. If a stored procedure is aged out or removed from the cache then performance info will not be in the view. For more information check out Books Online or http://msdn.microsoft.com/en-us/library/cc280701(v=sql.110).aspx

SELECT TOP 10 d.object_id, d.database_id,
OBJECT_NAME(object_id, database_id) ‘proc name’,
d.cached_time, d.last_execution_time, d.total_elapsed_time, d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;

SQL 2012 IIF() Function and CASE Expression

A new function available in SQL Server 2012 is IIF. IIF is very similar to the CASE expression and is in essence a shorthand way to write a CASE function. The IIF function returns one of two values depending on whether the first expression evaluates to TRUE or FALSE. The IIF function can be nested up to 10 levels just like the CASE expression. IIF can only evaluate two values and no more.

Here is a simple example of each:

– IIF

DECLARE @a int = 2012, @b int = 2013;

SELECT IIF( @a > @b, ‘Out with the old.’, ‘In with the new.’ ) AS HappyNewYear;

GO

– CASE

DECLARE @a int = 2012, @b int = 2013;

SELECT (CASE WHEN @a > @b THEN ‘Out with the old.’ ELSE ‘In with the new.’ END) AS HappyNewYear;

GO

For more information:

http://msdn.microsoft.com/en-us/library/hh213574.aspx

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Try the new Dynamic Management View

(SQL
2008 R2 and SQL 2012) Use the new dynamic management view sys.dm_server_services
to see information about SQL Server services such as when service last started,
running status, service account, etc… For more information, check out Books
Online or http://msdn.microsoft.com/en-us/library/hh204542.aspx

Get a List of CLR’s Registered to a Database

Get a list of CLR’s registered to a database.  This is helpful when migrating or troubleshooting.  For more information on CLR’s check out Books Online or http://msdn.microsoft.com/en-us/library/ms131089(SQL.100).aspx

 

SELECT o.name AS ObjectName, a.name AS Assembly,

      o.type_desc AS ObjectType

FROM   sys.assembly_modules am

JOIN   sys.objects o ON am.object_id = o.object_id

JOIN   sys.assemblies a ON a.assembly_id = am.assembly_id

WHERE  a.is_user_defined = 1

UNION

SELECT at.name, a.name, ‘Type’

FROM   sys.assembly_types at

JOIN   sys.assemblies a ON at.assembly_id = a.assembly_id

WHERE  a.is_user_defined = 1

Remove maintenance plans left after a server rename.

Many times when upgrading SQL Server or migrating to new hardware, it is necessary to rename the server when it’s ready to be used as production.  However, if any maintenance plans created before the server was renamed are still present after the rename, you may find that trying to delete the old jobs results in error 547 (below).

Drop failed for Job ‘User Databases – Full Daily Backup.Subplan_1′. (Microsoft.SqlServer.Smo)

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.
The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_schedule_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘schedule_id’.
The statement has been terminated.
The statement has been terminated. (Microsoft SQL Server, Error: 547)_________________________________________________________________________ 

 Here are the steps to correct the problem:

 – Find the maintenance plan name and id that you want to delete.

– Write down the id of the one you want to delete.

SELECT name, id FROM msdb.dbo.sysmaintplan_plans

 

 – Place the id of the maintenance plan you want to delete

– into the below query to delete the entry from the log table

DELETE FROM msdb.dbo.sysmaintplan_log WHERE plan_id = ‘<<id from 1st query>>’

 – Place the id of the maintenance plan you want to delete

– into the below query and delete the entry from subplans table

DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE plan_id = ‘<<id from 1st query>>’

 – Place the id of the maintenance plan you want to delete

– into the below query to delete the entry from the plans table

DELETE FROM msdb.dbo.sysmaintplan_plans WHERE id = ‘<<id from 1st query>>’

 Now you can delete the jobs from Management Studio. 

Find out when statistics were last updated.

Your SQL database uses statistics to choose the most efficient plan for retrieving or updating data, so you want them accurate and up-to-date. Otherwise, it can cause the SQL Server engine to take the wrong path, and taking the wrong path means slower performance. Check out the code below to see when your stats were last updated.   If you need help with SQL server, please contact me. It’s also good practice to regularly monitor the 11 VitalSigns used by SQLRx to debug performance bottlenecks.

SQL Server Administration:  Find out when statistics were last updated.   Out of date statistics are a common cause of poor performance.  Tables that are active should have statistics updated regularly.  Use the query below to find out if your databases have old statistics.   USE <<database>> GO – find last time when stats had been updated. SELECT object_id AS [TableId], index_id AS [IndexId], OBJECT_NAME(object_id) AS [TableName], name AS [IndexName], stats_date(object_id,index_id) stat_update_date, INDEXPROPERTY(object_id, name,’IsAutoStatistics’) is_system_generated_stats FROM sys.indexes WITH (NOLOCK) OPTION(MAXDOP 1) GO

What’s Causing Files to Automatically Grow

Every DBA has encountered a database that has a mystery statement that will unexpectedly cause either the transaction log or a table to grow out of control and fill the drive.  Here is a way to find out who is running what statement that is causing the problem.  Find out what statements are causing database files to automatically grow.

 /*  Step 1 – Capture a Profiler or server side trace with these events:

      Event 41 = SQL:StmtCompleted

      Event 45 = SP:StmtCompleted

      Event 92 = Data file auto growth event

      Event 93 = Log file auto growth event

* Make sure to include these columns:

      ApplicationName,EventClass,DatabaseID,HostName,LoginName,

      ObjectID,SPID,StartTime,EndTime,TransactionID,TextData,

      Reads,Writes,CPU,Duration

*/   

–    Step 2 – Load the trace into a table:

SELECT*INTO TraceTable FROM::fn_trace_gettable(<<path to trace file>>,DEFAULT)

GO

–    Step 3 – Make a column to hold part of the textdata for search purposes

ALTERTABLE TraceTable ADD QueryText VARCHAR(MAX)

GO

UPDATE TraceTable SET QueryText =CONVERT(VARCHAR(MAX), TextData)

GO

–    Step 4 – Get all spids and trasactionids that are associated with auto growth

SELECTDISTINCT SPID, TransactionID

INTO #GrowTrans

FROM TraceTable

WHERE EventClass IN(92, 93)

GO

–    Step 5 – Get the info on statements called by the spid and transactionid associated with auto growth

SELECT t.*

FROM TraceTable t

JOIN #GrowTrans g ON (t.SPID = g.SPID AND t.TransactionID = g.TransactionID)

ORDERBY StartTime

GO

Attempt to Recover a Suspect Database

Databases will go into suspect state for a multitude of reasons. If you find your database in SUSPECT state, check all error logs and the Windows Event Log for any related errors, and try to resolve them. This is a disaster situation!  Refer to the script below for help.

SQL Server Administration:  You can try to save data by creating an empty database and copying tables to it after placing the database into EMERGENCY and then SINGLE_USER state. Most of the time, a suspect database has to be restored to be able to be used again. However, sometimes it is possible to repair a suspect database. I recently did just that for a client, and recovered 4 suspect databases.

 

Use the script below to check the integrity of the database or to repair it. Running an integrity check will tell you where the errors are and what the minimum repair you can do is. Always make sure you have database and transaction log backups so you can recover a failed database to point in time, if necessary. 

– Attempt repair suspect database

USE master;

GO

– Set database in EMERGENCY read only state

ALTER DATABASE MyDB SET EMERGENCY

GO

– Set database in SINGLE_USER state

ALTER DATABASE MyDB SET SINGLE_USER

GO

USE MyDB

DBCC CHECKDB(MyDB) WITH NO_INFOMSGS  — Run integrity Check

– DBCC CHECKDB (MyDB, REPAIR_REBUILD) WITH NO_INFOMSGS  — Repair database with no dataloss

– DBCC CHECKDB (MyDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS  — Repair database with possible dataloss

GO

– Set database in MULTI_USER state

ALTER DATABASE MyDB SET MULTI_USER

GO

Find out How Much Data Files are Waiting with Filestats

Disk performance is a common issue on SQL Server systems.  In these situations, it is very useful to know how much SQL Server is waiting for I/O and what files are handling the most work or experiencing the poorest performance.  The virtual file stats DMF provides this information and is ideal for periodic captures, e.g., at the beginning, middle, and end of a day.  This function returns one row per file and can return this information either for specific files or for every file attached to a SQL Server instance.

To retrieve information for all files attached to a 2005 or 2008 instance, use the following syntax:

select * from sys.dm_io_virtual_file_stats (NULL, NULL)

The equivalent on SQL 2000 is as follows:

select * from ::fn_virtualfilestats(-1,-1)

Some of the most useful information is shown below:

• Number of reads and writes issued against this file
• Total number of bytes read from and written to this file
• Total time, in milliseconds, users waited for I/O completions overall (also reads and writes specifically on 2005 and 2008)
• Number of disk bytes used by this file

Since these values are cumulative from the last time the SQL Server instance was restarted, differences between samples must be computed.  Either SQL Server T-SQL or Excel can be used to perform these calculations.  In addition, the database and file IDs must be translated into meaningful names using the sys.databases  and sys.database_files DMVs on 2005 and 2008.  For those still using 2000, system tables must be used to decode these values.

Using this information, a DBA can prove whether the disk subsystem is performing adequately and possibly find alternative disk locations to which database files may be moved.