.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Best Practices for SQL Backups - Snapshots vs Replay Manager vs SQL Maintenance Plans vs Backup Exec

Posted By:      Posted Date: October 16, 2010    Points: 0   Category :Sql Server
Hi, can anyone give me some information on the best practice for backing up sql backups?  For example, is it better to use snapshots vs SQL Maintenance plans, etc.  Any info is greatly appreciated.

Thank You!

View Complete Post

More Related Resource Links

Do cleanup maintenance plans that are linked to a backup job work for other backup jobs as well?



I have a job that backs up transaction logs and then there is another job within that plan that cleans up .trn files that are older than 48 hours. This job points to the directory where the transaction logs are backed up to. I just created a seperate maintenance plan to backup transaction logs for a different database. Will the cleanup job for the first backup job also clean up the logs from the second plan or do I need to create another job to cleanup the logs from the second backup job. My confusion is that the first backup job has specific databases that it is backing up and I'm wondering if that limits what the cleanup job does as well.

Cannot save maintenance plans

I am running SQL 2005 standard SP2 and I cannot save maintenance plans. Using the wizard I create the plan but it always fails with the following error messages:   An exception occurred while executing a Transact_SQL statement or batch   Invalid column name 'from_msx'. Invalid column name 'has_targets'. (Microsoft SQL Server , Error: 207)   Invalid column name 'msx_job_id'. (Microsoft SQL Server, Error: 207)   Thanks   Todd

Unable to modify Maintenance Plans SQL 2008

Been having a problem for a while, in that we are unable to modify any maintenance plans in SQL Server Management Studio. The server is running SLQ Server 2008 Standard on Windows 2003 Standard SP2

Can't save Maintenance Plans (10.50.1600 / 2008 R2 x64)

I run SQL Server 10.50.1600 / 2008 R2 x64 Enterprise. I am unable to create a maintenance plan via Management Studio. If I just try to create a new maintenance plan, then I get the following problem: "An OLE DB error 0x80004005 (Login timeout expired) occurred while enumerating packages. A SQL statement was issued and failed."

Error with Maintenance Plans - SQL 2005

Hi everyone.I have a problem with SQL 2005 with Cluster.  There is no issues with installation process and the cluster works fine. Normally I install SQL and apply Service Pack. So I do this, installing Service Pack 3 in both nodes (two nodes)After this, I create some databaese and restore backup from other SQL 2005 Server and also I import the logins to access SQLBut when I try access Maintenance Palns appears the following messageFailed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)Additional Information:    an exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo) Invalid column name 'from_msx'Invalid column name 'has_targets') Microsoft Sql Server, Error:207)What I do. Remove client components, Integration and Notification Services in both nodes, reinstall and apply Service Pack. Now I can create a new plan. but in the saving step appears the following messageSaving maitenance plan failed.Additional information:Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo) Invalid colum name 'ms_job_id' (Microsoft Sql Server, Error:207)I found some information in Internet, about a script that fixes this, but in Service Pack 2. I thinking in remove SQL , reinstall and test without SP. And

Exporting SQL Maintenance Plans

I have taken a look at the new maintenance plan designer and have come up with a maintenance plan I would like to be able to deploy on an enterprise level.  Anyone know of a way I can export the plan to another SQL 2005 server - without directly pulling from the msdb system tables?  I like the fact that you can script the output of most of the wizards, but the maintenance plan wizard does not appear to be one of them.Thanks,Matt

Backup tools fro granular restore - Symantec BAckup Exec Problem

Dear all, we are facing a problem here. Our sys admin set up everything to run the farm backups. He is using Symanteck Backup Exec 2010. In that farm we have 3 web applications which represents 3 content databases on a separate SQL back-end machine. When the backup is done, the report tells the backup succeed. It happens that when we try to recover from the backups, we can recover from an item level from 2 of those web applications, but not from 1. It created the whole site backup, but we cannot dig in to recover an item or a subsite. Did anyone had this problem before? (I gave farm admin levels to the account used by Backup Exec to double check the rights. That user could log on to the site and indeed he could go to all subsites and all item levels. So it is not access I believe). Any other expiriences with this tool? What about other tools? We are having a demo of AvePoint soon. Does anyone worked with that before? Thanks in advance for any help. Regards, GoncaloGonçalo

Maintenance Plan Backup Failing Time to time

See ERRORO Below: I am using SQL Server 2008 64 bit enterprise edition and backing up on network drive. This differential backup run every weekdays and up to sat, it works just fine and sometimes fails every other week. Need some advice. I use a domain service account on SQL Server agent. Executed as user: DOMAIN\SERVER_ag_def. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:45:04 AM  Progress: 2010-09-11 00:45:34.09     Source: {8006A2EB-FD95-45C3-8426-554DAE1895B4}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Progress: 2010-09-11 00:58:12.09     Source: Back Up Database (Differential)      Executing query "BACKUP DATABASE [DataCollectorDW] TO  DISK = N'\\w...".: 50% complete  End Progress  Progress: 2010-09-11 01:01:17.26     Source: Back Up Database (Differential)      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress  Progress: 2010-09-11 01:01:22.22     Source: Mai

how can i export/import maintenance plans from 2005 to 2008?

I have this link http://sqlservernewbie.blogspot.com/2008/10/how-to-importexport-sql-server.html However, it is not the solution for me. When I expand SERVERNAME (Integration Services), all I can see it is Running packages and Stored packages, and I cannot expand any deeper. And the scripting way which described in the link above, seems is not finished. It just tells you about the select...JulieShop

