Scribd
Upload
58 views13 pages

SQL Commands: Backup Command

The document provides a list of SQL commands used for database backup, restoration, maintenance, automation, locking, and performance monitoring. Some key commands include backup and restore commands to take full, differential, transaction log, and partial backups or restore databases. Other commands check database integrity, fragmentation, optimize indexes, monitor locks, performance counters, and more.

Uploaded by

Sreenivasulu Reddy Sanam
Copyright
© © All Rights Reserved
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
Download as doc, pdf, or txt
58 views13 pages

SQL Commands: Backup Command

The document provides a list of SQL commands used for database backup, restoration, maintenance, automation, locking, and performance monitoring. Some key commands include backup and restore commands to take full, differential, transaction log, and partial backups or restore databases. Other commands check database integrity, fragmentation, optimize indexes, monitor locks, performance counters, and more.

Uploaded by

Sreenivasulu Reddy Sanam
Copyright
© © All Rights Reserved
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
Download as doc, pdf, or txt
Download as doc, pdf, or txt
You are on page 1/ 13

SQL Commands

 Backup Command

1:- Full Backup: - take the backup of entire database which can
backuped up in case of failure.

Backup database database name to disk =’ F:\test_1 backup\Full


backup\databasename.bak’

2:- Differential Backup:- whatever the changes made in the database


since the last full backup was performed can e backed up through
differacial backup.

Backup database database name to disk =’ F:\test_1


backup\differential\databasename.bak’ with differential

3:- Transaction Log backup:- All the log record can be backup by which
is written to transaction log since the last full backup and log backup
was performed.

Backup log database name to disk =’ F:\test_1 backup\Full


backup\databasename.trn’

4:- File and File group Backup:-When a database is divided across many
files and filegroups, these file and file group can be backuped
individually.
5:-Backup database database name file group=’secondary’ to disk=
‘F:\test_1 backup\Full backup\databasename.bak’

6:-Partial Backup: - a Partial database backup consists of the primary


filegroup, read write file groups and and read only file group specified.

7:-Backup database database name read_write_filegroups to disk=’


F:\test_1 backup\partial\databasename.bak’

8:-Stripe Backup:-Striping a backup across multiple devices in the


backup process since multiple physical devices are being written to
simultaneously.

9:-Backup database database name to disk= ‘F:\test_1 backup\Full


backup\databasename.bak’

10:-Backup database database name to disk= ‘F:\test_1 backup\Full


backup\databasename2.bak’

11:-Backup database database name to disk= ‘F:\test_1 backup\Full


backup\databasename3.bak’

12:-Mirror Backup:- Mirror backup is copy of your original backup.

13:-Mirror to disk= ‘F:\test_1 backup\Fullbackup\databasename.bak’


with format
14:-With copyonly Backup:- It will not effect the log chains.

15:-Backup log database name to disk= ‘F:\test_1 backup\Full


backup\databasename.bak’ with copy_only

 Restoration of all database backup Command

16:- Restore database database name from disk =’ F:\test_1


backup\Full backup\database name\pa_backup_201102251205.bak’
with no recovery

17:- Restore database database name from disk =’ F:\test_1


backup\differential\database name\pa_backup_201102251205.bak’
with no recovery

18:- Restore log database name from disk =’ F:\test_1 backup\Full


backup\database name\pa_backup_201102251205.bak’ with
recovery

19:- Restore database database name file group secondary from disk
=’ F:\test_1 backup\Full backup\database
name\pa_backup_201102251205.bak’

20:- Restore database database name read_write_filegroups from


disk=’ F:\test_1
backup\partial\databasename\pa_backup_201102251205.bak’
21:-Select * from sys.databases_recovery_status this
command is use for check the backup has been taken or
not.

22:-Restore verifyonly from disk = ‘F:\test_1 backup\Full


backup\databasename.bak’ this command is use for check the
restoration has been completed or not.

 Maintaning and Automating SQL Server

DMV Command

23:sys.dm_db_index_physical_stats this command is use


check the fragmantion level.

24:- dbcc_showcontig (‘Tablename’) this command is use


for check the fragmantion level of the table

25:-dbcc dbreindex (‘Tablename’) this command is use


for reindexing.

26:-alter index reiorgnize this command is use for


reorgnize.

27: sp_helpfile this command is use for check the


database file.

28:-sp_helpindex tablename this command is use for


check the index for a table.

29:-sp_helpdb:- this command is use for check the


database id no.
30:-alter index (IndexID) on tablename rebuild this
command is use for reindex on the table

31:-alter index (indexid) on tablename reorgnize This


command is use for reorgnize on the table.

32:-dbcc show_statistics:- this command is use for


check the mapping b/w index and table.

33:- update statistics tablename wth fullscan this


command is use for update the mapping b/w index and
table..

34:-dbcc sqlperf (logspace) this command is use for


check the log size.

35:-dbcc loginfo this comand is use for check the logs.

36:-alter database databasename modify file


(name=databasename_log,size=500) This coomand is use
for decrese the log size.

