SQL Backup

You say you are using SQL Server 2005, so open SQL Server Management Studio (SSMS) and connect to your server in the dialog box just after the opening splash screen.

Next, in the Object Explorer panel to the left, drill in until you find your database. Right click on the database name and select Properties. Go to the Options tab and you should see an entry called Recovery Model. Now you can read lots of information about this but, essentially, you can choose between two options - Simple and Full. SQL Server writes everything it does to a log file before doing it - the Transaction Log. If your database is set to Simple you don't have to worry too much about backing this up: if set to Full you definitely must. I'm assuming here that someone else set this thing up for you, so you'd better leave it set the way it already is. We just need to know the setting.

Next, close that, select the name and right-click it again, and select Tasks and then Backup... (see Backup_1).  This will get you a dialog for backing up. It will already have your database name, recovery model, backup type (full), and a name for it. All you need to do here is go down to the Destination section, delete anything already there (you don't want to overwrite the last one!), and use the Add... button to add a new name and destination. Give it the extension .bak.

Now, before you press OK to make the backup, go up to the top of the dialog box, where you'll find a couple of buttons - Script and Help. Click on Script, and select the Script Action to New Query Window option. Now go ahead and click OK and your backup will happen.

After you get the Success! window, return to SSMS and you'll see a new window with some code:

BACKUP DATABASE [mai] 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.UBUNTU_2012\MSSQL\Backup\mai.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'mai-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Select allOpen in new window

This is the actual code that SSMS launched to dothe backup you just made! You can put this inside your VB code in future - changing the filename each time so you don't overwrite old backups - and make backups from your program.

Now, if your recovery mode was Full (not Simple), you will need to make a Transaction Log backup, so that SQL Server knows it can re-use the log file (and not keep on adding to it until it totally fills up your disk!).

Do everything as before, up to getting the Backup Dialog filled in. For Backup Type, which normally says "Full", drop it down and select Transaction Log (that won't appear  if you have a database in Simple mode) - see picture Backup_3. Give it a name, and this time go to the Options tab of the dialog. Here, check that "Truncate the Transaction Log is checked. Now get the script as before and run the backup.

BACKUP LOG [mai] 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.UBUNTU_2012\MSSQL\Backup\mai_tl.bak' 
WITH NOFORMAT, NOINIT,  
NAME = N'mai-Transaction Log  Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Select allOpen in new window

Again, you should be able to put that into your code.

hth

Mike
Backup-1.png
Backup-2.png
Backup-3.png

Done works