[ The imperialist Japanese military must officially acknowledge and apologize for the coercion of young women into sexual slaves. / êꪤҳàõªòàõªÎÒ¿Ö˪˪·ª¿ð¨ÏÐñ«ëùìíÜâÏÚªÎ?ð¤àõªòÍëãÒªËìãªá¡¢Þóñªª·ªÊª±ªìªÐªÊªéªÊª¤. ]

»ç¿ëÀÚ

ID:
PW:

INDEX
01.°Ô½ÃÆÇ
°Ô½ÃÆÇ [72]
02.File Book
File ÀÚ·á½Ç [96]
Site Link [14]
°³ÀÎÆú´õ [201]
03.Field Book
Altibase [19]
Tibero [31]
MS-SQL [32]
MySQL [47]
DB2 [79]
ORACLE [3202]
PSQL [143]
CUBRID [5]
±âŸÁ¤º¸ [193]
¿î¿µÃ¼Á¦ [633]
04.Q/A Book
Q/A [53]
05.¹æ¸í·Ï
¹æ¸í·Ï [54]
·¹º§¾÷ [37]
±¸ÀÎ/±¸Á÷ [2]

±â³äÀÏ
Search
LINK
+ °¡Á· Hompy
+ DNSEver
IOTN :: Work Book :: MS SQL

 È¸¿ø°¡ÀÔ

mssql sqlcmd [ backup script ]
ÃÖ±æÈ£ [LIST]   2015-07-05 12:21:27, Á¶È¸ : 7,153

C:\Users\Administrator>sqlcmd -S 192.168.0.155,1433 -Usa -Pmssqlpwd
1> select @@version;
2> go

sqlcmd
sqlcmd -A
sqlcmd -S ¼­¹ö¸í\»ç¿ëÀÚ¸í
sqlcmd -S dbsvr\sqlexpress -U user

-- recovery model È®ÀÎ
SELECT name, recovery_model_desc FROM sys.databases;
sqlcmd -S 127.0.0.1,1433 -Q "SELECT (name + ': ' + recovery_model_desc) COLLATE DATABASE_DEFAULT FROM sys.databases"

-- FULL ·Î º¯°æ
ALTER DATABASE model SET RECOVERY FULL ;
ALTER DATABASE model SET RECOVERY SIMPLE ;
go

-- ¹é¾÷
sqlcmd -S 127.0.0.1,1433 -Q "BACKUP database model TO DISK='c:\backup\full.%date%.bak'" >> c:\backup\full.%date%.log
sqlcmd -S 127.0.0.1,1433 -Q "BACKUP LOG model TO DISK='c:\backup\full.%date%.bak'" >> c:\backup\full.%date%.log

-- select db_name
SELECT name from sys.databases;
-- dbname,filename,size
select name as DBName, filename,
  case groupid when 1 then 'Data' ELSE 'Log' END AS type,
  (size*8)/(1024.) AS SizeInMBs
from master..sysaltfiles;

-- describe table
select * from INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'database';
-- session
sp_who
sp_who 'active'
sp_who 'login_name'
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses

-- dbfile [ use dbname °ü·Ã ÆÄÀϸ¸ º¸ÀÓ ]
SELECT * FROM dbo.sysfiles

-- 3 line
select top(3) * from emp;
-- create table as select ...
select * into emp1 from emp;

-- begin tran À¸·Î transaction ½ÃÀÛ ÇØ¾ß rollback/commit °¡´É
1> rollback
2> go
¸Þ½ÃÁö 3903, ¼öÁØ 16, »óÅ 1, ¼­¹ö dbsvr\SQLEXPRESS, ÁÙ 1
ROLLBACK TRANSACTION ¿äû¿¡ ÇØ´çÇÏ´Â BEGIN TRANSACTIONÀÌ ¾ø½À´Ï´Ù.
=> begin tran / DML / rollback | commit [ tran ]


exec sp_detach_db 'testdb'
go
exec sp_attach_db 'testdb','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testdb.mdf','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testdb_log.LDF'

