none
database backup without data

    Question

  • is there a way to back up a database without backing up the data (table, view, procs)

    also back up the jobs on a server/linked servers.

     

    is the only option to script them all? I dont need the data just the items like I mentioned about

    Friday, November 12, 2010 4:40 PM

Answers

All replies

  • Hello,

    If I understand you right, you want to "backup" only the database schema as CREATE (DDL) statements without the data?

    In SSMS right-mouse click on the database => Tasks => "Generate scripts" and then follow the wizard, there are several option for what object types you want to create scripts.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Friday, November 12, 2010 5:26 PM
  • In addition to what Olaf already suggested, you can use following step to backup Jobs:

    1. Open SSMS

    2. Expand SQL Server Agent

    3. Select Jobs ==> all jobs will be listed in Object Explorer

    4. Select All Jobs

    5. Right Click => Script Job As => Create To => Location where you would like in available options

    For Linked Server:

    1. Expand "Server Objects" in SSMS

    2. Select "Linked Servers" ==> All Linked Servers will be listed in "Object Explorer"

    3. Selet All Linked Servers

    4. Right Click => Script Linked Server As ==>  select Location where you would like in available options

     


    Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.
    • Proposed as answer by Sandesh Segu Saturday, November 13, 2010 10:00 AM
    Saturday, November 13, 2010 6:32 AM
  • In addition to what Olaf already suggested, you can use following step to backup Jobs:

    1. Open SSMS

    2. Expand SQL Server Agent

    3. Select Jobs ==> all jobs will be listed in Object Explorer

    4. Select All Jobs

    5. Right Click => Script Job As => Create To => Location where you would like in available options

    For Linked Server:

    1. Expand "Server Objects" in SSMS

    2. Select "Linked Servers" ==> All Linked Servers will be listed in "Object Explorer"

    3. Selet All Linked Servers

    4. Right Click => Script Linked Server As ==>  select Location where you would like in available options

     


    Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.


    I can only select 1 job at a time, since there are over 100 jobs right clicking each one is not efficient - i am unable to select all jobs, only select 1 at a time.

    Is there a way to select all jobs?

    Monday, November 15, 2010 4:20 PM
  • use "Object Explorer Details" which is F7 in SSMS
    Richard Douglas
    • Marked as answer by jameslester78 Tuesday, November 16, 2010 9:05 AM
    Monday, November 15, 2010 4:27 PM
  • As Richard suggested, Object Explorer windows will allow you to select all
    Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.
    Monday, November 15, 2010 7:18 PM
  • is there a way to back up a database without backing up the data (table, view, procs)

    also back up the jobs on a server/linked servers.

     is the only option to script them all? I dont need the data just the items like I mentioned about



    http://support.microsoft.com/kb/914288 
    (How to generate a script of the necessary database metadata to create a statistics-only database in SQL Server 2005 and in SQL Server 2008)
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, November 15, 2010 7:29 PM
  • use "Object Explorer Details" which is F7 in SSMS
    Richard Douglas

    Thats it thanks :)
    Tuesday, November 16, 2010 9:05 AM