Backing Up a MSSQL Database?

[Pollynator]

hi all.

Remember the website i did PuzzleWorx™.

The database is in MSSQL
Well i wanted to backup the database that is on the server but i don't have access to the management tools only the client so i need to know...

What are the SQL commands to do a full backup of the Database?

[Azrael]

USE master
GO
 --
-- add a device and link it to a file...
--
EXEC sp_addumpdevice 'disk', 'my_db_bakup', 'c:\dbbackup\my_db.bak'

--
-- Back up my db...
--
BACKUP DATABASE my_database TO my_db_bakup

[Azrael]

You may also find this KB article How to back up a Microsoft Data Engine database by using Transact-SQL useful if you want to set up a scheduled job for that (using SQL Server Agent).

[Wolvebain]

Good stuff @ Azrael!

It`z great to see another MsSQL Mage amongst the sea of MySQL Guru`z around...

If it wasn`t for this recent post, I would have never even knew about the question [I think I should get my lazy ass to subscribe to this section ]

There are two other wayz of doing back-upz as well Polly...

1. If Ms Access is installed on the same machine as the MsSQL Srvr, you could create a Ms Access Project [an *.adp], and then use the Database tools to do a back-up... simple, fast, and very nifty if you using MSDE on a Dev. Box [see below for screen-shotz of MsAccess2k3 & 2k7]
   
   
   
2. Use VBScipt with SQL-DMO [or SMO in MsSQL Srvr 2k5] objects, to create a back-up [see below code; just change the variables for your environment]... Also rel. simple, fast, and extremely nifty if you using MSDE as the Main / Live Db Server and need to do scheduled back-upz... and, cos it usingz VBScripting, you can make it do whatever else you want; for example: send an email notification out of the back-up [useful on a fail event], check disk-space of the location you wanna back-up to and either zip the oldest back-upz or choose a different location, etc...

Dim oBackup
Dim oServer
Dim sSqlServer
Dim sBackupSetName
DIM sDatabase
DIM sBackupSetDescription
DIM sPath
DIM sFile

'###### [start] Change variables for your environment [start] ###### 
  sSqlServer = "DIRAC"    'Sets the Name of the MsSQL Srvr
  sDatabase = "Northwind"    'Sets the Name of the Db to be backed up
  sBackupSetName =  sDatabase & "_BkUp"    'Sets the Name of the BackupSet
  sFile = sDatabase & "_" & sDateTimeText 'Sets the back-up file name to comprise of the Db Name & time of back-up
  sBackupSetDescription = sFile    'Sets the description of the BackupSet to comprise of the Db Name & time of back-up
  sPath =  "C:\del\"    'Sets the path of the back-up file
'####### [end] Change variables for your environment [end] #######
  
  Set oServer = CreateObject("SQLDmo.SqlServer")
  oServer.LoginSecure = True
  oServer.Connect sSqlServer
  Set oBackup = CreateObject("SQLDMO.Backup")

  oBackup.BackupSetName = sBackupSetName
  oBackup.Database = sDatabase
  oBackup.Action = "0"
  oBackup.BackupSetDescription = sBackupSetDescription
  oBackup.Files = sPath & sFile  & ".BAK" 
  oBackup.TruncateLog  = "0"
  oBackup.Initialize   = True
  oBackup.SQLBackup oServer
  oServer.disconnect

  set oServer = Nothing
  set oBackup = Nothing
  
Function sDateTimeText
Dim sFileName
  sFileName = now()
  sFileName = Replace(sFileName, "/", "")
  sFileName = Replace(sFileName, ":", "")
  sFileName = Replace(sFileName, " ", "_")
  sDateTimeText = sFileName
End Function

[Pollynator]

WOWIE that only took about 6 months to get a reply but thanks alot to both of u, i will try this as soon as i can.

OK guys, the problem is that the DB is on the university's server and i have no control over the server. I have to back the DB up from a locked down workstation in one the Comp Sci lans, i have no access to Management facilities so i need a SQL command to not only backup the table structurte but the contents aswell.

Basically its near impossible because the server is a single huge DB with my only access to it being a query analyzer.

[Wolvebain]

WOWIE that only took about 6 months to get a reply but thanks alot to both of u, i will try this as soon as i can.

OK guys, the problem is that the DB is on the university's server and i have no control over the server. I have to back the DB up from a locked down workstation in one the Comp Sci lans, i have no access to Management facilities so i need a SQL command to not only backup the table structurte but the contents aswell.

Basically its near impossible because the server is a single huge DB with my only access to it being a query analyzer.

No problem... the solution is not as `smart` or elegant as the VBScript... but itz a 1 liner of code that will do your back-up from SQL Query Anayzer:

BackUp Database <Db Name> to Disk = '<Back-up file path and name>'

Just replace the parameters that are in <> with that of your environment, and you are set... Also remember, that the <Back-up file path and name> parameter points to the location on the MsSQL Srvr, and not the workstation, so you have to know the valid path for it... and you have to logon to the MsSQL Srvr via SQL Query Anayzer with a user that has the rel. rightz to perform the back-up [sa will do just nicely ]...
Here is an example, that backz up the Northwind Db on the the Machine that is hosting the MsSQL Server, in location: C:\del\MsSQL_BackUpz

BackUp Database Northwind to Disk = 'C:\del\MsSQL_BkUpz\nortwind_13Jul07.bak'

Good luck bud...

[Pollynator]

Thanks dude, i will try this.