I want to take a database backup automatically and to zip the database automatically

Actually i know there is some possibilities but i don't know how to write a script to this .Am asking for the script .

If scripts existed for SSIS I expect someone would have pointed you in the right direction (or Google will find one for you). so I doubt that there is a solution via that route.

I am less sure about a script for precisely what you want. I think most people who want to "copy" a database to a remote server want to do it more often than once-a-week, so it is more usual to use Log Shipping or Replication [which copies the database, continuously, in real-time or near-real-time

For "less frequent" copying I think the most likely equivalent [to find a script for] would be Developers copying a Production Database to a Development server.

My way, copying from above, would be

Set up a backup task, using compression, that outputs to x:\path\MyDatabaseName_yymmdd_hhmm.BAK

Scripts for Backup are readily available, however it is not entirely straightforward. WiIl you use the same filename each time? (if so how will you know whether the file is New, or just the old one from last week?). If you include the Date/Time in the filename then you have to determine a means of copying the newly named file (RoboCopy "Mirror" mode will do this automatically) but you also have to figure out how to delete the file (after a week or two, or after the Restore has been completed on the Remote - in which case the Remote has to communicate back "Restore completed successfully").

You don't have to have this level of sophistication ... but ... if you don't a) how will you know that it has worked? and b) how will you get an alert if & when it fails for some reason? maybe it doesn't matter if it fails?

Suitable backup command would be:

-- If database in FULL Recovery Model ideally make a LOG backup first
DECLARE	@strOutputFilename	varchar(256)
SELECT	@strOutputFilename
		= 'x:\Path\'  -- '
		+ 'MyDatabaseName'
		+ '_'
		+ CONVERT(varchar(8), GetDate(), 112)	-- yyyymmdd
		+ '_'
		+ REPLACE(CONVERT(varchar(8), GetDate(), 108), ':', '')	-- hhmmss
		+ '.BAK'
BACKUP DATABASE MyDatabaseName
TO DISK = @strOutputFilename	-- e.g. "x:\Path\MyDatabaseName_yymmdd_hhmm.BAK"
WITH
	COPY_ONLY
	, COMPRESSION
--	, DESCRIPTION = 'text'
	, INIT	-- This will overwrite any existing file, rather than append
	, CHECKSUM
	, CONTINUE_AFTER_ERROR	-- But consider "STOP_ON_ERROR" instead
	, STATS = 10
--	, ENCRYPTION ... if you need that

Having said that, presumably you already have a backup of your databases? If not you need to!! In which case why don't you just copy the most recent full backup file, once a week, instead of making another one?

Use a RoboCopy task with "Mirror" parameter which copies the files to \RemoteServer\Path

You would need to consider what options you need in RoboCopy - personally I would want logging to help diagnose any problems, and you might want specific file types only, all sub folders, and so on.

The basic command is:

robocopy x:\SourcePath \\RemoteServer\Share\DestinationPath /mir 

What about sending you an alert (e.g. by Email) if the copy fails? e.g. disk-full on Remote Server, or network down, or DNS cannot resolve the RemoteServer name? (RoboCopy will re-try the copy so intermittent network problems should be fine)

Set up a task on Remote Server which detects a new file and restores it (assuming you need that option, maybe you just want to have the latest files on that server and not actually restore them?)

You haven't said what you want to do with the file on the target server??

Some sort of "purge" which deletes stale files on Source after a reasonable retention time. Using RoboCopy Mirror will cause the files to also be deleted from the Remote Server.

Do you need to keep the backup file after it is transferred? How long for? DO you need to know if the file has been "processed" successfully by the remote server?

1 Like

is there any possibility by using maintanance plan in sql server to take a database backup

Yes. Use the Maintenance Plan Wizard in SSMS.

BUT ... the maintenance plans are a lousy APP, they suffer from all sorts of fragility. e.g. if a database changed from FULL to SIMPLE Recovery Model, or is set to READ_ONLY / OFFLINE / etc.

That said, Maintenance Plan will give you a Scheduled Backup which will delete the backup file after X-days retention period and Email you if it goes wrong etc. PROVIDED that you answer all the questions correctly. No reason why you shouldn't, but there are an AWFUL LOT of questions and not understanding / realising what the implications are might lead you into trouble.

A couple of days ago @gbritton told me about an excellent SQL Housekeeping APP called "Minion Backup" (they also do other APPs including Reindex and CheckDB, and Enterprise solutions)
http://minionware.net/backup/

that might be a better solution for you. I haven't tried it, but from what I have read you should be able to just install it and it will start making backups of all your databases, simple as that (VERY IMPORTANT: Make sure you edit the install script with the PATH LOCATION where you want the backup files stored)

NOTE that you will need to enable xp_cmdshell, if not already (it is DISABLED by default, because there are security implications to consider)

Just installing it will give you FULL Backups daily on System database and on Saturday evening for User Databases with Differential Backups on other evenings in the week, and Log Backups on any database set to FULL Recovery Model every 30 minutes. You can change the schedule for any/all of those, but from the moment you install it that is what you will get until you change anything.

Just checking the DOCs I see that Minion Backup has an option to copy a backup file to another location, so that might be a way for you to get your Backup File to your remote server?

1 Like

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 :slight_smile: 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, :grinning:

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

https://msdn.microsoft.com/en-IN/library/ms188059.aspx

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