SQLServer Summary

sp_dboption
sp_help titles
sp_helptext
sp_spaceused mydb2
sp_spaceused billable_trade

sp_linkedservers
sp_addumpdevice ‘disk’, ‘mydb_dump’, ‘c:\temp\my_dump’

sp_attatch_db ‘pubs’, ‘c:\data\pubs.mdf’, ‘c:\data\pubs_log.ldf’

sp_attachsingle_file_db ‘pubs’ , ‘c:\data\pubs.mdf’

sp_detatch_db ‘pubs’
dbcc opentrans

 alter database pubs set recovery full
 alter database pubs set single_user with rollback immediate( =  sp_dboption pubs,’single user’, true )

alter database pubs set auto_shrink

 

alter database pubs set recovery simple

> * Change database name
> alter database oldDB set single_user with rollback immediate go
> sp_renamedb ‘oldDB’ , ‘newDB’
> go
> alter database newDB set multi_user with rollback immediate
>
>
> * Show log file size and usage
> dbcc sqlperf (logspace)
>
>
restore database mydb from mydb_device with file=2

restore database mydb from mydb_device move ‘c:\myfile’ to ‘d:\myfile’
move log files to ‘d:\mylog’

 

 ==> When log file is full and you have to empty without backup. This command will truncate log.

backup log pubs with no_log

==> This NO_TRUNCATE option can be used even though sqlserver is not in normal status and this command will not delete log even after the backup.

backup log mydb to mydb_dump with no_truncate