37:-dbcc checkdb this command is use for check the


allocation,logical and structal intigrity of objects in
the database.

38:- select index_id,avg_fragmantation_in_percent

from sys.dm_db_index_physical_stats(db_id(),

object_id (‘Tablename’),

default,default,’detailed’ this command is use for


check the fragmanation level in percent.

39:-dbccc shrink filename (fileid,500) this command is


use for relesing the unnessary pages.
40:-alter index (indexid) with (fill factor=70) this
command is use for living the space in the pages for
future expansions.

41:- create index indexname on tablename (columnname)


this command is use for the create the index in a
table.

42:-create view (Viewname)his command is use for create


the view.

43:-truncate table tablename this command is use for


turncate the record of the table.

44:- select getdate() this command is use for check the


date

 LOCK COMMAND
45:- SP_LOCK This command is use for check the lock.

46:- sp_who This command is use for check the lock.

47:- sp_who2 this command is use for check the lock.

48:- select * from sys.sysdatabassse where blocked <> 0


This command is use for check the blocking.

49:-dbcc inputbuffer (spid) this command is use for


find the lock.

50:- select * from sys.dm_tran_locks command is use for


check the lock.

51:- select db_name(spid) this command is use for find


the lock
52:-Sys.dm_os_performance_counters:- This command is
use for check the utilization of CPU,and all the
counters like(lock,deadlock,block).

53:-Select * from dbo.querytunning:-

54:-Select @@servername this command is usse for check


the server name

55:-Select @@version this command is use for chck the


version of sql server.

56:-select * from sysobjects

select * from information_schema.tables ----to see only


the tables pertaining to DB.

57:-Select * from sysaltfiles check for db file path.

58:-Select * from sysaltfiles where dbid = 2 check for


Particular database file path.

59:- sp_helpsrvrolemember 'sysadmin' check the Sysadmin


privilieges of the users in the database
60:- sp_helplogins check for Other privilieges of the
users in the database

61:- select serverproperty('productlevel'),('edition')


To know edition and SP for SQL Server.

62:- Select * from sysobjects where type='U'


type='v' or 'S' To get the list of user tables, views
and stored procedures in db

63:- select * from sysperfinfo where counter_name like


'%buffer cache%' To check perfmon information

64:- select * from sysperfinfo where counter_name like


'%user connections%' To check perfmon information

65:- SELECT * FROM fn_trace_getinfo(default) To get the


default trace file path

66:- select * from fn_trace_gettable(path of the


default trace);

67:- use master


alter database test
modify file(name=test_data, filegrowth=500MB) Query to
add free space in a data file

68:- backup log db_name with truncate_only To truncate the


log (once we truncate the log file we need to shrink the log file)

69:- sp_configure 'show advanced option', 1 To check the


advanced options.

70:- dbcc showcontig with all_indexes To know about all the


indexes

71:-dbcc freesystemcache('all') To Free space occupied


by TempDB without restarting SQL services

72:-select * from fn_trace_getinfo(default) To know the


path of the Trace file situated

73:- select * from sysperfinfo where counter_name


like'%user%' check the user conncation

74:- select * from sysperfinfo where counter_name


like'%buffer cache%' Check for cache hit ratio

75:- dbcc perfmon check for perfomance

76:- sp_jobruninfo 1,0 gives the reprt of duration of


jobs

77:- backup log databasename with truncate_only it will


truncate the log file.

78:- Select name,recovery_model_desc from sys.databases


check the recovery model status

79:- sp_who2 'active' check for current query running.

80:- sp_helptext disk_space


81:- sp_spaceused check the db size and free space.

82: dbcc useroptions:- Check for which one isolation


level set.

83 checkpoint

84 select * from sys.dm_os_buffer_descriptors where


is_modified<>0 to check the memory utiliaztion

85 sp_who2 ‘active’

86 select * from sys.dm_exec_connections to check the


processer speed

87 select * from sys.dm_exec_sessions

88 select * from sysprocesses where spid>

89 select * from sys.dm_tran_locks where request_mode<>


‘s’

90 select * from sys.dm_io_pending_io_requests

91 select * from sysprocesses where spid> 50 order by


cpu desc

92 select * from dbo.dmv_result where wait_time>0 and


database_name in ('sow_db')
93 Exec sp_help_logshipping_moniter

94 create nonclusterd index indes name on table name


(column name)

95 Drop index indes name on table name

96 select * from dbo.dmv_result where wait_time>0 and


database_name in ('sow_db')

97 select * from sys.sysprocrsses where dbid= dbid to


check the user connevtion and open transaction
98 select
spid,blocked,dbid,cpu,hostname,program_name,cmd,loginame from
sysprocesses
where blocked <>0

99 select
spid,blocked,dbid,cpu,hostname,program_name,cmd,loginame from
sysprocesses
wherespid=85

100 select * from sysprocesses where spid=

select * From sys.dm_os_waiting_tasks: to check the wait type

sp_configure 'max degree of parallelism',30 to configure the Max DOP value

reconfigure with override: after executing the uper command mandatry to run

You might also like