SQL Server Analysis Services Backup error "Errors in the metadata manager. Either the database with


I get the following error when this perl script tries to backup the database using ASCMD.exe 2008 command line tool

<return xmlns="urn:schemas-microsoft-com:xml-analysis"><root xmlns="urn:schemas-
microsoft-com:xml-analysis:empty"><Exception xmlns="urn:schemas-microsoft-com:xm
l-analysis:exception" /><Messages xmlns="urn:schemas-microsoft-com:xml-analysis:
exception"><Error ErrorCode="3239313412" Description="Errors in the metadata man
ager. Either the database with the ID of &#39;xyz_old&#39; does not exi
st in the server with the ID of &#39;ABCD\Sql_AS&#39;, or the user doe
s not have permissions to access the object."
Source="Microsoft SQL Server 2008
Analysis Services" HelpFile="" /></Messages></root></return>

I have tried using different account for impersonation but everything fails.

Any help would be really appreciated.


SQL SERVER 2008 Create Maintenance Plans encounter General Access Denied error


After I clicked New Maintenance Plans and filled in <Maintenance Plans Name>, an error appeared like below: General access denied error, my account is already sysadmin and public, and agent service has started.


TITLE: Microsoft SQL Server Management Studio


General access denied error


General access denied error



Maintenance Plan - Full Backup


Hey all

I am running a maintenance plan (Sql Server 2008 Enterprise) every night at mid-night. All the plan entails is doing a full backup of one database (about a 3GB database). We are getting timeouts on our site at exactly that point when the database is being backed up and they last up to 20min. I was under the impression that doing backups this way does not take the db down? Or am I wrong about this?

Has anyone else experienced this and if so what steps did you take to rectify it? Sorry if I have not provided other information, just let me know and I will give it to you.


SQL Server 2005 Maintenance Plans - Reporting And Logging Does Not Work


I have a maintenance plan built on a SQL Server 2005 instance that rebuilds indexes, rebuilds statistics, checks for database integrity, performs a maintenance cleanup, and performs a history cleanup.  This job runs once per week.

In the "Reporting and Logging" section I have the following options:

- Generate a text file report is checked

- Create a new file is selected

- A valid destination folder is identified

- Send report to an email recipient is checked

- A valid agent operator is selected

- Log extended information is checked

The job seems to complete successfully and a report is created and emailed, but the only information in the report is as follows:

Quest SLSSQLMaint Utility.
Logging on to SQL Server 'DEVSQL01' with Windows Authentication
Logged on to SQL Server 'DEVSQL01'

[1] Delete old text report files...
[1] Execution Time: 0 hrs, 0 mins, 0 secs.

[2] Send DBMail or SendMail report file....

[2] Execution Time: 0 hrs, 0 mins, 1 secs.

End of maintenance plan 'DB Maintenance Plan (No Backups) [DB Maintenance]' on 9/29/2010 8:40:49 PM (Success)
C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Binn\slssqlmaint.exe Process Exit Code: 0 (Success)

Where is the information about the rest of the maintenance plan?&nbs

Backup Exec 2010 -MOSS 07 GRT Restore troubles

I was just curious if anyone else had trouble restoring documents from Backup Exec 2010 SP1 ?  I'm at a client and whenever they try to restore a document library to their portal, only the folder is restored and none of the contents. Re-directed restores to file shares work fine.  Symantec support says to upgrade to R2 to fix it, but that this is not a 'known issue'.  Funny, because they didn't troubleshoot long and had a quick fix ready to manually re-link the document to the site!!  Seems a little shady. I'm just wondering if this client has a unique issue or if anyone else out there has experienced this problem as well?  I'd like to warn our other clients with the MOSS 2007/ BUE 2010 combo if this is common. 

cannot see the maintenance plans menu on the management menu



i've read a few related themes, but i cannot find the one, i hope you can answer my question.

I just cannot see the title to create a maintenance plan. i've read you create one following this steps:

3. In SQL Server Management Studio, expand the Management node, rightclick Maintenance Plans, and then select Maintenance Plan Wizard.
4. On the Maintenance Plan Wizard welcome page, click Next.
5. On the Select Plan Properties page, type a name for the maintenance plan, click Next.

the problem is that i don't have de Maintenance Plans title. My menu node expands like this:

    ->SQL Server Logs

i need to create a new maintenance plan, but only appear the old m. plan.

thks. Diana


backup Config Manager


The SA is trying to backup the config manager under the maintenance plan.  However, she is getting an error saying it cannot find the path under that backup file.  I was wondering if she even needs to backup the config manager since it is part of the management studio application.  Does anyone know if it needs to be backed up or not?

Thank you for your help.

Maintenance Plan with Multiple Sub Plans with Multiple Schedules


I have a Maintenance Plan that have various other subplans. Each subplan has its own schedule. For instance, one will be an hourly log backup, a daily complete backup and a weekly maintenance subplan. Now when ever the log backup takes place, say subplan1 succeeds HOWEVER, the other Subplans subplan 2 and subplan 3 show errors on the error logs. I know that these messages dont matter, however the GLARING X's marks on the Maintenance Plan History does not do service. This way i will have to go in and check each history. Definitely not as simple as looking at Tick marks on the work well done.

IS there a solution. (other than creating a separate maintenance plan for each subplan)!!!

PLease HELP. Thanks. 

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend