I have new doubt that is there any lock system in sql server means in a database there are 20 tables in that if i want lock a single table and remaining tables want to take backup is it possible .
SQL Backup works just fine with locks and transactions.
A Full Backup includes the transaction log at the end, so a restore will revert the database to a consistent transaction point.
1 Like
Is it minionware backup is a scheduled backup.
As I said:
Does that answer your question?
1 Like
Thank you
regards
Nagaraju
Good Morning
What is statistics in sql server.
why it is creating in sql server . is there any use with Statistics.
its crating like this
_WA_Sys_00000003_2F4FF79D, _WA_Sys_00000005_2F4FF79D, _WA_Sys_00000008_2F4FF79D, PK__Admin_mS__2B7477877CF981FA
You need to Google it and read up so that you understand it. It is a huge topic and not one that can be answered in a one-line answer in a forum like this.
Link to Microsoft description of Statistics
Overview:
SQL maintains statistics on the distribution of data in an index. It then uses those statistics to decide how well a particular index would perform for that query.
If you say
SELECT Col1, Col2, ..
FROM MyTable
WHERE Col3 = 'X'
then an index on [Col3] would be useful provided that the statistics indicate that the value "x" is rare. If 'x' is very common then it will be faster to use a table scan instead.
SQL also maintains statistics on columns etc., in addition to indexes that you have specifically created. These are named WASys_... etc.
You can ask SQL to tell you, based on its statistics and other data which it logs, what indexes you COULD create, but don't currently have, which will improve performance. Do NOT use that information BLINDLY, it is only a guide. Creating additional indexes will slow the performance of INSERT / UPDATE / DELETE so is only worthwhile where they improve the performance of JOIN / WHERE sufficiently to offset the extra "cost" during INSERT / UPDATE / DELETE.
Please start a new thread for a new question, like this one.
1 Like
Hi,
I actually wrote a automatic DB copy and restore script here, maybe that can help you. It does not require the use of ZIP, as I assume you already using the native DB compression. With a few steps to setup, you can have your dev environment refreshed on the schedule you wanted.
Hope this helps
1 Like
No need to zip or unzip backup with SQL Server 2008 R2.
Here's the examples for your referrence.
a. Backup one database with compression option to replace zipping bak file.
Backup database [test] to disk = 'd:\test.bak' with compression
b. Backup two databases with compression option to single bak file to replace zipping bak file.
Backup database [test1] to disk = 'd:\database_bak_combination.bak' with noinit,compression
Backup database [test2] to disk = 'd:\database_bak_combination.bak' with noinit,compression
Note: noinit means that the backup operation will append the existing backup file.
1 Like
Interesting idea 
I would never do it because of the risk that one corruption would mean that I could not restore any of the remaining DBs, but in saying that would that EVER actually happen in practice?
1 Like
yeah, agree with you.
Just for the reqirement of one single bak file, 
1 Like
I can't decide if my anxiety about "single file" is purely irrational ... I suppose on-the-day-that-it-happens I'd be glad of individual files, and I'm not sure its any more work (nor CPU/I/O) to support multiple files?
1 Like
Hi nagaraju,
I would suggest to turn on backup compression rather then using zip, as backup compression can be use in SQL 2008 R2 standard edition as well, which already make the backup file pretty small.
I just tested on one of my backup file, having 20Gb (11.5G used) database, the backup file is only 1.36Gb. I tried to zip it after that, and it only get down to 1.26Gb, I don't think it worth doing given you need to unzip if you ever need to restore it and all the overhead of zipping since it only gives you less then 10% of space saving.
If you really looking for compressing your backup, try some 3rd party software, I tried some of them and they can give you the little space back, but in return, it will be higher CPU and maybe a little bit longer overall duration. However it does integrated with SQL and it can perform backup and restore directly (without unzipping it first).
Hope this helps.
Use the below VB.Net Code and create exe file and use windows sheduler to take the backup, after that you can zip.
Imports System.Data.SqlClient
Public Class Form1
Dim con, con1 As SqlConnection
Dim cmd As SqlCommand
Dim dread As SqlDataReader
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
connection()
blank("backup")
End Sub
Sub blank(ByVal str As String)
Dim Lsm_Date As String
Lsm_Date = Convert.ToDateTime(Today).ToString("dd-MMM-yyyy")
If str = "backup" Then
Timer1.Enabled = True
ProgressBar1.Visible = True
Dim s As String
s = "D:\Autobackup\" & Lsm_Date & ".bak"
query("backup database BBNAME to disk='" & s & "'")
End If
End Sub
Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
If ProgressBar1.Value = 100 Then
Timer1.Enabled = False
ProgressBar1.Visible = False
)
End
Else
ProgressBar1.Value = ProgressBar1.Value + 5
End If
End Sub
Sub query(ByVal que As String)
On Error Resume Next
cmd = New SqlCommand(que, con)
cmd.ExecuteNonQuery()
End Sub
Sub connection()
con = New SqlConnection("Data Source=SERVERNAME;Initial Catalog=Master;User id=sa;Password=PASSWORD")
con.Open()
cmd = New SqlCommand("select * from sysdatabases", con)
dread = cmd.ExecuteReader
While dread.Read
End While
dread.Close()
End Sub
End Class