InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 08 Nov 2009 01:39:07 GMT20http://www.sqlservercentral.com/Forums/Topic815481-357-1.aspxHi,I had to do a translog dump:DBCC SHRINKFILE('mxphxx_log001', 1) -- BACKUP LOG MSPHXX WITH TRUNCATE_ONLY DBCC SHRINKFILE('mxphxx_log001', 1)Now my translog backups are failing with this error:[quote]Executed as user: PBGOPS\LPB_SSDS_PRD. ....00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:40:01 PM Progress: 2009-11-07 17:40:06.11 Source: {7405DEE1-C269-48B3-89E2-AD28208B59C9} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2009-11-07 17:40:06.92 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'P:\Program F".: 25% complete End Progress Progress: 2009-11-07 17:40:06.94 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'P:\Program F".: 50% complete End Progress Progress: 2009-11-07 17:40:06.94 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'P:\Program F".: 75% complete End Progress Progress: 2009-11-07 17:40:06.94 Source: Back Up Database (Transaction Lo... The package execution fa... The step failed.[/quote]Help - I don't know how/if i can reset the translog?Sat, 07 Nov 2009 15:59:06 GMTkrypto69http://www.sqlservercentral.com/Forums/Topic813200-357-1.aspxHi all,I'm not sure how to start on this, but here's what our network admin is trying to implement now:He wants to take every server we have (data, sql server, backups, files, etc) on a tape, every day.Well, when this looks like a good idea, I told him we need to do SQL Backups (Full and diffs) in a directory, that he will then be able to take in backup. He said that his new software takes care of the sql server databases, and backups everything we need, so I do not need to do sql backups anymore (real .bak files).I read a bit on the software, and where it sounds really interesting, I would like to have a few opinions on this, since I'm really not comfortable in having no sql backups at all, and just relying on a software that should do it all. Is it safe, what do you experts think backup wise?Most of our servers are not "that" critical, meaning none of them need to be restored as a point in time, but we have invoice history servers that need to be backed up and sent to a relay center every month, so we can recreate invoices, if the building gets destroyed. So all our databases are in simple recovery mode, and it's probably staying that way for a long time.I would really like to have some input on this, I want to stand up against that fact, but only if it really is not a good idea.Thanks in advance,Tue, 03 Nov 2009 13:41:11 GMTJ-F Bergeronhttp://www.sqlservercentral.com/Forums/Topic814373-357-1.aspxCan anyone give me the pros & cons of using SQL Server backup vs other backup tools like AppAssure Replay for SQL?Our network admin is swears by this product for backing up the Exchange server but I am wary of third party backup & recovery tools.Any help/advice would be much appreciated.ThanksThu, 05 Nov 2009 09:37:36 GMTbdaveyhttp://www.sqlservercentral.com/Forums/Topic815201-357-1.aspxHi.My server team is pushing a switch to DPM after they saw what it says it can do.I have a lot of questions, and we need to do a lot of testing, but was wondering if someone could shed some light on issues they've had with it so I know what to focus on.Things that concern me are environments with large transactions per second, potential locking/blocking within the database, etc.It seems like a reasonably good idea, but call me old-fashioned...But...I'd prefer to back up to a SAN/LUN and then have it mirrored and SNAPped and then backup the broken mirror, etc, etc.Am I being too anal? Change is good.......As long as it doesn't muck up database performance or recovery!Thanks,MikeFri, 06 Nov 2009 14:41:37 GMTMike-263299http://www.sqlservercentral.com/Forums/Topic809912-357-1.aspxHi Everyone,I want to know how to find out in Sql Server 2005 that the backup is happening through the third party tool(eg veritas), other than the sql server backup. Is there any script avaliable, or which table i have to query in msdb ([b]backupfile,backupfilegroup,backupmediafamily,backupmediaset,backupset[/b]). I tried to query all these table in msdb but in vain :( Thanks in Advance.PT.Wed, 28 Oct 2009 07:28:41 GMTParitosh-412771http://www.sqlservercentral.com/Forums/Topic814249-357-1.aspxI set up back to run through my Maintatnce Plan and it is failing, error message: Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed. Any idea why?Thu, 05 Nov 2009 07:53:27 GMTKrasavitahttp://www.sqlservercentral.com/Forums/Topic814596-357-1.aspxwhat is the best practice for backup/restore database files and filegroup files ?Regards,Thu, 05 Nov 2009 15:13:10 GMTMTY-1082557http://www.sqlservercentral.com/Forums/Topic814506-357-1.aspxHi there,From what I've seen this question gets asked quite a bit, but I can't get a solution that works for me.I am running a database using SQL Server Management Studio Express. There is no backup wizard. My HMI program can run short SQL scripts on a schedule. I want to take a daily backup of the database, say at midnite. And then I want to take transaction log backups every few hours.I want each transaction log backup to have date AND timestamp included in the filename, but this does not seem easy. I can use this script:DECLARE @fullpath nvarchar(255) set @fullpath = 'c:\sql\logbak' + str(year(getdate())) + str(month(getdate())) + str(day(getdate())) + '.trn' BACKUP log cimplicity TO DISK = @fullpath WITH FORMAT, STATSAnd it works, but it only has a datestamp. I need time included. It seems that many time data types and procedures aren't included or something on this instance of SQL.Does anyone know how to get a timestamp included with filename? I just don't want to keep overwriting a file or keep growing a single file.Thanks,Scott Cheneyscheney@coritech.comThu, 05 Nov 2009 12:51:50 GMTscheney-1152259http://www.sqlservercentral.com/Forums/Topic814486-357-1.aspxThis might sound a little naive, but why take more than one Full Database backup if you periodically take Differential and Log backups?Thu, 05 Nov 2009 12:19:37 GMTbugophttp://www.sqlservercentral.com/Forums/Topic813233-357-1.aspxI am not a DBA but am acting as one for now so I don't know 100% how to write certain things. The situation we have is follows:We have a stored procedure that runs every night that does a full backup of all the databases on a particular server to a NAS location lets say, "\\server1\backups" This location only holds the most current backup and 1 day old backup. These backups are production backups. These backups are typical names (database_20091102120019.bak)I would like to create a stored procedure that will restore TO THE DEVELOPMENT server, only a selection of databases from the "\\server1\backups" folder. The restore will not be taking the most recent backup but the one that is a day old. Since the backups are timestamped, I'm not 100% sure how to do this.The restore does not have to have recovery and is something that I want to schedule to happen once a week. Anyone have any sql T-scripts that can help me? Thanks.Tue, 03 Nov 2009 14:26:35 GMTdwh-1095413http://www.sqlservercentral.com/Forums/Topic813424-357-1.aspxMS SQL 2005I have a problem I need to restore a database without restoring the associated Log Files.The reason for this is that the log files are massive (~80gig) and the server which this needs to be restored to has only 10gig free for logs. Also the log file is about 99% free for some reason.The actual backup weights in at around 5gig and the size of database is around the 11gig.I have enough room to recover the database and Full Text Indexes.I cannot take the database offline to copy the .MDF file as its a production and every 5 minutes 1000s of row of new data is inputted.Also the target Server can not talk to the production server, so I can do SQL INSERT INTO commands.I've researched the internet and can't find any help.Tue, 03 Nov 2009 23:02:07 GMTra_leechhttp://www.sqlservercentral.com/Forums/Topic814375-357-1.aspxI have a quick question. Can you run the following on a production live database while users are on it? sp_configure 'show advanced options'1goreconfguregoThank YouThu, 05 Nov 2009 09:40:06 GMTskhan-680833http://www.sqlservercentral.com/Forums/Topic814127-357-1.aspxHi,how to determine the backup is successfully completed?Thankskoteswar RaoThu, 05 Nov 2009 04:59:28 GMTy.koteswarrao-652921http://www.sqlservercentral.com/Forums/Topic813619-357-1.aspxosql -S VCDB02\Core -E -e -Q "EXEC master.dbo.xp_backup_database @database='xapi', @filename='\\VCDB02\j$\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\Step_11_xapi_backup.BAK', @init= 1, @threads = 3" >> \\vslpbapp1\Phoenix\archive\LogFiles\UpgradeBackupLog_20080912_Step_11.txtIncorrect syntax near 'S'.Can't figure out why the -S is wrong..Wed, 04 Nov 2009 08:17:19 GMTkrypto69http://www.sqlservercentral.com/Forums/Topic813560-357-1.aspxHi, Machine Configuration : Sql server 2005 with SP2, OS : windows server 2KIn last couple of days , Sql server agent (services ) got stopped unexpectedlynobody has done anything, while see through naked eyes , i couldnt find anything specific to that stop. We have "restart sql server agent when unexpectedly stops" option ONcan any one , help me in it. Below is the pasted log ----------------------------------------------------------------------------Date,Source,Severity,Message10/26/2009 12:20:23,,Warning,[098] SQLServerAgent terminated (forcefully)10/26/2009 12:20:23,,Error,[359] The local host server is not running10/26/2009 12:20:22,,Error,[359] The local host server is not running10/26/2009 12:20:22,,Warning,[188] Scheduler engine timed out (after 15 seconds) waiting for 1 jobs(s) to stop10/26/2009 12:20:06,,Error,[359] The local host server is not running10/26/2009 12:20:06,,Error,[359] The local host server is not running10/26/2009 12:20:06,,Error,[359] The local host server is not running10/26/2009 12:20:06,,Warning,[360] SQLServerAgent initiating shutdown following MSSQL$REPORT_DEV shutdown10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnAttemptCachableOp)10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnAttemptCachableOp)10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnUpdateJobActivity_NextScheduledRunDate)10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnAttemptCachableOp)10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnAttemptCachableOp)10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnAttemptCachableOp)10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnAttemptCachableOp)10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnAttemptCachableOp)10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnAttemptCachableOp)10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnUpdateJobActivity_NextScheduledRunDate)10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnUpdateJobActivity_NextScheduledRunDate)10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnAttemptCachableOp)10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnAttemptCachableOp)10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (JobManager)10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnAttemptCachableOp)10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnAttemptCachableOp)10/26/2009 12:20:02,,Error,[382] Logon to server 'intechdba' failed (ConnUpdateStartExecutionDate)10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005<c/> this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> Client unable to establish connection due to prelogin failure [SQLSTATE 08001]10/26/2009 12:20:02,,Error,[165] ODBC Error: 0<c/> Client unable to establish connection due to prelogin failure [SQLSTATE 08001]10/26/2009 12:20:01,,Error,[382] Logon to server 'intechdba' failed (ConnUpdateStartExecutionDate)10/26/2009 12:20:01,,Error,[165] ODBC Error: 0<c/> Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000]10/26/2009 12:20:01,,Error,[382] Logon to server 'intechdba' failed (ConnUpdateStartExecutionDate)10/26/2009 12:19:03,,Error,[382] Logon to server 'intechdba' failed (ConnAttemptCachableOp)10/26/2009 12:19:01,,Error,[382] Logon to server 'intechdba' failed (JobManager)10/26/2009 12:19:01,,Error,[382] Logon to server 'intechdba' failed (ConnUpdateStartExecutionDate)10/26/2009 12:18:04,,Error,[382] Logon to server 'intechdba' failed (ConnUpdateStartExecutionDate)10/23/2009 01:20:52,,Warning,[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect10/23/2009 01:20:52,,Warning,[260] Unable to start mail session (reason: No mail profile defined)10/23/2009 01:20:52,,Information,[129] SQLAgent$REPORT_DEV starting under Windows NT service control10/23/2009 01:20:52,,Error,[364] The Messenger service has not been started - NetSend notifications will not be sent10/23/2009 01:20:51,,Information,[432] There are 11 subsystems in the subsystems cache10/23/2009 01:20:47,,Information,[339] Local computer is intechdba running Windows NT 5.2 (3790) Service Pack 210/23/2009 01:20:47,,Information,[310] 4 processor(s) and 6142 MB RAM detected10/23/2009 01:20:47,,Information,[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is intechdba10/23/2009 01:20:47,,Information,[102] SQL Server ODBC driver version 9.00.403510/23/2009 01:20:47,,Information,[101] SQL Server intechdba version 9.00.4262 (0 connection limit)10/23/2009 01:20:47,,Information,[100] Microsoft SQLServerAgent version 9.00.4262.00 ((Unknown) unicode retail build) : Process ID 4136Wed, 04 Nov 2009 06:33:12 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic813702-357-1.aspxHI, A vendor just set up a database for us and we were told we had to perform the backups and restores. That's fine but this is the first database we have with multiple files and file groups. We have a nightly backup and have included the "RESTORE FILELISTONLY' output from the backup file. We can't get a good sample from BOL.Can someone please help us with a script to restore the database with all it's files and filegroups using the with move. Our test server will rewquire us to use the with move for this test as we only have a drive C. For now we must test the restore using drive C for everything. We have tried looking it up in BOL but that's beyond this configuration.Please help... Database name is: NGProd Backup File is NGProd_data_dump.bak (Single FULL backup)The database will be put in FULL recovery mode shortly so we will have log backups also.Listing from RESTORE FILELISTONLY: Logical Name Path FileGroup Type FileIDNextGenSys D:\MSSQL.1\MSSQL\Data\NGProd_Data.mdf PRIMARY D 1NextGen_Core_Data_0 D:\MSSQL.1\MSSQL\Data\NGProd_Core_Data0.ndf NEXTGEN_CORE D 3NextGen_Core_Data_1 D:\MSSQL.1\MSSQL\Data\NGProd_Core_Data1.ndf NEXTGEN_CORE D 4NextGen_Core_Data_2 D:\MSSQL.1\MSSQL\Data\NGProd_Core_Data2.ndf NEXTGEN_CORE D 5NextGen_Core_Data_3 D:\MSSQL.1\MSSQL\Data\NGProd_Core_Data3.ndf NEXTGEN_CORE D 6NextGen_Core_Data_4 D:\MSSQL.1\MSSQL\Data\NGProd_Core_Data4.ndf NEXTGEN_CORE D 7NextGen_Core_Data_5 D:\MSSQL.1\MSSQL\Data\NGProd_Core_Data5.ndf NEXTGEN_CORE D 8NextGen_Core_Data_6 D:\MSSQL.1\MSSQL\Data\NGProd_Core_Data6.ndf NEXTGEN_CORE D 9NextGen_Core_Data_7 D:\MSSQL.1\MSSQL\Data\NGProd_Core_Data7.ndf NEXTGEN_CORE D 10NextGen_Index_0 I:\MSSQL.1\MSSQL\Data\NGProd_Index0.ndf NEXTGEN_INDEX_1 D 11NextGen_Index_1 I:\MSSQL.1\MSSQL\Data\NGProd_Index1.ndf NEXTGEN_INDEX_1 D 12NextGen_Index_2 I:\MSSQL.1\MSSQL\Data\NGProd_Index2.ndf NEXTGEN_INDEX_1 D 13NextGen_Index_3 I:\MSSQL.1\MSSQL\Data\NGProd_Index3.ndf NEXTGEN_INDEX_1 D 14NextGen_Index_4 I:\MSSQL.1\MSSQL\Data\NGProd_Index4.ndf NEXTGEN_INDEX_1 D 15NextGen_Index_5 I:\MSSQL.1\MSSQL\Data\NGProd_Index5.ndf NEXTGEN_INDEX_1 D 16NextGen_Index_6 I:\MSSQL.1\MSSQL\Data\NGProd_Index6.ndf NEXTGEN_INDEX_1 D 17NextGen_Index_7 I:\MSSQL.1\MSSQL\Data\NGProd_Index7.ndf NEXTGEN_INDEX_1 D 18NextGen_LOB_0 D:\MSSQL.1\MSSQL\Data\NGProd_LOB0.ndf NEXTGEN_LOB_1 D 19NextGen_LOB_1 D:\MSSQL.1\MSSQL\Data\NGProd_LOB1.ndf NEXTGEN_LOB_1 D 20NextGen_LOB_2 D:\MSSQL.1\MSSQL\Data\NGProd_LOB2.ndf NEXTGEN_LOB_1 D 21NextGen_LOB_3 D:\MSSQL.1\MSSQL\Data\NGProd_LOB3.ndf NEXTGEN_LOB_1 D 22NextGen_LOB_4 D:\MSSQL.1\MSSQL\Data\NGProd_LOB4.ndf NEXTGEN_LOB_1 D 23NextGen_LOB_5 D:\MSSQL.1\MSSQL\Data\NGProd_LOB5.ndf NEXTGEN_LOB_1 D 24NextGen_LOB_6 D:\MSSQL.1\MSSQL\Data\NGProd_LOB6.ndf NEXTGEN_LOB_1 D 25NextGen_LOB_7 D:\MSSQL.1\MSSQL\Data\NGProd_LOB7.ndf NEXTGEN_LOB_1 D 26NextGen_Log L:\MSSQL.1\MSSQL\Data\NGProd_Log.ldf NULL L 2Is the code that different [u]without[/u] the 'WITH MOVE' option?[b]Thank You.[u][/u][/b]really frustrated with BOL.Wed, 04 Nov 2009 09:57:20 GMTDana Bibeaulthttp://www.sqlservercentral.com/Forums/Topic813680-357-1.aspxI've been asked to try and recover some data accidentally deleted by a user. This is a contact database with not that much transaction volume. The kicker is, it does not appear this database had ever been backed up, and it was in Simple recovery mode.The transaction log file date goes back a month, so I'm wondering if there is any possibility of A) rolling back, or B) changing the database to Full recovery mode, take a backup of db/log and then restoring to a previous point. Would anything like that even be possible, or is this user simply SOL, and should take this as a dramatic lesson in ensuring you have backups?I've never attempted a rollback before, but have done restores to a point in time -- but in those cases there was a good backup routine that had been in place. I feel I may be grasping at straws here, but want to be sure before telling this person they are hooped.Wed, 04 Nov 2009 09:34:54 GMTpvanzhttp://www.sqlservercentral.com/Forums/Topic813592-357-1.aspxI have been tasked with changing a backup script.The only problem is that the server has changed.Here's the script:osql -SVCDB02\Core -E -e -Q "EXEC master.dbo.xp_backup_database @database='xapi', @filename='\\VCDB02\j$\Program Files\Microsoft SQL Server\MSSQL$CORE\Backups\Phoenix_Upgrade_20080912\Step_11_xapi_backup.BAK', @init= 1, @threads = 3" >> \\vslpbapp1\Phoenix\archive\LogFiles\UpgradeBackupLog_20080912_Step_11.txtI don't see a j$\Program Files\Microsoft SQL Server\MSSQL$CORE\Backups\Phoenix_Upgrade_20080912\Step_11_xapi_backup.BAK'location I have a :J:\Microsoft SQL Server\MSSQL.2\MSSQL\Backupdirectory on the new serverdo I need to change this? or will it create the location/file?Wed, 04 Nov 2009 07:39:31 GMTkrypto69http://www.sqlservercentral.com/Forums/Topic599238-357-1.aspxI have a Maintenance Plan job that takes log backups of the User databases. Most of the time it runs successfully. Every now and then, it sends out a failure notification, however the log for the job shows success for the two tasks and the job has created all the log backup files. This is the message in the job step history:End Progress Error: 2008-07-26 12:15:41.04 Code: 0xC0024104 Source: Reporting Task for subplan-{08F57368-7039-4250-9BA3-AB97CEE97007} Description: The Execute method on the task returned error code 0x80131931 (Data is Null. This method or property cannot be called on Null values.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Error: 2008-07-26 12:15:41.04 Code: 0xC0024104 Source: Reporting Task for subplan-{0921A72F-E46C-4023-847C-B30E1D8382EA} Description: The Execute method on the task returned error code 0x80131931 (Data is Null. This method or property ca... The package execution fa... The step failed.I am running SQL Server 2005 Standard Edition SP2 CU6 (version 9.0.3228) on a 32-bit Windows Server 2003 Standard Edition SP2. The Log Backup Subplan only has 2 tasks: Maintenance Cleanup Task and Back Up Database Task.Have any of you experienced this before? I don't want the job to send out failure notifications to the support team when it is successfully completing the tasks. What should I do to correct this?Fri, 07 Nov 2008 12:49:12 GMTCindi McCann-Murphyhttp://www.sqlservercentral.com/Forums/Topic813108-357-1.aspxHello,I need help or good site to get the steps to migrate the data, shcema, logins, dependencies, agent jobs from sql 2000 to Sql 2008. The shcema has been changed in sql 2008 so I have no idea how to move the data to new Server with old schema. Please HELP-Tue, 03 Nov 2009 11:10:51 GMThydbadrosehttp://www.sqlservercentral.com/Forums/Topic463441-357-1.aspxHad to migrate to new hardware running Server 2003, sql 2005, but keeping same server name. Databases, logins, two SSIS(DTS) packages working great. But, System and User maintenance plans failing since name change.So, recreated them, and (with minor problem to be addressed at another time) the recreations are working fine. But, I can't delete (or modify) the old plans or jobs. Get the following error when physically sitting in front of the server logging in "Exception has been thrown by the target of an invocation(mscorlib). An error has occurred while establishing a connection to the server. When connecting to sql server 2005, this failure may be cause by the fact that under the default settings, sql server does not allow remote connecitons. (provider: tcp provider, error: 0 no such host is know) error 11001As I said, I've disabled both jobs, but would prefer to delete the plan/jobs. But, it won't let me. Any ideas?Mon, 03 Mar 2008 14:32:48 GMTJanet Barnetthttp://www.sqlservercentral.com/Forums/Topic798005-357-1.aspxHi Folksusing SSMS, I have user full backups scheduled to run everynight at 12:00 amas of 9-30-09 they stopped working for some reasonthey have been working since 7-9-2009 when i set the jobs upthere are no errors anywhere that i can findthere are no entries in the Application events there are no entries for them under "View History"my diff backups and trans backups are still workingdoes anyone know where i can look to see what has happened to my full backup jobs ?ThanksjimMon, 05 Oct 2009 09:38:12 GMTJC-3113http://www.sqlservercentral.com/Forums/Topic812939-357-1.aspxHi allSQL Server 2005 sp3 on Windows 2003 sp2. database in full recovery mode, full back has been ok, ran low on disk space causing backup to fail. i deleted file to free up space on C: drive. Full back fails but log file backsup fine.Microsoft was unable to figure this out. Any ideas would be appreciated.Processed 252648 pages for database 'Store146', file 'Store901_Data' on file 1.Msg 3202, Level 16, State 2, Line 1Write on "c:\DB_Backups\store146.bak" failed: 1(Incorrect function.)Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.2009-11-03 06:48:26.52 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.2009-11-03 06:48:26.61 spid12s Starting up database 'msdb'.2009-11-03 06:48:26.61 spid13s Starting up database 'Store146'.2009-11-03 06:48:26.76 spid9s CHECKDB for database 'model' finished without errors on 2009-11-02 22:00:03.530 (local time). This is an informational message only; no user action is required.2009-11-03 06:48:26.78 spid9s Clearing tempdb database.2009-11-03 06:48:27.95 spid12s CHECKDB for database 'msdb' finished without errors on 2009-11-02 22:00:03.797 (local time). This is an informational message only; no user action is required.2009-11-03 06:48:30.30 spid9s Starting up database 'tempdb'.2009-11-03 06:48:31.12 spid9s CHECKDB for database 'tempdb' finished without errors on 2009-11-02 22:00:03.530 (local time). This is an informational message only; no user action is required.2009-11-03 06:48:32.15 spid12s The Service Broker protocol transport is disabled or not configured.2009-11-03 06:48:32.18 spid12s The Database Mirroring protocol transport is disabled or not configured.2009-11-03 06:48:32.38 spid12s Service Broker manager has started.2009-11-03 06:48:34.83 spid13s CHECKDB for database 'Store146' finished without errors on 2009-11-02 22:00:05.873 (local time). This is an informational message only; no user action is required.2009-11-03 06:48:34.86 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.2009-11-03 06:48:34.86 spid5s Recovery is complete. This is an informational message only. No user action is required.2009-11-03 06:48:36.30 spid51 Using 'xpsqlbot.dll' version '2005.90.4035' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.2009-11-03 06:48:36.65 spid51 Using 'xpstar90.dll' version '2005.90.4035' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.2009-11-03 06:48:36.72 spid51 Using 'xplog70.dll' version '2005.90.4035' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.2009-11-03 06:54:47.44 spid56 Error: 18210, Severity: 16, State: 1.2009-11-03 06:54:47.44 spid56 BackupMedium::ReportIoError: write failure on backup device 'c:\DB_Backups\store146.bak'. Operating system error 1(Incorrect function.).2009-11-03 06:54:47.48 Backup Error: 3041, Severity: 16, State: 1.2009-11-03 06:54:47.48 Backup BACKUP failed to complete the command BACKUP DATABASE Store146. Check the backup application log for detailed messages.2009-11-03 07:21:36.37 spid56 Error: 18210, Severity: 16, State: 1.2009-11-03 07:21:36.37 spid56 BackupMedium::ReportIoError: write failure on backup device 'c:\temp\store146.bak'. Operating system error 1(Incorrect function.).2009-11-03 07:21:36.37 Backup Error: 3041, Severity: 16, State: 1.2009-11-03 07:21:36.37 Backup BACKUP failed to complete the command BACKUP DATABASE Store146. Check the backup application log for detailed messages.2009-11-03 07:37:11.35 Backup Log was backed up. Database: Store146, creation date(time): 2009/10/01(11:07:01), first LSN: 26541:58149:1, last LSN: 26544:129764:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\DB_Backups\Store146\Store146_backup_200911030737.trn'}). This is an informational message only. No user action is required.2009-11-03 07:40:47.99 spid56 Error: 18210, Severity: 16, State: 1.2009-11-03 07:40:47.99 spid56 BackupMedium::ReportIoError: write failure on backup device 'c:\temp\store146.bak'. Operating system error 1(Incorrect function.).2009-11-03 07:40:47.99 Backup Error: 3041, Severity: 16, State: 1.2009-11-03 07:40:47.99 Backup BACKUP failed to complete the command BACKUP DATABASE Store146. Check the backup application log for detailed messages.BackupMedium::ReportIoError: write failure on backup device 'c:\temp\store146.bak'. Operating system error 1(Incorrect function.).Tue, 03 Nov 2009 08:01:32 GMTDarwin101http://www.sqlservercentral.com/Forums/Topic810602-357-1.aspxHi,When restoring a SQL 2005 database to another database server, the statistiscs of the source database server are not restored. Is there a way to also restore the statistics of the database?Regards,Meridium.Thu, 29 Oct 2009 03:53:52 GMTmailings 84246http://www.sqlservercentral.com/Forums/Topic811426-357-1.aspxHi,I have to copy one database from one server to another.as i can't opt back up and restore bcoz of shortage of space,i decided to copy the mdf & ldf files then attach the same.i copied into another serveri ran the following script:[code="other"]create database DacsTranson (filename ='\dbname.MDF')log on (filename = 'dbname.LDF')for attach[/code] but while attaching i'm getting the following error:[code="plain"]Msg 1813, Level 16, State 2, Line 2Could not open new database 'DacsTrans'. CREATE DATABASE is aborted.Msg 824, Level 24, State 2, Line 2SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:3067392; actual 0:0). It occurred during a read of page (1:3067392) in database ID 7 at offset 0x000005d9c00000 in file 'E:\DB Backup\DacsTrans.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.[/code] i tried to create a new database with the same ldf& mdf in different location. then replaced the mdf file, deleted and thought i can rebuild the log file.but as i can't change the sysdatabase status(sql server 2005) i could not follow that method.any suggestions and advices are highly appreciated.Regards,AnamikaFri, 30 Oct 2009 03:42:46 GMTAnamikahttp://www.sqlservercentral.com/Forums/Topic810646-357-1.aspxI run the following script ---------------------------------------------------------------RESTORE DATABASE testFROM DISK = '\\srv\d$\Backup\DB20091019.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10GO---------------------------------------------------------------But found this error---------------------------------------------------------------Msg 3201, Level 16, State 2, Line 3Cannot open backup device '\\srv\d$\Backup\DB20091019.bak'. Operating system error 5(error not found).Msg 3013, Level 16, State 1, Line 3RESTORE DATABASE is terminating abnormally.---------------------------------------------------------------By default, my account can NOT access the folder\\srv\d$\BackupBut once I try to enter the above folder Windows pop up page to enter UserName & Password.Then I input UserName & Password of the SERVERAfter that, I can access the folder.However, I still can NOT run SQL for Restore properly (Without error)Please advise.Thu, 29 Oct 2009 05:26:02 GMTneo_pomhttp://www.sqlservercentral.com/Forums/Topic812065-357-1.aspxHi,How to verfiy / double cross check there is a LSN chain break?Is there any system table?Also,What is the difference between First LSN, Last LSN, Differentail LSN and Checkpoint LSN. How to analyze/understand or How these are going to help us in backup and recovery??Thanks in advance.Sat, 31 Oct 2009 20:57:22 GMTmahesh.vsphttp://www.sqlservercentral.com/Forums/Topic452567-357-1.aspxWhat would be the best action to take when you see the following error messageBACKUP failed to complete the command BACKUP LOG 'Databasename'. Check the backup application log for detailed messages.Thanks in advanceWed, 06 Feb 2008 23:30:23 GMTZiljan4http://www.sqlservercentral.com/Forums/Topic561898-357-1.aspxI have a bit of a strange issue where my backup maintenance plan for one of my databases fails on the Differential backup on the Sunday night.What is strange is that I have a full backup that takes place on a Friday night, the differential backup works on the Saturday and then fails on the Sunday. I have no idea what is causing this and it has been happening for a few weeks now. When I run a manual full backup the maintenance plan Differential will start to behave again. I know there is a valid full backup as I can see it in the directory.I have updated the server to SP2 and rebuilt the Maintenance plan and the issue is persisting. The server details and error messages are listed below.If anyone has experienced similar issues or has any suggestions I would appreciate them, if any more information is needed please let me know and I'll dig it out.Many thanksJohn[b]Server Details[/b]Edition: Standard Edition (64-bit)Service Pack: SP2Software Version: 9.00.3042.00[b]Maintenance Plan Details[/b]Sub Plan 1: Weekly Full backup on Fridays at 18:00Sub Plan 2: Daily Diff backup on Sat - Thurs at 18:00 (This one fails on the Sunday)[code][b]Package Error Message[/b]Executed as user: APOLLO_FIRE\svc_SQL_%SERVERNAME%. ...042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.Started: 18:00:00 Progress: 2008-08-31 18:00:00.80 Source: {BBD35348-C9D1-4E80-B3D2-0B6140C665C0}Executing query "DECLARE @Guid UNIQUEIDENTIFIEREXECUTE msdb..sp".: 100% complete End Progress Progress: 2008-08-31 18:00:00.92Source: Back Up Database TaskExecuting query "EXECUTE master.dbo.xp_create_subdir N'E:\DatabaseB".: 100% completeEnd Progress Error: 2008-08-31 18:00:00.94 Code: 0xC002F210Source: Back Up Database Task Execute SQLTask Description: Executing the query "BACKUP DATABASE [%DatabaseName%]TO DISK = N'E:\DatabaseBackups\%DatabaseName%\%DatabaseName%_backup_200808311800.bak'WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'%DatabaseName%_backup_20080831180000',SKIP, REWIND, NOUNLOAD, STATS = 10 "failed with the following error: "Cannot perform a differential backup for d... The package execution fa... The step failed.[b] Step Error Message[/b]Executing the query "BACKUP DATABASE [%DatabaseName%]TO DISK = N'E:\\DatabaseBackups\\%DatabaseName%\\%DatabaseName%_backup_200808311800.bak'WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'%DatabaseName%_backup_20080831180000',SKIP, REWIND, NOUNLOAD, STATS = 10" failed with the following error: "Cannot perform a differential backup for database "%DatabaseName%",because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE,omitting the WITH DIFFERENTIAL option. BACKUP DATABASE is terminating abnormally.".Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly,or connection not established correctly.[/code]Mon, 01 Sep 2008 02:54:35 GMTJMartin-392745http://www.sqlservercentral.com/Forums/Topic526252-357-1.aspxDifferential backups were working fine on my server (SQL Server 2005) until last week. Now they fail on every database. Even if I run the most basic backup command i get an error like this one:[quote]Failed:(-1073548784) Executing the query "BACKUP DATABASE [MYDB] TO DISK = N'D:\\SQLBackup\\MYDB\\MYDB_backup_200806271259.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'MYDB_backup_20080627125948', SKIP, REWIND, NOUNLOAD, STATS = 10" failed with the following error: "Cannot perform a differential backup for database "MYDB", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.[/quote]I am sure I have a full backup because after I've taken it I check in the logs that it's worked and check in SQL Server Manager and can see the date of last backup is correct.Mon, 30 Jun 2008 15:18:11 GMTdaniel.bowetthttp://www.sqlservercentral.com/Forums/Topic809766-357-1.aspxHi Folks, Is it possible to backup the database between the dates? As we can take differential backup, is it possible to backup the database between two particular dates? We have a huge data. The data entry operation team produces thousands of records per week. We already have delivered the application and first full backup of 100000 records to client. After that we have delivered 10k of new records to client as first differential backup. We need to deliver the newly entered data on bi-weekly basis. Here after first differential backup, second differential backup contains the data difference since last full backup(with redundant data of first differential backup). How is it possible to backup only newly entered data in every two weeks and restore that data on client site? -VickyWed, 28 Oct 2009 03:26:56 GMTVicky-854895http://www.sqlservercentral.com/Forums/Topic810964-357-1.aspxGot an odd one here, that I hoped one of you very helpful people reading this, may be able to clear up for me.I execute a backup script to backup a database to C:\Database Backups on the local server.The backup returned for following information....[code="other"]Processed 24616 pages for database 'myDatabase', file 'myDatabase_Data' on file 1.Processed 1 pages for database 'myDatabase', file 'myDatabase_Log' on file 1.BACKUP DATABASE successfully processed 24617 pages in 18.313 seconds (11.011 MB/sec).[/code]The problem is the execution actually only took 3 seconds!! So why is it telling me 18 seconds??????I run the same backup, only this time to a file share location using a \\UNC path and returned details said the backup took 5 seconds which it did. So I'm a little confused as to why SQL server thinks it took 18 seconds to backup to the local drive!Any ideas peeps?Thu, 29 Oct 2009 10:17:59 GMTdave-488664http://www.sqlservercentral.com/Forums/Topic810898-357-1.aspxWe have to data bases say 1 and 2 and we have restored the data base 1 and 2 from live to our test environment.Now we are trying to re-register the scripts for clr procs and its giving us the following errors:-SQL Server Database Error: The database owner SID recorded in the master database differs from the database owner SID recorded in database 'Test'. You should correct this situation by resetting the owner of database 'Test' using the ALTER AUTHORIZATION statement.Why this is coming? Is that because DB ID's on live and Test environment are different?cheersSiddarthThu, 29 Oct 2009 09:36:11 GMTsiddartha palhttp://www.sqlservercentral.com/Forums/Topic809882-357-1.aspxWe have to data bases say 1 and 2 and we have restored the data base 1 and 2 from live to our test environment. We have a user with name appsa and when we try to logon to data base 1, we cant login.We checked ---security--login-appsa--rt click---user mappings. one form appears with three fields data base name, user and default schema. Now the user name is showing dbo instead of showing appsa. could you please let me know whaht could be the issue?cheersSiddarthWed, 28 Oct 2009 07:11:40 GMTsiddartha palhttp://www.sqlservercentral.com/Forums/Topic806522-357-1.aspxI have an sql2005 installation and a mirror server. Many of my databases are already mirrored, and I went to add a new one now that it is up and ready for production. It is running fine on the primary server. Just like the others I did a full backup and a log backup.On the mirror server I loaded the .bak with 'restore with no recovery'. No problem.Then I went to restore the log file (using studio) and got the error (as soon as I try to select the file, it does not wait till I select the restore instance):Cannot find table 0. (System.Data)In case the backups were corrupt, I tried it today with new .bak and .trn and the exact same failure occurred.I tried restoring the .trn from t-sql and got this error:Msg 4038, Level 16, State 1, Line 1Cannot find file ID 1 on device 'g:\temp\BACKUPFILENAME.trn'.Msg 3013, Level 16, State 1, Line 1RESTORE LOG is terminating abnormally.Other dbs have been successfully restored to this machine (and subsequently mirrored). As recently as yesterday I had loaded and mirrored another db (and this db IS full logging option). What could be going on with this one?Wed, 21 Oct 2009 08:10:11 GMTjay holovacshttp://www.sqlservercentral.com/Forums/Topic810590-357-1.aspxTest db still in restoring state after restored live db over test dbI have restored a live database over a test database. However i did not backup the tail log of the test database before i restored the live database on top of it.i used the following script USE master GO /* ABC = production database ABCTEST = test database */ RESTORE DATABASE [ABCTEST] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ABC_backup_200910212030.bak' -- edit file name to match WITH FILE = 1, -- normally this would be = 1 MOVE N'ABC' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABCTEST.mdf', MOVE N'ABC_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC_log.LDF', NOUNLOAD, REPLACE, STATS = 10 GO ALTER DATABASE [ABCTEST] MODIFY FILE (NAME=N'ABC', NEWNAME=N'ABCTEST') GO WHICH WILLIAM MITCHELL POSTEDHow do i bring the database out of the restore state?Thanks, Iain.Thu, 29 Oct 2009 03:28:08 GMTicampbellhttp://www.sqlservercentral.com/Forums/Topic809764-357-1.aspxHi,I've got a database back up that writes to the local server about 10 x 37gig files, the ten files then get moved to the main back up storage server into a folder that has the 'Compress contents to save disc space' ticked in the folder options. However over the past few days i've been getting a warning in the event view of the storage server that indicates that the second file of the back up is getting a 'Windows - Delayed Write Failed' as its been unable to save the data. I did think that windows folder compression got a bit flaky over the 20gig, but i've got other database in other folders on the same SAN drive that are a back up of 40gig, and they compress fine?My main question is, Is storing the backups in a compressed folder best practice?? and has anyone else seen this??I may have to dump the idea of compression, and just leave the files uncompressed on the drive??thanksJonWed, 28 Oct 2009 03:20:53 GMTjon.lunnhttp://www.sqlservercentral.com/Forums/Topic810443-357-1.aspxThis week I setup logshipping in my prod env to another env for around 50 dbs and all of them are in SQL 2005 instance. One of the db is taking hell lot of time to recover the db in standby mode. The db is 13GB size and logs are 5 to 7MB sizes.When I restore full back manually it took long time and when I track the spid using the below query, the percent_complete value is reaching 100 in 2mins 15secs but after it stays more than 30mins to recover the db.SELECT percent_complete, * FROM sys.dm_exec_requestsWHERE session_id=135Once the db restored (after 35mins) I did the LS setup then the .TRN files taking long time to restore. even for 7MB file taking 18+mins to restore. Initially i thought it would be some storage issue but lot of other database files resides and they are going well. Note: The same db is being log shipped 2 other instances, they also having the same issue. Also in the primary, this db is migrated from sql 2000 and still the compatibility level is 80.Anybody faced this kind of issues earlier or any thoughts on this to overcome?ThanksJay.Wed, 28 Oct 2009 19:09:01 GMTJayakumar Krishnanhttp://www.sqlservercentral.com/Forums/Topic810406-357-1.aspxWhen you make a backup of SQL server using VSS or a third party tool... what happens?It says it freezes I/O, VSS gets a snapshot then it resumes I/O... but what is going on there?If I have a 20 TB database to snapshot it is going to take me more than 60 seconds right?So that entire time does VSS maintain a differential snapshot or something that I backup where each change is recorded by VSS as it differs from the original?SQL server keeps working and if 50 million rows are deleted then VSS adds a differential to it's snapshot of those 50 million rows so they will be there for my backup to work with?Am I completely misunderstanding how this all works?Wed, 28 Oct 2009 16:27:12 GMTMaxerhttp://www.sqlservercentral.com/Forums/Topic571286-357-1.aspxWe are currently unable to open SQL Server Management Studio and are getting the following error:Database 'msdb' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)This morning we were getting another error stating that msdb could not be accessed. When I noticed that the msdbDATA.mdf file was missing I attempted to restore from backup. This is when I started to see the error listed above. Now the msdb is in a state of "Restoring". I would like to stop that restore so that I may delete the msdb database and then re-create it from a script or restore it from a backup. Here's what I've tried:1. Restarted the SQL Server service (error remains)2. Rebooted server (error remains)3. Started service with start sqlservr.exe -c -T3608 (this allows me to browse other databases via SSMS but the msdb still shows as (Restoring...) and other DB then will stop functioning (i.e. the apps that hit these DBs will be broken).Due to this problem we are not able to run any scheduled jobs/backups. How can I change the status of msdb to Normal so that I might remove it, then restore from backup? Thanks for any help you can offer.Wed, 17 Sep 2008 13:32:54 GMTRyan-209402