-- testdb 200M Á¦ÇѾøÀÌ ÀÚµ¿ 10% Áõ°¡
alter database testdb modify file (name=testdb,size=200,maxsize=unlimited,filegrowth=10)
go
alter database testdb add file (name=testdb2, filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testdb2.mdf',size=2)
alter database testdb add log file (name=testdb2_log,filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testdb2.ldf',size=2)
alter database testdb remove file testdb2_log
alter database testdb modify name = dbtest
-- 2008 ºÎÅÍ Áö¿ø identify ÀÚµ¿Áõ°¡ 1ºÎÅÍ 1¾¿ Áõ°¡
create table test ( id int identify(1,1))

-- view °ü·Ã
create view vemp as select * from emp where DEPTNO=10
select * from vemp;
-- view script È®ÀÎ
sp_helptext vemp
alter view vemp with encryption as select * from emp where DEPTNO=10
°³Ã¼ 'vemp'ÀÇ ÅØ½ºÆ®°¡ ¾ÏȣȭµÇ¾ú½À´Ï´Ù. [ sp_helptext È®ÀÎ ºÒ°¡ ]

--- ¹é¾÷/º¹±¸
--- http://www.sqler.com/bColumn/700208
BACKUP DATABASE SW_TEST TO DISK = 'D:\SQL_BACKUP\SW_TEST.BAK'
GO
BACKUP DATABASe SW_TEST TO DISK = 'D:\SQL_BACKUP\SW_TEST_DIFF1.BAK' WITH DIFFERENTIAL
GO
BACKUP DATABASe SW_TEST TO DISK = 'D:\SQL_BACKUP\SW_TEST_DIFF2.BAK' WITH DIFFERENTIAL
GO
==
RESTORE DATABASE SW_TEST FROM DISK = 'D:\SQL_BACKUP\SW_TEST.BAK' WITH NORECOVERY, REPLACE
GO
RESTORE DATABASE SW_TEST FROM DISK = 'D:\SQL_BACKUP\SW_TEST_DIFF1.BAK' WITH NORECOVERY, REPLACE
GO
RESTORE DATABASE SW_TEST FROM DISK = 'D:\SQL_BACKUP\SW_TEST_DIFF1.BAK' WITH RECOVERY
GO
-- ¹é¾÷µÈ Á¤º¸
-- http://www.sqler.com/bColumn/700217
SELECT
A.DATABASE_NAME, B.PHYSICAL_DEVICE_NAME,
CAST(A.BACKUP_SIZE/1000000 AS VARCHAR(14))+' '+'MB' AS BKSIZE,
CAST (DATEDIFF(SECOND,A.BACKUP_START_DATE , A.BACKUP_FINISH_DATE)AS VARCHAR(4))+' '+'SECONDS' AS RUNTIME,
A.BACKUP_START_DATE,
CASE A.[TYPE]
WHEN 'D' THEN 'FULL BACKUP'
WHEN 'I' THEN 'DIFFERENTIAL BACKUP'
WHEN 'L' THEN 'TRANSACTION LOG BACKUP'
END AS BACKUPTYPE,
A.SERVER_NAME,
A.RECOVERY_MODEL
FROM MSDB.DBO.BACKUPSET AS A
INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY AS B
ON A.MEDIA_SET_ID = B.MEDIA_SET_ID
WHERE A.DATABASE_NAME = 'SW_TEST'
ORDER BY DATABASE_NAME, BACKUP_START_DATE, BACKUP_FINISH_DATE


  LIST

Copyright 1999-2025 Zeroboard / skin by ÃÖ±æÈ£(gilho.kr@gmail.com)
ÃÖ±Ù ´ñ±Û
ÃÖ±Ù °Ô½Ã¹°
06/27
[ORACLE]
The clock on host node2 differ....
by ÃÖ±æÈ£
06/26
[ORACLE]
PRCD-1229 ORA-29701: unable to....
by ÃÖ±æÈ£
06/17
[±âŸÁ¤º¸]
tmmd [COM0603]: Connection ref....
by ÃÖ±æÈ£