SQL Server - Take Database Backup - SQL Server 2005 (.bak file)

Asked By Mitesh Darji
18-Nov-10 09:27 AM
Hi All,

I want to take .bak file (backup) through SQL Script and for that I have created one
Store Procedure and In this i have used this Script

BACKUP DATABASE @AllDatabaseName TO DISK = @fileName

In my sql server there is 10 Database but taking only 8 backups only, it's ignore other 2 database backup

My Store Procedure in Master database so there is no any issue with Rights

Any Idea?

Regards
Mitesh Darji
  sarvesh singh replied to Mitesh Darji
18-Nov-10 09:48 AM
Please post the stored procedure you are using.
  Mitesh Darji replied to sarvesh singh
18-Nov-10 09:52 AM
In the store only one line which is I have provided in my last post

BACKUP DATABASE @AllDatabaseName TO DISK = @fileName

@AllDatabaseName = "DBName"
@fileName = "c:\temp\DBName.bak"

Regards
Mitesh Darji
  sarvesh singh replied to Mitesh Darji
18-Nov-10 10:06 AM
1. is there enough space in that location ?
2. How are you running this stored procedure, manually or through SQL job?  Do you see any error in SQL job output or SSMS if running manually?
3. Can you backup just those two databases on it's own?
  Mitesh Darji replied to sarvesh singh
18-Nov-10 10:21 AM
1. There is no issue with disk space, Enough space available in my disk
2. through vb.net code I am running this SP, and suppose I am running from SQL server manually then it's working perfectly
3. I am able to backup manually but not through program


  Anoop S replied to Mitesh Darji
18-Nov-10 11:50 AM

refer this

Dim oDevice As New SQLDMO.BackupDevice
Dim BACKUP As New SQLDMO.BACKUP
Dim SERVER As New SQLServer
 
Private Sub Form_Load()
On Error Resume Next 'If the device already exists an error will result if you try to add it again so just resume next cos its already there
 
With oDevice
  .Type = SQLDMODevice_DiskDump
  .Name = "NorthwindBakUp"
  .PhysicalLocation = "C:\Documents and Settings\Administrator\Desktop\BACKUP.bak"
End With
 
SERVER.Connect "Sanjib", "sa"
SERVER.BackupDevices.Add oDevice
BACKUP.Action = SQLDMOBackup_Database
BACKUP.Database = "Northwind"
BACKUP.Devices ="NorthwindBakUp"
BACKUP.BackupSetDescription = "Full BackUp"
BACKUP.BackupSetName = "By Sanjib"
 
BACKUP.SQLBackup SERVER
 
End Sub


http://social.msdn.microsoft.com/Forums/en/sqlsmoanddmo/thread/1bef2612-6820-4ddd-bb64-087d0bc9d93d

Create New Account
help
replication sql 2000 - -> sql 2005 SQL Server , sql, 2005" / > Is it possible to replicate a db from sql server 2000 to sql server 2005? When I set the publications, I look this "select
SSIS for SQL Server 2005? SQL Server How do I download SSIS for SQL Server 2005? SQL Server Programming Discussions SQL Server 2005 (1) Distributed (1) Imp (1) Exp (1) E5C6F1F60688
DTS in SQL Server 2005 SQL Server With the SQL server 2005, how can I invoke DTS? Should I install 'SQL Server Business Intelligence Development Studio'? If
Where is my SQL Server 2005 ? SQL Server I installed SQL Server 2005 on my machine, but when I go to the SQL Server Management Studio and connect
Cannot open server 2000 file in SQL SERVER 2005 SQL Server I installed SQL SERVER 2005 replacing SQL SERVER 2000 and now I cannot open the 2000 database file. Do