InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 13:20:36 GMT20http://www.sqlservercentral.com/Forums/Topic550669-357-1.aspxOK, before I ask if and how to do this please let me explain why I’m attempting to do this. We have our primary MS-SQL server (server sql01) at an externally hosted site (site A). We also have an internal MS-SQL server for internal operations onsite (sql02, site B).Currently some operational reporting is done on SQL01 but this is no longer viable due to an increase in transactions. As this reporting needs to be near, but not quite real time I am setting up log shipping between sql01 & sql02 so that we have a read-only version of database that is only slightly out of date.It would be useful if we could take backups of this standby database for Q&A of development changes. Is there a way to do this? I don’t mind if the standby database is unavailable for reporting during the time it takes to complete that backup. Alternatively I don’t mind using a 2nd standby database just for the purposes of creating this backup. I just need something that is going to be reliable, require no ongoing manual intervention. Otherwise I’ll just backup the primary database and retrieve that file.I have a feeling that is what I’m going to have to do and I’m just trying to be too tricky by far :DMon, 11 Aug 2008 17:47:45 GMTAlex Desshttp://www.sqlservercentral.com/Forums/Topic1305913-357-1.aspxI am relatively new to both SQL and Netbackup. I am having an issue with the backup of one of my SQL servers. This server is running Windows 2003, SQL 2005, and Netbackup 7.0.1. The problem is that I am backing up the Server, meaning all drives. It appears that the backup of file on one of the network drives is not be completed. There are several files that give the error "The process cannot access the fie because it is being used by another process". The files in questions are sql database files. The error logs are below. I am actually backing up the SQL database using Netbackup SQL scripts directly to tape, so I am wondering if I shpuld be concerned. Any assistance would be appreciated.5/23/2012 9:26:27 PM - Info bpbrm(pid=6728) from client Abcd1234: TRV - object not found for file system backup: I:5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\DES_20111209.ldf (WIN32 32: The process cannot access the file because it is being used by another process. )5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\DES_20111209.mdf (WIN32 32: The process cannot access the file because it is being used by another process. )5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\master.mdf (WIN32 32: The process cannot access the file because it is being used by another process. )5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\mastlog.ldf (WIN32 32: The process cannot access the file because it is being used by another process. )5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\model.mdf (WIN32 32: The process cannot access the file because it is being used by another process. )5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\modellog.ldf (WIN32 32: The process cannot access the file because it is being used by another process. )5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdbdata.mdf (WIN32 32: The process cannot access the file because it is being used by another process. )5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdblog.ldf (WIN32 32: The process cannot access the file because it is being used by another process. )5/23/2012 9:26:59 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\tempdb.mdf (WIN32 32: The process cannot access the file because it is being used by another process. )5/23/2012 9:26:59 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\templog.ldf (WIN32 32: The process cannot access the file because it is being used by another process. )5/23/2012 9:26:59 PM - Info bpbrm(pid=6728) from client Abcd1234: TRV - last message being suppressed after 10 occurrences 5/24/2012 5:45:11 AM - Info bpbrm(pid=6728) from client Abcd1234: TRV - object not found for file system backup: X:5/24/2012 5:45:24 AM - end writing; write time: 08:24:42the requested operation was partially successful(1)Thu, 24 May 2012 08:44:06 GMTNewBackupSyshttp://www.sqlservercentral.com/Forums/Topic1305797-357-1.aspxWe have a production d/b that we back up to tape every night. This process sometimes fails so I wanted my own backupsI've set up 4 SQL Agent jobs that are now running: (I've checked the logs)1. Backup my production d/b nightly2. Backup its transaction logs hourly3. Remove any .BAK file older than 3 days4. Remove any .TRN file older than 3 daysSo I'm seeing a .BAK file and a .TRN file being created every night at 2:00 am, and disappearing 3 days later. All is good there.But the .TRN file is only created once a day, with the nightly BAK file at 2AM according to the date modified in Windows Explorer. Are my log backups working? The job log says they run for 3 to 7 seconds each hour, but I don't see the date modified change on an hourly basis.I have the TRN log backup set to Append. Is that why I don't see the Date Modified change hourly?Also, the size of my TRN files fluctuates wildly from day to day:5/22: 473 Kb5/23: 5.7 Gb5/24: 32 MbIs this because my cleanup job is not actually deleting the transaction logs but just cleaning them out of older, unneeded data? Or should I be concerned about this?Thanks in advanceThu, 24 May 2012 07:01:33 GMTSigersonhttp://www.sqlservercentral.com/Forums/Topic1305129-357-1.aspxFollowing is the snapshot of differnential backups in one of our production servers. As you can see size of diff backup has gone to 100 MB and again it reached to 50 on 2 AM timestamp. As per the logic the size should not decrease unless there is a full backup. In this case we have only weekly full backup and can see same size difffernce daily. If any of you have seen similar issues or if you know the reason, please update me. Database is hosted on SQL2005 instance with SP1.05/21/2012 08:00 PM 50,417,152 Tracker_Data_backup_201205212000.Dif05/21/2012 10:00 PM 50,417,152 Tracker_Data_backup_201205212200.Dif05/22/2012 12:00 AM 100,832,768 Tracker_Data_backup_201205220000.Dif05/22/2012 02:00 AM 50,417,152 Tracker_Data_backup_201205220200.Dif05/22/2012 04:00 AM 50,417,152 Tracker_Data_backup_201205220400.Dif05/22/2012 06:00 AM 50,417,152 Tracker_Data_backup_201205220600.Dif05/22/2012 08:00 AM 50,417,152 Tracker_Data_backup_201205220800.DifWed, 23 May 2012 10:29:56 GMTGopi Krishnan-468302http://www.sqlservercentral.com/Forums/Topic1302125-357-1.aspxHi all,I am looking for a small script that would simply display the status of all database backups dbnamestartendstatus ( sucess or failed )servername*** the important info is an actual column mentioning 'SUCCESS or FAILED 'Thx for the help !Thu, 17 May 2012 14:03:31 GMTjohnnyrmtlhttp://www.sqlservercentral.com/Forums/Topic1302404-357-1.aspxI have enabled AWE and configured the max memory min memory etc yesterday.I gave the 'lock pages in memory' privilege to a Windows AD account. This account is used for both the SQL Server and SQL Server Agent services and also has the sysadmin role.The overnight maintenance plans ran fine except one. This is failing at the 'Alter' stage which is doing the index rebuild of the system databases. All the other jobs run fine.I would have expected them all to fail, but I am guessing the failing job is something to do with the privileges needed for the 'Alter Index' part.Various errors from error logs and maintenance plans;"Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process."Check Database integrity on Local server connectionDatabases: All system databasesInclude indexesTask start: 2012-05-18T09:50:15.Task end: 2012-05-18T09:50:15.Failed:(0) Alter failed for ServerThe package execution returned DTSER_FAILURE (1)Any Ideas?Fri, 18 May 2012 05:00:47 GMTthunderousityhttp://www.sqlservercentral.com/Forums/Topic415939-357-1.aspxWe have a PRD db on our SAP Production server and a PRD db on our SAP Production Disaster Recovery server.We employ log shipping to keep the DR box db up to date.Over the weekend, log shipping stopped working and the DR PRD db is stuck in "Loading\Read-Only\Suspect" mode and we cannot do anything with it.Is there a way to salvage this [u][b]without [/b][/u]trashing the DR PRD db, restoring over the top from a Production dump and then re-creating the log shipping process??Any help you guys could give would be great!Thanks,BenMon, 29 Oct 2007 05:29:59 GMTBen Curryhttp://www.sqlservercentral.com/Forums/Topic1297210-357-1.aspxHello I have to automate backup-restore from production environment to Day behind server.I schedule and it works fine for 2 monthshere is my sql statement [code="other"]declare @filenametbl1 varchar(60), @thepath1 nvarchar(150)set @filenametbl1 = (SELECT top 1 rtrim(ltrim(REVERSE(SUBSTRING(REVERSE(T2.physical_device_name),0,(charindex('\',REVERSE(T2.physical_device_name))))))) [filename] FROM RTIDBPROD.msdb.dbo.backupset T1 JOIN RTIDBPROD.msdb.dbo.backupmediafamily T2 ONT1.media_set_id = T2.media_set_idwhere T1.[type] = 'D' and T1.database_name = 'STARS'order by T1.backup_finish_date desc)set @thepath1 = '\\data-store-srv\rtibackups\'+@filenametbl1+''select @thepath1RESTORE DATABASE stars FROM DISK = @thepath1WITH MOVE 'Stars_Data' TO 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\STARS_Data.mdf',MOVE 'Stars_Log' TO 'C:\Program Files\Microsoft SQL Server\STARS_Log.ldf', REPLACE[/code]but right now. In Production databases, Log files used to be 60-100 MB before but right now it increases upto 14 GB.so I try that command above manually and I got an error like, there is insufficent space in C drive (There is 10.3 GB Free space and current log file is 60 MB) and the error log from SQL job is[code="plain"]Executed as user: PPSCENTRAL\mrcluster. Cannot open backup device '\\data-store-srv\rtibackups\'. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.). [SQLSTATE 42000] (Error 3201) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.[/code] so I am Thinking , If i shrink Log file from Production database and it come down till to 100 MB. then i took backup and restore on Day behind.is that solution of my problem or any other way i can do. please help me for this problemWed, 09 May 2012 09:39:40 GMTsurma.sqlhttp://www.sqlservercentral.com/Forums/Topic999654-357-1.aspxwhat is the alter tempdb command to move the tempd.mdf file from D:\MSSQL2005\MSSQL$Instance\MSSQL.1\MSSQL\Data to the H:\SQL directoryWed, 06 Oct 2010 11:10:14 GMTDanzzhttp://www.sqlservercentral.com/Forums/Topic1300556-357-1.aspxI have a very small window for server maintenance on 1 server. If for some reason I need to reboot a server while a t-log backup is running, how does SQL handle that when the server is back up ? Does it regard the log backup as not having occured, so the next backup will maintain the log chain ?Perhaps cancelling the job manually before rebooting would be a better option so SQL can rollback ?Tue, 15 May 2012 13:37:27 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic1299832-357-1.aspxHi, We have 14-15 databases that get transaction log backups every 1/2 hour. They are backed up to a local drive - not C:/, but the same drive the data files are on. Each database has its own backup folder. All's been well for 9+ months, but in the last week or two we're getting failures:Executing the query "BACKUP LOG [xxxx] TO DISK = N'F:\\MSSQL.1\\MSSQL\\Backup\\xxxx\\xxxx_backup_201205140830.trn' WITH NOFORMAT, NOINIT, NAME = N'xxxx_backup_20120514083001', SKIP, REWIND, NOUNLOAD, STATS = 10" failed with the following error: "Cannot open backup device 'F:\\MSSQL.1\\MSSQL\\Backup\\xxxx\\xxxx_backup_201205140830.trn'. Operating system error 5(Access is denied.).BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.When this happens, there'll be a zero byte file created in the directory. The backup 1/2 hour before and 1/2 hour after will go just fine. The specific database that fails is random - not always the same one(s). Some days none will fail, some days 3 or 4 will fail over the entire day. I've never seen two different databases fail at the same time, or the same one fail twice in a row. Not running especially tight on disk space. Server was rebooted for a third-party application update, still seeing failures. Been Googling but no concrete answers. Where should I look next?Thanks,PatMon, 14 May 2012 13:24:35 GMTmiapjphttp://www.sqlservercentral.com/Forums/Topic1295503-357-1.aspxI have a 3TB database. Understand that SQL can back it up, the problem is not only to back it up but more importantly to restore it. We do weekly full, daily diff, and log every 15min. Full is the challenge here. For full, we break the entire backup into 15 different files spreading them over to 4 separate local drives on RAID 0 64k block size. Still, it will take 48 hours +/-. Trying 3rd party tool, but much data in this database is BLOB and can't seem to get much compression rate out of it, and the worse part, the tool froze up, so we are back to native SQL backup. How do you backup yours? Thanks.Fri, 04 May 2012 17:24:37 GMTTrailRunnerhttp://www.sqlservercentral.com/Forums/Topic1298517-357-1.aspxCurrently we use Symantec Backup Exec to run a full Saturday SQL database backup across all of our servers followed by midweek differentials. If any new databases were created we would manually add a full backup run for individual databases. However, there has been an influx of development work and I really need to automate this process - as Symantec support stated BE could not process the operation of picking up full database backups during a diff. run.Is there any way to run a full backup script and midweek diffs, as well as pick up full backups of new databases which get diff runs for the remainder of the week?Fri, 11 May 2012 06:16:23 GMTrobertprice45http://www.sqlservercentral.com/Forums/Topic1298888-357-1.aspxMy understanding was that the transaction log will be cleared on a backup.I see that some transaction logs are cunsuming way too much of our available storage.Is my understanding incorrect?Is there a setting that I am missing?We do daily full backups.Thanks..Fri, 11 May 2012 13:04:45 GMTinevercheckthis2002http://www.sqlservercentral.com/Forums/Topic1120746-357-1.aspxHi,Mydb is about 2GB. When doing a backup everyday, following error is coming although the backup is done successfully. I use the dbcc checkdb and there is no errors.10 percent processed.20 percent processed.30 percent processed.40 percent processed.50 percent processed.60 percent processed.70 percent processed.80 percent processed.90 percent processed.Msg 3202, Level 16, State 2, Line 1Write on "\\inbackup01\FINAL2\FULL\mydb_backup_201106070921.bak" failed: 64(The specified network name is no longer available.)Msg 3271, Level 16, State 1, Line 1A nonrecoverable I/O error occurred on file "\\inbackup01\FINAL2\FULL\mydb_backup_201106070921.bak:" 64(The specified network name is no longer available.).Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.Mon, 06 Jun 2011 23:49:16 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic755762-357-1.aspxHi,I have a the following SQL code run daily from a .bat file to do daily backups..The .bat file has...Sqlcmd -S MyDB -U MASTER -P CONTROL -i C:\MyDB\MyDB.sqlThe MyDB.sql file has...BACKUP DATABASE MyDB TO DISK = "C:\MyDB\MyDB.bak" WITH INITIt works fine - but each time it overwrites the last backup - (i want to keep last 3 or 4 backups)An ideas how I can save the file name like yyyymmdd_MyDB.bakwhere yyyymmdd is the year-month-day of the backup so we can save copies each day.many thanksMikeMon, 20 Jul 2009 07:35:54 GMTmike-499273http://www.sqlservercentral.com/Forums/Topic1292707-357-1.aspxFor transaction log shipping, I get that a full backup needs to be created and then thereafter transaction log backups. Restore the full backup on the DR box and then have a collection of jobs to restore the transaction logs every so many minutes on the DR box.Question is: after initial full backup on prod box and all the transaction logs created on prod box, do I need to keep them or can they be deleted as each of them are restored on the DR box?Also, I've read that the DB on the DR box can't be used at all even to read... it just sits there doing nothing but restores as transaction logs are received? Trying to determine when to use mirroring vs. shipping since with mirroring, a snapshot can be created so can use the DR box for reporting, at least.Mon, 30 Apr 2012 12:13:02 GMTdixon_dwaynehttp://www.sqlservercentral.com/Forums/Topic1290289-357-1.aspxHello All,We are planning to implement backup's using veritas netbackup tool and for this I have created a sql account with basic dbreader and backup operator roles onto the required databases. I am assuming this should be good enough for the tool to connect and do the backup and restores.But I am not able to connect to the sql when I try from the netbackup tool. But when I update the permission to sys admin, I am able to connect. When I remove sysadmin and update with dbowner, I am not able to connect again.so am I missing any server level permissions for the sql account?Please help me on this to get this resolved asap.Wed, 25 Apr 2012 15:10:52 GMTstriker-babahttp://www.sqlservercentral.com/Forums/Topic1265882-357-1.aspxI was given a SQL Server 2008 "bak" file.... it would not restore to SQL Server 2005. I then tried to restore to SQL Server 2008 to see if it were from a SQL Server 2008 backup. It restored with no issues. Unfortunately, the restore needs to be on a SQL Server 2005 instance on our cluster. I tried to use an SSIS package, but it complained about the versions. I think tried Export/Import.... still received version errors. I then tried to generate a create script. Still have issues. Can a SQL Server 2008 backup be restored to a SQL Server 2005 instance? Thank you in advance for any information.CharlieTue, 13 Mar 2012 07:25:12 GMTrummingshttp://www.sqlservercentral.com/Forums/Topic1291980-357-1.aspxhii my query is i cannot connect to sql server 2005 it asks me the server name nd i dont know it when i go to browse dialog box there are no server name shown i want to connect sql server 2005 through windows authentication nd i dt knw wat is the instance name plzz helpSat, 28 Apr 2012 00:47:49 GMThp7786http://www.sqlservercentral.com/Forums/Topic1290342-357-1.aspxHi all,I need to create a backup that runs once a month and it should have a custom filename, such as backup01_31_2012.bak and backup02_28_2012.bak and so forth.I wanted to know how I can create file names dynamically? Thanks in advance.Wed, 25 Apr 2012 17:36:33 GMTshahgolshttp://www.sqlservercentral.com/Forums/Topic1283591-357-1.aspxI have created maintenance plan(s) to run automatically at a certain time. They have not run for quite sometime now. When I force them to run/execute, Am getting the following error message:[b]"Execute Maintenance Plan- Execute maintenance plan. Fixed Assets BackUp On NHCHQDB - Mon-Fri (Error)MessagesExecution failed. See the maintenance plan and SQL Server Agent job history logs for details.------------------------------ADDITIONAL INFORMATION:Job 'Fixed Assets BackUp On NHCHQDB - Mon-Fri.Subplan_1' failed. (SqlManagerUI)"[/b].But also whenever I tried to delete/remove it it failed, said LogIn failed for user "sa"Sat, 14 Apr 2012 03:08:19 GMTMringi.Isakihttp://www.sqlservercentral.com/Forums/Topic1286475-357-1.aspxHello - Everytime I try and restore a database that is over 40GB if get the following error message.Msg 3271, Level 16, State 1, Line 2A nonrecoverable I/O error occurred on file "???:" 170(The requested resource is in use.).Msg 3013, Level 16, State 1, Line 2RESTORE DATABASE is terminating abnormally.This is on SQL 2005 SP2 on a Windows 2003 Server Enterprise x64 SP2 VMCan someone please advise???Regards,DavidThu, 19 Apr 2012 09:01:05 GMTdavid.ostranderhttp://www.sqlservercentral.com/Forums/Topic1285570-357-1.aspxGenerally in Web Application, database backup and restoring the same database if required can be done manually for single client or organization. But in Single Instance Multi - tenant Architecture where some time number of database can grow more than 5,000 to 10,000 or more and taking backup manually is cumbersome. So how to manage automated backup and restoring of database if required, for such large number of small Databases.Wed, 18 Apr 2012 05:58:37 GMTgopalchettrihttp://www.sqlservercentral.com/Forums/Topic1276629-357-1.aspxHi guys,Any ideas where i can get a Template for Database restore strategy?i am planninng to do Restores every three months.And i am not good at doing documentation but i know what my intentions are.I do not have a DR site, and for some reason i am not comfortable with just a SQL job reporting that the Database backup run successfully last night. Please help fellows.So my intention is to restore backups to My Test environment and Also to the same production environment.Mon, 02 Apr 2012 07:46:35 GMTTHE-FHAhttp://www.sqlservercentral.com/Forums/Topic586881-357-1.aspxHello, Explain How to take backups in My SQL Serveri) Command promptii) MYSQL administratorI have taken two backups but it don’t know whether it is correct or wrong.I) I open Administrator Menu fig.1ii) I am Entering the Server Host is localhost port No.3306 fig.2iii) Enter passwordiv) MySQL administrator window will be displayed. My database was BWTExperss , but how to select and how to take backup that project database has been Explained fig.3 v) I push the BWTExpress database as same as in that fig.4 After clicking the Execute backupnow buttonvi) Then the Execute Backup file will be stored in .sql Exception Please Explain How to create database backup fileRegardsvijayamvijayam123@live.comThu, 16 Oct 2008 06:23:51 GMTvijayam123http://www.sqlservercentral.com/Forums/Topic1280913-357-1.aspxA backup job of one of my prod dbs failed last night. It has not been changed since August of last year. It runs at 10pm every day and backs up the db to a (SAN) mapped drive. Because of the db size I back it up to multiple backup devices. I run a virtually identical job (which calls the same usp that runs the backup) for a different db within a few minutes-- and it succeeded. So it should not be a matter of permissions; if one failed they both should have, if that were the case.Here is the feedback from the job step:[font="Courier New"]Msg 3201, Sev 16, State 1, Line 1 : Cannot open backup device 'U:\folder\dbname_20120409_3_5.bak'. Operating system error 5(Access is denied.). [SQLSTATE 42000]Msg 3013, Sev 16, State 1, Line 1 : BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]Msg 0, Sev 16, State 1, Line 95 : ---------------------- [SQLSTATE 01000]Msg 0, Sev 16, State 1, Line 96 : THE BACKUP COMMAND [SQLSTATE 01000]Msg 0, Sev 16, State 1, Line 97 : ---------------------- [SQLSTATE 01000]Msg 0, Sev 16, State 1, Line 98 : BACKUP DATABASE [dbname] TO DISK = N'U:\folder\dbname_20120409_1_5.bak', DISK = N'U:\folder\dbname_20120409_2_5.bak', DISK = N'U:\folder\dbname_20120409_3_5.bak', DISK = N'U:\folder\dbname_20120409_4_5.bak', DISK = N'U:\folder\dbname_20120409_5_5.bak' WITH NOFORMAT, INIT, SKIP, REWIND, NOUNLOAD, STATS = 5, NAME = N'dbname_backup' [SQLSTATE 01000][/font]...any ideas?Tue, 10 Apr 2012 09:54:03 GMTmstjeanhttp://www.sqlservercentral.com/Forums/Topic1282870-357-1.aspxHi friends,I made a video on sql server backups and clustering. Please feel free to look at the video and post your suggestions.We shall be pleased to implement them in our next video.http://www.youtube.com/watch?v=YlResX1pwng&feature=autoplay&list=UU-tD_VCu_DSyEhpEGSKIqlQ&playnext=1&noredirect=1regardsThu, 12 Apr 2012 20:33:43 GMTlifeatitsbest123http://www.sqlservercentral.com/Forums/Topic1282869-357-1.aspxHi friends,I made a video on sql server backups and clustering. Please feel free to look at the video and post your suggestions.We shall be pleased to implement them in our next video.regardsThu, 12 Apr 2012 20:33:04 GMTlifeatitsbest123http://www.sqlservercentral.com/Forums/Topic1280209-357-1.aspxFolksIs there any script someone has written which would take care of followingdatabase is in SIMPLE recoveryand is being backed up daily. If next day it gets changed to FUll, I should be able to find out from some tables and be able to run full backup followed by a tran log backup.I have the process to run log backups and it identifies if DB is in FULL or SIMPLE. I just dont know where to look for the change to the recovery model. Is there a table/view which stores such history. Simply knowing recovery model is not helpful here.. I have to know if the database showing "FULL" was in "SIMPLE" earlierMon, 09 Apr 2012 09:35:56 GMTchopperGuyhttp://www.sqlservercentral.com/Forums/Topic1278612-357-1.aspxI having error in Secondary Server LSCopy_websvr-kl1_irdkl1 job where want to copy the .trn from primary server.Below are the error message:-Message2012-04-05 15:30:15.37 Starting transaction log copy. Secondary ID: 'cf7f62fb-915e-4405-adce-588bc2778e1f'2012-04-05 15:30:15.39 Retrieving copy settings. Secondary ID: 'cf7f62fb-915e-4405-adce-588bc2778e1f'2012-04-05 15:30:15.42 Retrieved copy settings. Primary Server: 'WEBSVR-KL1', Primary Database: 'irdkl1', Backup Source Directory: '\\WEBSVR-KL1\TransacLogKL1 ', Backup Destination Directory: '\\WEBSVR-KL2\TransacLogKL1', Last Copied File: '<None>'2012-04-05 15:30:15.43 Copying log backup files. Primary Server: 'WEBSVR-KL1', Primary Database: 'irdkl1', Backup Source Directory: '\\WEBSVR-KL1\TransacLogKL1 ', Backup Destination Directory: '\\WEBSVR-KL2\TransacLogKL1'2012-04-05 15:30:15.46 *** Error: Access to the path '\\WEBSVR-KL1\TransacLogKL1' is denied.(mscorlib) ***Permission on folder i check no problem as same with other copy job.Any idea how to fix this?Thu, 05 Apr 2012 01:50:07 GMTjason07http://www.sqlservercentral.com/Forums/Topic1274905-357-1.aspxI have a corrupted backup which I need to restore. I do it using:RESTORE DATABASE Test FROM DISK='C:\DB\Test.bak' WITH REPLACE, CONTINUE_AFTER_ERROR And getting the error:Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.Msg 3624, Level 20, State 1, Line 1A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support. Looking at the error log I see a line above:Error: 17066, Severity: 16, State: 1.SQL Server Assertion: File: <bckioreq.cpp>, line=1375 Failed Assertion = '0'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.Thu, 29 Mar 2012 01:20:32 GMTWaitingWonder2http://www.sqlservercentral.com/Forums/Topic1277817-357-1.aspxHI Guys, As of now we are copying backup on Static IP but it was not supporting in disaster. We have a cluster environment where we are copying backup to network share regularly on Statis IP. When backup copying in progress unexpected failover happend due to that we are unnable to copy the backup successfuly. Now we are planning to copying process on Virtual IP. We have 1GBPS Line. i wants know Is there any impact, if i process backup operation on VIP. we are not using total line. Please suggest us.Wed, 04 Apr 2012 00:59:27 GMTSimha24http://www.sqlservercentral.com/Forums/Topic584539-357-1.aspxhi,i'm trying to copy a database made in sql server 2008 to a server with sql server 2005.i get an error: restore headeronly is terminating abnormally error: 3241.can the management tool do this type of action ?Sun, 12 Oct 2008 07:00:18 GMTAmitCBRhttp://www.sqlservercentral.com/Forums/Topic441601-357-1.aspxHi,My Transcaction log is increasing manifold to upto 20GB which is almost twice the size of whole DB. This is threatening to be a possible DISK SPACE CRUNCH scenario on my system in another few months.I wish to reclaim the precious disk space also without taking my DB offline. I tried with transaction log backup but while it does take the backup doesn't let me reclaim the disk space.I want to know what are my options w.r.t Disk Space Reclaim.Please help.AnkitFri, 11 Jan 2008 01:02:02 GMTAnkit Mathur-481681http://www.sqlservercentral.com/Forums/Topic1274043-357-1.aspxHi ,Developement team is complaining that they are facing poor query performance all of a sudden . I checked for blocking & long running queries ,but not able to find anything unusual. Full backup was running on the server and that was the only job running at that point. My question is whether full\log backup will cause performance issue?? As per my knowledge backup wont constitute any performance degradation.Please correct me if am wrong.TIATue, 27 Mar 2012 22:22:41 GMTRechana Rajanhttp://www.sqlservercentral.com/Forums/Topic1274094-357-1.aspxMy database Backup file size 20 GB. i Restore this database another server that time MDF file will take 135 GB. i already tried shrink the database.but no use. give me a solution. I am using Sqlserver 2005 enterprice edition.Wed, 28 Mar 2012 01:23:19 GMTvs.satheeshhttp://www.sqlservercentral.com/Forums/Topic1273027-357-1.aspxA few years ago we had a student doing our system maintenance. When it came to setting up SQL Server and it's backups, he did it. He left and it fell upon me to do this. I know I've asked on these forums before for guidance on how to simplify things, and how to make it more frequent. (The former DBA has just 1 full backup a week, and 1 daily incremental backup. I don't think that's enough.) However, I'm not entirely sure why he's done what he's done. I think some of it might be good to retain, and others removed. Before I begin, I'd like to clear up something for myself. If a SQL Job starts at some step within a subplan, it doesn't automatically proceed to the next subplan, correct?OK, here's what the student DBA has. He's got 6 subplans, some with multiple steps/tasks. Here they are:[li] First subplan [b]daily_check_db[/b] does a daily database integrity check of all databases [/li][li] Second subplan [b]weekly_rebuild_update_shrink[/b] has 4 tasks/steps[li] Rebuilds index on all databases [/li][li] Updates statistics on all databases [/li][li] Cleans up report (*.txt) files [/li][li] History Cleanup [/li][/li][li] Third subplan [b]weekly_full_backup[/b] has 2 tasks/steps[li] Full backup of all user databases [/li][li] Backup of transactlon logs for all user databases [/li][/li][li] Fourth subplan [b]daily_incremental_backup[/b] has 2 steps/tasks[li] Differential of all user databases [/li][li] Backup of all user databases trasnaction logs [/li][/li][li] Fifth subplan [b]weekly_incremental_backup[/b] has 2 steps/tasks[li]Incremental of all user databases [/li][li]Transaction log backup of all user databases [/li][/li][li] Sixth subplan [b]weekly_full_system_backup[/b] has 2 steps/tasks[li] Full backup of master, model and msdb[/li][li] Transaction log backup of ([i]he didn't specify what databases to backup[i])[/li][/li](Well, I guess the IFCode doesn't allow for lists within lists. I'm sorry about that.)Anyway, I'm a sort of junior DBA, but this seems overly complex to me. Are there elements of what he's got here that I should duplicate when I redo it, or not?Mon, 26 Mar 2012 15:04:14 GMTRod at workhttp://www.sqlservercentral.com/Forums/Topic1271831-357-1.aspxHi guys, LONG time lurker. This is my first post as I could not find any help with this. I am trying to create a SQL Job that will grab the latest full backup from a server and restore it using Litespeed. I have everything down except that backup file name changes with the date (ex. FullBackup_03232012.bak). I know there is a way I can write my script to grab the latest backup of a particular database. Anybody got a clue? Sorry If there is already a similar thread on this, I could not find it. Thanks!! :-)Fri, 23 Mar 2012 10:17:16 GMTrhinton21http://www.sqlservercentral.com/Forums/Topic1270640-357-1.aspxDear forum member, I have an interesting question. The disk space where I keep my SQL data of one of my dbs is running out of space. I tried to backup the transaction log, which works fine, but the log is still 8 G big. When I check the initial space allocated for the log file, it is 8 G. That is ofcourse the reason why the log will not decrease in size. I cannot decrease the initial space allocated since the log is at this moment 8 G, but I can also not get the log smaller because the initial space allocated prevents that. I'm running around in a circle here. How can I solve this? The initial size of the log doesn't have to be that big, I think this is from the start of the database when a lot of data was migrated into it. Anyone? Thanks. VeraThu, 22 Mar 2012 01:15:09 GMTVera-428803