+ Reply to Thread
Results 1 to 7 of 7

Thread: Backing Up a MSSQL Database?

  1. #1
    PCBG Moderator Pollynator's Avatar
    Join Date
    2005 Apr
    Location
    this.Location.City = "Durban";
    Posts
    5,610

    Icon1 Backing Up a MSSQL Database?

    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?



    DOOM˛ ||CPU: Core i7 920 @ 3.2Ghz - Cogage Arrow Cooler || MOBO: Asus P6T Delux v2 || RAM: 3 x 2Gb Corsair XMS3 DDR3 1600Mhz CL8 - Corsair RAM Cooler || SSD: 1 x 60Gb G.Skill Phoenix Pro
    || GFX: Force3D HD5970 || PSU: Coolermaster SilentPrower 850Watt || Case: Coolermaster HAF922


  2. #2

    Default Re: Backing Up a MSSQL Database?

    Code:
    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
    Last edited by Azrael; 2007-07-12 at 11:50 PM.
    --------------------------------------------

    When fighting monsters, one must see to it that he does not become a monster himself, for when you gaze long into the abyss, the abyss also gazes into you...

  3. #3

    Default Re: Backing Up a MSSQL Database?

    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).
    --------------------------------------------

    When fighting monsters, one must see to it that he does not become a monster himself, for when you gaze long into the abyss, the abyss also gazes into you...

  4. #4
    VIP Member Wolvebain's Avatar
    Join Date
    2005 Jan
    Posts
    3,894

    Default Re: Backing Up a MSSQL Database?

    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...
    Code:
    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
    Last edited by Wolvebain; 2008-09-27 at 03:20 AM.
    ▫Case: ThermalTake Armor VA8000BWS || ▫PSU: Silverstone Zeus 650W ST65ZF || ▫Mobo: Intel BadAxe - D975XBX2 || ▫CPU: Intel Core 2 Quad Q6600 [Zalman CNPS-9700 LED] || ▫Mem: 2 x Kingmax DDR2-SDRAM PC6400 - 2GByte || ▫GFX: Inno 3D 9800GTX+ DHT

  5. #5
    PCBG Moderator Pollynator's Avatar
    Join Date
    2005 Apr
    Location
    this.Location.City = "Durban";
    Posts
    5,610

    Default Re: Backing Up a MSSQL Database?

    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.
    Last edited by Pollynator; 2007-07-13 at 12:28 PM.



    DOOM˛ ||CPU: Core i7 920 @ 3.2Ghz - Cogage Arrow Cooler || MOBO: Asus P6T Delux v2 || RAM: 3 x 2Gb Corsair XMS3 DDR3 1600Mhz CL8 - Corsair RAM Cooler || SSD: 1 x 60Gb G.Skill Phoenix Pro
    || GFX: Force3D HD5970 || PSU: Coolermaster SilentPrower 850Watt || Case: Coolermaster HAF922


  6. #6
    VIP Member Wolvebain's Avatar
    Join Date
    2005 Jan
    Posts
    3,894

    Default Re: Backing Up a MSSQL Database?

    Quote Originally Posted by Pollynator View Post
    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:

    Code:
    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
    Code:
    BackUp Database Northwind to Disk = 'C:\del\MsSQL_BkUpz\nortwind_13Jul07.bak'
    Good luck bud...
    ▫Case: ThermalTake Armor VA8000BWS || ▫PSU: Silverstone Zeus 650W ST65ZF || ▫Mobo: Intel BadAxe - D975XBX2 || ▫CPU: Intel Core 2 Quad Q6600 [Zalman CNPS-9700 LED] || ▫Mem: 2 x Kingmax DDR2-SDRAM PC6400 - 2GByte || ▫GFX: Inno 3D 9800GTX+ DHT

  7. #7
    PCBG Moderator Pollynator's Avatar
    Join Date
    2005 Apr
    Location
    this.Location.City = "Durban";
    Posts
    5,610

    Default Re: Backing Up a MSSQL Database?

    Thanks dude, i will try this.



    DOOM˛ ||CPU: Core i7 920 @ 3.2Ghz - Cogage Arrow Cooler || MOBO: Asus P6T Delux v2 || RAM: 3 x 2Gb Corsair XMS3 DDR3 1600Mhz CL8 - Corsair RAM Cooler || SSD: 1 x 60Gb G.Skill Phoenix Pro
    || GFX: Force3D HD5970 || PSU: Coolermaster SilentPrower 850Watt || Case: Coolermaster HAF922


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

     

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts