SQL 2012 :: Backup Job And Integrity Checks Are Failing?
Sep 27, 2015
I am using sql server 2012 with HADR (Always on with sql cluster).
We have database maintenance plans through wizard for full backup & DBCC CHECK DB. It was running successfully but it failed with the below error
Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection.
(A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)).
(A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)).
I can able to take the backup from query window. It is succesful. The Sql Agent has full permissions. I don't think there are any recent changes happen.
View 9 Replies
ADVERTISEMENT
Apr 21, 2003
I have a few databases on this Windows 2000 Server running
SQL 2000 which were detached from SQL 7.0 and attached to
SQL 2000.
The problem is the Maintenance Plans (Integrity Checks
keep failing on SQL 2000. I 'DTS'ed a SQL 7.0 database to
this SQL 2000 server and ran the Maintenance Plans on that
database. Works fine only for the DTS'ed database.
What am I missing ???
:confused:
View 5 Replies
View Related
May 7, 2007
Hi,
SQl Server 7
I have Daily User DB Integrity Checks job running daily
From past 2 days i am getting below error.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 35 consistency errors in table 'Prod_Hist' (object ID 2098106515).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 99 consistency errors in database 'Ucatalog'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Ucatalog repair_fast).
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Please suggest..
Thanks in Advance
Adil
View 1 Replies
View Related
Aug 21, 2002
On weekends I have Integrity Checks scheduled to run. Many of these fail for individual databases because users do not log off and the databases cannot be switched to single user mode.
I have checked Books-on-line and have not yet stumbled onto a TSQL command that breaks the connections.
Is there a TSQL command to do this? If not, how can these connections be broken?
View 2 Replies
View Related
Jul 29, 2004
Hello, I had a DB Maintenance plan, the schedule is every day, but today I found teh 'Integrity checks job is failed". What is that mean? How to check this. Thanks.
View 14 Replies
View Related
May 10, 2006
Activity: Check Data and Index Linkage
Error Number: 3624
Severity: 20
State: 1
The errorlog has this:
SQL Server Assertion: File: <p:sqltdbmsstorengdrsinclude
ecord.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
There is a dump file generated also.
I had run DBCC CHECKDB and no error is found.
Any help is appreciated.
Thanks
View 3 Replies
View Related
Aug 30, 2005
My SQLMaint integrity checks consistently fail when the "Repair any minor problems" option is checked. The reported reason is that the database must be in single user mode. This doesn't seem practical. Am I missing an option somewhere?
View 1 Replies
View Related
Dec 3, 2007
The integrity checks job on the user databases failed over the weekend and here is the error I got from the report:
Database DB_Stores: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed.
Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed.
Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]
Database state cannot be changed while other users are using the database 'DB1'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
Googled the issue and found some articles on this issue, but wanted to also run it by you.
I looked at the maint plan properties and under the integrity tab of the maint plan attempt to repair any problems is checked, this is what I think:
Since attempt to repair is checked, the db was trying to repair the issue and since users were logged into the system
it could'nt repair the issue as the system needs to be in single user mode. I think if we uncheck the attempt to repair
option, then the job would run fine.
But is this the best way to do? how about the errors it was trying to repair? Do we have to fix the error's it was trying to
fix later by changing the db to single user mode.
PLease let me know your ideas, thanks!!
View 1 Replies
View Related
Feb 1, 2007
Hi,
I want to automate the process of database integrity checks in sql server any suggestions how to do
View 1 Replies
View Related
Jan 2, 2002
Hi everbody,
Integare check job failing on replication(merge) database.Can you tell me how to schedule the this job.
Thanks
Mark
View 1 Replies
View Related
Aug 5, 2014
I have a VM set up for offloading DBCC checks. Specs are below. I've read through this, but I'm not seeing the performance gains by enabling the trace flags and using the physical only switch.
Is the whole drawback that I'm on SATA storage? Is there a VM configuration with the CPU I can/should change? I've been playing with MAXDOP trying to see if I can get any benefits but I'm not seeing a much.
wait_type wait_time_spctrunning_pct
CXPACKET 561191.4228.7128.71
OLEDB 387136.7619.8148.52
PAGEIOLATCH_SH 340674.5817.4365.95
TRACEWRITE 321598.8416.4682.41
[code].....
View 9 Replies
View Related
Nov 15, 2007
I am using sql server 2000 and windows server 2003 standard edition:
My database backup job is failing due to lack of disk space. I am taking the backup onto E drive and the
available free space on E drive is 6.85 GB and there are no other drives I can use for the database backups.
The size of mdf file is 21 GB and that of ldf file is 4.2 GB.
The transaction log back up job of that db ran fine.
This database recovery model is Full and
Auto shrink is not checked.
There is one primary filegroup for the database.
In this situation, I am thinking of the following option:
1. Backup the db and log files onto another network shared drive.
If I want to still use the same server E drive to backup the db and log files instead of using another network shared drives.
how can I do that.
Please let me know the best way of handling this issue.
Any help is greatly appreciated. Thanks!
View 1 Replies
View Related
Jun 1, 2015
I have a database in the 3rd normalized form. There is a need to load rows into a child table. To avoid having to drop RI, an ALTER TABLE WITH NOCHECK is being used. I am looking for a way to verify the integrity after the load is complete. After the load an ALTER TABLE WITH CHECK will be executed. Can I used DBCC to verify, that no orphaned child rows were loaded? If so, which parms would I have to use with DBCC?
View 0 Replies
View Related
Jul 6, 2014
Running Frequently Transaction logbackup during Integrinty check DB /optimization job will cause any issue /impact as duration will extend ...
View 2 Replies
View Related
May 29, 2014
Up until a couple of days ago all my maintenance plans were working.
Now they are all failing with the following errors -
Message
Executed as user: MHS2sql2008. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 10:07:02 Progress: 2014-05-29 10:07:03.20 Source: {CE851AA2-045F-429A-885E-E60E75A38639} Executing query "DECLARE @Guid UNIQUEIDENTIFIER
[Code] ....
The package execution failed. The step failed. Now I have changed the SQL Server Agent service account to use a network account called "sql2008".Stopped and started the Agent service.
The account has access to the folders it is trying to back up to. It's a full admin on the sql box and the box where I am trying to backup to?
View 4 Replies
View Related
Sep 19, 2014
In SQL Server 2012 (also 2005 and 2008), can you have Referential Integrity across a Database? Across a Schema?
View 1 Replies
View Related
Dec 24, 2007
Backup job for User databases is failing.
I found below errors
View job history
Step 0:
The job failed. The job was invoked by schedule 4(DBMP_User). The last step to run was step 1(subplan)
Step 1:
Message: Executed as user ServernameSystem. The package execution failed. The step failed
Appln-event log:
SQL server scheduled job DBMP_User failed. Invoked on 2007-12-24 , the job was failed.
Sql server error log
Database backed up. DBname creation,date()time……paged dumped 8434659,first LSN: 21126:101410:48,last LSN :21128:933:1, number of dump devices:1, device information: file=1,type=disk (E:MSSQLBACKUP )
This is an informational message only. No user action is required
Error log:
Date:
Log: SQL Agent (current …)
Message:
(396) An idle CPU condition has not been defined-OnIdle job schedules will have no effect.
Please advice how to proceed?
View 11 Replies
View Related
Apr 25, 2007
I have an SSIS package that does one simple thing: perform a FULL backup of a database.
I executed this package yesterday at 2:00. The package backs up to four individual files on a network share. The network share is accessible from the SQL Server. THe database in question is 245GB in size.
The package was running fine when I left for the day. When I got in today, there was an error in the SQL Server log:
Error: 3041, Severity: 16, State: 1.
BACKUP failed to complete the command BACKUP DATABASE ServicingODS. Check the backup application log for detailed messages.
Where is this infamous backup application log?!? The Event Viewer says the same thing. Needless to say, the error message is a bit vague.
There were no "issus" overnight (power outages, network issues, etc.)
Anyone have any ideas?
Thanks!
View 5 Replies
View Related
Jul 25, 2001
Help! I am getting a Job failure for a DB Backup Job when I open up the
EM -> Management -> SQL Server Agt -> Jobs
When I check the SQL Logs, there is no Error message, nor is there an error message in the DB Maintenance Plan History. I checked the Server to make sure the DB was being backed up, and the current file is there. Does anybody have any suggestions? Thanks! (make me look good to the rest of my group!)
View 5 Replies
View Related
Dec 14, 2005
I have a DB Maintenance plan created that performs optimizations, integrity checks, and a database backup. The optimizations and integrity checks work fine, but the backup job fails. The backup job is to write the .bak file to a network share.
When I change the owner of the job to "sqluser", the job fails at step 0 withe the following error:
The job failed. Unable to determine if the owner (rsnsqluser) of job DB Backup Job for DB Maintenance Plan 'Online Database Server Maintenance Plan' has server access (reason: Could not obtain information about Windows NT group/user 'rsnsqluser'. [SQLSTATE 42000] (Error 8198)).
Checked with the system admin for this server and sqluser has priviledges to do everything, including write to the network share.
So, now I change the owner of the same maintenance job from sqluser to "sa". I now get this error:
Executed as user: RSNsqluser. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Checked the settings for MSSQLSERVER and SQLSERVERAGENT in Control Panel. Everything seems to be in order according to the other threads I have read.
For grins, I tried to ensure the sqluser password on the SQL Server Agent was correct. When I try to enter the new password I get this:
sql server agent startup account could not be verified
I click yes (it asks if I want to continue anyway).
Is the password not being registered properly in the agent?
One other thing, the sqluser user is listed under Security|Logins as connecting with Windows Authentication. We have several other servers that are set up the same and work fine. What am I missing? I'm pulling my hair out! :eek:
View 6 Replies
View Related
Dec 25, 2007
Backup job for User databases is failing.
I found below errors
View job history
Step 0:
The job failed. The job was invoked by schedule 4(DBMP_User). The last step to run was step 1(subplan)
Step 1:
Message: Executed as user ServernameSystem. The package execution failed. The step failed
Appln-event log:
SQL server scheduled job DBMP_User failed. Invoked on 2007-12-24 , the job was failed.
Sql server error log
Database backed up. DBname creation,date()time€¦€¦paged dumped 8434659,first LSN: 21126:101410:48,last LSN :21128:933:1, number of dump devices:1, device information: file=1,type=disk (E:MSSQLBACKUP )
This is an informational message only. No user action is required
Error log:
Date:
Log: SQL Agent (current €¦)
Message:
(396) An idle CPU condition has not been defined-OnIdle job schedules will have no effect.
Please advice how to proceed?
View 3 Replies
View Related
May 11, 2006
Hi All,
I have a small script that is failing :
BACKUP DATABASE CorporateComplaints
TO DISK = 'E:MSSQLBACKUPInsight_Dump.BAK'
WITH INIT
-- Declare the variable to be used.
DECLARE @MyCounter INT
DECLARE @MySpid INT
DECLARE @MYSQL varchar(50)
-- Initialize the variable.
SET @MyCounter = (SELECT Count(spid) FROM SysProcesses WHERE SysProcesses.dbid =
( SELECT dbid FROM SysDatabases WHERE SysDatabases.name = 'CorporateComplaints2' ))
print'mycounter '+ cast(@MyCounter as varchar (5))
-- Test the variable to see if the loop is finished.
WHILE (@MyCounter > 0)
BEGIN
-- Kill process.
EXEC sp_refreshview ActiveProcessesView
SET @MySpid = (SELECT Min(Spid) FROM ActiveProcessesView)
SET @MYSQL = 'KILL ' + CAST (@MySpid AS Varchar )
EXEC (@MYSQL)
-- Increment the variable to count this iteration
-- of the loop.
SET @MyCounter = @MyCounter - 1
END
GO
RESTORE FILELISTONLY
FROM DISK = 'E:MSSQLBACKUPInsight_Dump.BAK'
RESTORE DATABASE CorporateComplaints2
FROM DISK = 'E:MSSQLBACKUPInsight_Dump.BAK'
WITH REPLACE,
MOVE 'CorporateComplaints_Data' TO 'E:MSSQLDATAcorpcomps2data.mdf',
MOVE 'CorporateComplaints_Log' TO 'F:MSSQLDATACorpcomplaints2Log.ldf'
error event id 17055
Can any body help wht is wrong in script. I dont understand what is there in middle part its killing some process. what is need.
what I can usderstand its taking a backup of database CorporateComplaints and restoring it to CorporateComplaints2.
View 2 Replies
View Related
Feb 7, 2007
Environment: SQL 2005 Workgroup Ed. (part of SBS 2003 R2 Premium)
I have a database attached to SQL 2005 that is on my D: drive. I'm trying to run a full backup of this database to a backup device also on D:. The first time I ran the backup it worked fine, then I installed SQL 2005 SP1 and now it's failing. The error message is:
quote:
Backup failed for Server 'MYSERVER'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Write on "My Backup Device(D:BackupMyBackupDevice.bak)" failed: 112(There is not enough space on the disk.)(Microsoft.SqlServer.Smo)
In the event viewer, the following events are logged in the Application log:
quote:
Source: MSSQLSERVER
Category: (6)
Event ID: 3041
Description:
BACKUP failed to complete the command BACKUP DATABASE MyDatabase. Check the backup application log for detailed messages.
quote:
Source: MSSQLSERVER
Category: (2)
Event ID: 18210
Description:
BackupMedium::ReportIoError: write failure on backup device 'D:BackupMyBackupDevice.bak'. Operating system error 112 (There is not enough space on the disk.).
If I do the backup to the MSSQL default backup directory (on C: ), the backup completes successfully. If I try to back up the master database (on C: ) to the backup directory I created on D:, the backup completes successfully. If I try to back up the master database to the MSSQL default backup directory (on C: ), the backup completes successfully.
The SqlServer and SqlAgent processes are both running under a domain user account, and that account has full control of my backup directory on D:. I should also mention that my D: drive has over 100GB free, and the database is only 330MB. It is a simple database model.
I've searched all day today trying to find the solution to this issue, and I can't find anything relevant. Could someone please help me!!! I'm about at my wit's end!
Thanks in advance,
Greg
View 20 Replies
View Related
Oct 15, 2015
In one off my production box, we are notable to take a backups of MSDB . When i look at the error, it is failing locate allocation unit ID.. complete error as below
Msg 2533,Sev 16,State 1, Line 36 : Table error : Page (1:111720 ) allocated to object id 110623437, index ID 1, Partition ID 72057594043432960, alloc unit ID 72057594044874752 (type-inrow data) was not seen. This page is invalid or may have an incorrect alloc unit ID in its header.[SQLSTATE 42000]
Due this failure, we are unable to take the backup of MSDB database and our integrity check and reindex jobs also failured with the same.Also, I could see events of I/O issues with underlaying hard dirve with following name
DeviceHarddisk0DR0,has a bad block.
1. I dont no what could happen if restart my server, Question is: Does it recognize MSDB during server statup.
View 9 Replies
View Related
Jan 4, 2008
Good morning,
We get frequent (1 out of 3) "sqlmaint.exe failed" errors during backups for one of our larger databases (40GB). So i removed the maintenance plan and put in a custom backup job to get a better error msg in the log:
BackupMedium::ReportIoError: write failure on backup device 'F:MSSQLmydb_2008_1_3_21_45.BAK'. Operating system error 112(There is not enough space on the disk.).
However, we have PLENTY of space on disk:
LOG DISK - NTFS - used space = 2GB, free space = 81GB
BACKUP DISK - NTFS - used space = 29GB, free space = 82GB
So even if it doubled in size, there would still be 50GB free.
Any ideas?
Thanks!!
View 6 Replies
View Related
Jan 5, 2005
Folks, this is the error i get when backing up user db to local disk:
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
10 percent backed up.
Connection Broken
If i copy a file (2gb) to local disk from network it works. The disk have much space. Any ideas;;; :confused:
I get the same error when backuping up even MSDB to a network path using UNC [\].
View 9 Replies
View Related
Oct 7, 2005
Recently I created a new db in production and restored it from test. Ihave a nightly backup job that backs up all user databases. The backupfor this new db is failing.The message in the error log is2005-10-04 00:13:47.65 backupBACKUP failed to complete the command BACKUP DATABASE [MTUDD_GEMINI]TO DISK = N'd:sqldataMSSQLBACKUPMTUDD_GEMINI MTUDD_GEMINI_db_200510040013.BAK'WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMATI notice that the file name seems to have a space in it between thedatabase name and the end of the backup _db and a space also appears atthe end of the directory name.I can perform a backup successfully using the backup task under alltasks under the database via Enterprise Manager.Anyone seen this? Any suggestions other than backuping up the dbmanually, dropping the database, recreating it, and restoring it fromthe backup?-- Mark D Powell --
View 3 Replies
View Related
May 16, 2008
FYI: I've posted this on a couple of forums and haven't gotten any response. I hope someone here can help since this is way past due.
First I'll give a little background on our situation.
Log Shipping and Replication are out, so I am scripting a backup locally, an xcopy to a remote box, and then a restore.
In the early stages of this, I'm trying to do 3 databases. 2 of them work fine alone. It's when I add the 3rd one that I have a problem. I noticed that in the 2nd stored procedure that I probably need to take out the WITH REPLACE if I'm dropping it beforehand as well. I don't have time to test it on this box until later tonight. I don't think that's the issue because it was doing the same thing before I added the drop. I'm overwriting the .txt file so I don't have the exact error that it's giving. I believe it's something similar to "Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation." I believe it also said [SQLSTATE 42000].
Now for the code. Props to Tara and the code she's put online.
Any help would be appreciated and I'll be glad to help answer questions related to what I've got.
1st Step:
Agent Job scheduled to call stored procedure
EXEC sp_backup_user_dbs3
2nd Step (The code for that stored procedure is):
CREATE PROC sp_backup_user_dbs3
AS
SET nocount ON
DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss
DECLARE @cmd SYSNAME -- stores the dynamically created DOS command
DECLARE @Result INT -- stores the result of the dir DOS command
DECLARE @RowCnt INT -- stores @@ROWCOUNT
DECLARE @DBName SYSNAME
DECLARE @filename VARCHAR(200) -- stores the path and file name of the BAK file
DECLARE @loglogical VARCHAR(1000)
DECLARE @datalogical VARCHAR(1000)
DECLARE @restoreData VARCHAR(255)
DECLARE @restoreLog VARCHAR(255)
DECLARE @backupFile VARCHAR(255)
DECLARE @physicalNameData VARCHAR(255)
DECLARE @physicalNameLog VARCHAR(255)
DECLARE @physicalNameDataStripped VARCHAR(255)
DECLARE @physicalNameLogStripped VARCHAR(255)
DECLARE @ExecStr NVARCHAR(4000)
DECLARE @strSQL VARCHAR(1000)
DECLARE @restoreToDataDir VARCHAR(255)
DECLARE @restoreToLogDir VARCHAR(255)
DECLARE @path VARCHAR(100)
SET @path = 'I:ackupMoveTo14'
--we need to delete all the old backup files from the I:ackupMoveTo14 folder
-- Build the del command
SELECT @cmd = 'del ' + @path + '*.BAK' + ' /Q /F'
--PRINT @cmd
EXEC master..xp_cmdshell @cmd,
NO_OUTPUT
CREATE TABLE #whichdatabase
(
dbname SYSNAME NOT NULL
)
INSERT
INTO #whichdatabase
(
dbname
)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] IN ( 'db1', 'db2')
ORDER BY [name]
-- Get the database to be backed up
SELECT TOP 1 @DBName = dbname
FROM #whichdatabase
SET @RowCnt = @@ROWCOUNT
-- Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 0 BEGIN SELECT @filename = @Path + '' + @DBName + '.BAK' BEGIN backup log @dbname
WITH truncate_only
END
-- Backup the database
BACKUP database @DBName TO disk = @filename
DELETE
FROM #whichdatabase
WHERE dbname = @DBName
-- Get the database to be backed up
SELECT TOP 1 @DBName = dbname
FROM #whichdatabase
SET @RowCnt = @@ROWCOUNT
-- Let the system rest for 5 seconds before starting on the next backup
WAITFOR delay '00:00:05'
END
DROP TABLE #whichdatabase
SET nocount OFF BEGIN
SET @cmd = ''
SET @cmd = 'xcopy I:ackupMoveTo14*.BAK \RemoteServer /C /Y' EXEC master.dbo.xp_cmdshell @cmd
END BEGIN
EXEC [RemoteServer].master..usp_restoreDbsFromDir2
END
RETURN 0 GO
3rd Step(the code for the usp_restoreDbsFromDir2 on the remote server):
CREATE PROCEDURE usp_restoreDbsFromDir2
AS
SET NOCOUNT ON
DECLARE @dbname varchar(255)
DECLARE @loglogical varchar(1000)
DECLARE @datalogical varchar(1000)
DECLARE @physicalName varchar(255)
DECLARE @physicalFileName varchar(255)
DECLARE @restoreData varchar(255)
DECLARE @restoreLog varchar(255)
DECLARE @backupDisk nvarchar (255)
DECLARE @physicalNameData varchar(255)
DECLARE @physicalNameLog varchar(255)
DECLARE @physicalNameDataStripped varchar(255)
DECLARE @physicalNameLogStripped nvarchar (255)
DECLARE @rowCnt int -- @@ROWCOUNT
DECLARE @ExecStr NVARCHAR(4000)
DECLARE @strSQL varchar(1000)
DECLARE @spidstr varchar(8000)
DECLARE @cmd sysname
DECLARE @bkpFile nvarchar(1000)
DECLARE @sql nvarchar(4000)
DECLARE @restoreDir varchar(255)
DECLARE @PhysicalDataPath varchar(255)
DECLARE @PhysicalLogPath varchar(255)
SET @restoreDir = 'F:MSSQLBACKUP'
-- Get files sorted by date
SET @cmd = 'dir ' + @restoreDir + '*.BAK /OD'
CREATE TABLE #Dir
(DirInfo VARCHAR(7000)
) -- Stores the dir results
CREATE TABLE #BackupFiles
(BackupDate varchar(10),
BackupFileName nvarchar(1000)
) -- Stores only the data we want from the dir
CREATE TABLE #RestoreFileListOnly
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
[Size] numeric(20,0),
[MaxSize] numeric(20,0)
)
INSERT INTO #Dir
EXEC master.dbo.xp_cmdshell @cmd
INSERT INTO #BackupFiles
SELECT SUBSTRING(DirInfo, 1, 10), SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #Dir
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 1 AND DirInfo NOT LIKE '%<DIR>%'
-- Get the newest file
SELECT TOP 1 @bkpFile = BackupFileName
FROM #BackupFiles
ORDER BY BackupDate DESC
SET @rowCnt = @@ROWCOUNT
-- Iterate throught the table until no more databases need to be backed up
WHILE @RowCnt <> 0
BEGIN
SET @cmd = @restoreDir + @bkpFile
INSERT INTO #RestoreFileListOnly
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @cmd + '''')
--get the dbname from the bkpFile name
--SET @strSQL = CHARINDEX('_db_', @bkpFile)
--SET @dbname = LEFT(@bkpFile, @strSQL - 1)
SET @strSQL = CHARINDEX('.bak', @bkpFile)
SET @dbname = LEFT(@bkpFile, @strSQL - 1)
--PRINT @dbname
--IF @@ROWCOUNT <> 2
-- RETURN 3
SET @backupDisk = @restoreDir + @bkpFile
SELECT @datalogical = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'D'
SELECT @loglogical = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'L'
SELECT @PhysicalDataPath = PhysicalName
FROM #RestoreFileListOnly
WHERE Type = 'D'
SELECT @PhysicalLogPath = PhysicalName
FROM #RestoreFileListOnly
WHERE Type = 'L'
SELECT @strSQL = 'alter database ' + @dbname + ' set offline with rollback immediate'
--alter database MyDatabase set offline with rollback immediate
--PRINT @strSQL
EXEC (@strSQL)
SELECT @strSQL = 'DROP database ' + @dbname
--alter database MyDatabase set offline with rollback immediate
--PRINT @strSQL
EXEC (@strSQL)
--restore the database
SELECT @strSQL = ''
SELECT @strSQL = @strSQL + 'RESTORE DATABASE ' + @dbname + CHAR(10)
SELECT @strSQL = @strSQL + 'FROM DISK = ''' + @backupDisk + '''' + CHAR(10)
SELECT @strSQL = @strSQL + 'WITH' + CHAR(10)
SELECT @strSQL = @strSQL + CHAR(9) + 'REPLACE'
SELECT @strSQL = @strSQL + ',' + CHAR(10)
SELECT @strSQL = @strSQL + CHAR(9) + 'MOVE '''+ @datalogical + ''''+ ' TO '''+ @PhysicalDataPath + ''''
SELECT @strSQL = @strSQL + ',' + CHAR(10)
SELECT @strSQL = @strSQL + CHAR(9) + 'MOVE ''' + @loglogical + '''' + ' TO '''+ @PhysicalLogPath + ''''
--PRINT @strSQL
EXEC (@strSQL)
SELECT @strSQL = 'alter database ' + @dbname + ' set online with rollback immediate'
--alter database MyDatabase set offline with rollback immediate
--PRINT @strSQL
EXEC (@strSQL)
BEGIN
-- Build the del command
SELECT @cmd = 'del ' + @restoreDir + '' + @bkpFile + ' /Q /F'
--PRINT @cmd
-- Delete the file
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
END
--This is supposed to remove the row once done
DELETE FROM #BackupFiles
WHERE @bkpFile = BackupFileName
-- Get the database to be backed up
SELECT TOP 1 @bkpFile = BackupFileName
FROM #BackupFiles
ORDER BY BackupDate DESC
SET @rowCnt = @@ROWCOUNT
--Wait a couple of seconds before starting the next one
WAITFOR delay '00:00:30'
END
Drop TABLE #Dir
Drop TABLE #BackupFiles
Drop TABLE #RestoreFileListOnly
SET NOCOUNT OFF
RETURN 0
GO
View 2 Replies
View Related
Jul 4, 2000
In SQL Server 7.0 I right click on the database, select Tasks, Select backup, then indicate backup of database (complete) to tape. Overwrite (I put tape into NT Server), OK, then it says backup in progress and then I get the message shown in the subject...
Any advice please? I have backed up NT files on this tape before and have tried a second tape...
Thanks much,
Steve Brown
View 1 Replies
View Related
Nov 21, 2013
I have several databases on a 2012 instance that are mirrored to a second server, and log ship to a third server for reporting purposes.Recently, for two of the databases, the log shipping has been failing at least once per day, and sometimes more often.
i have deleted and recreated both the mirroring and log shipped databases on several occasions, but the problem is still happening.The log shipping restore jobs don't get marked as failed in the job history, but if you expand the history you can see errors such as this:
Date21/11/2013 13:00:01
LogJob History (LSRestore_OAK-DCSQLDB1_OtherCarrierLive)
Step ID1
ServerCHERRY
Job NameLSRestore_OAK-DCSQLDB1_OtherCarrierLive
Step NameLog shipping restore log job step.
Duration00:00:30
Sql Severity0
[code]....
Restoring a new backup of the database cures the problem for anywhere between 15minutes and 12 hours, but it always seems to re-occur.I have run DBCC on the source databases with no errors reported, and five other databases have the log shipping working without errors.
View 9 Replies
View Related
Jan 30, 2015
We have an UPDATE trigger that is failing. This seems like a basic task - we want to write a record to a separate tracking table when our main transaction table is updated for any reason. Our assumption is that we have a reference to the data from the "inserted" record that was just updated. The scenario here is that we are running a batch process which READS several thousand records in our transaction table each evening.
We then mark each individual record as processed on the transaction table and expect that the UPDATE trigger will successfully fire (it is not). The version of our trigger listed below shows our attempt to deal with the fact that TransactionID does NOT exist from "inserted." We also have a version of this trigger that deals with INSERTS - it works flawlessly.
ON [dbo].[FPS_Transaction]
AFTER UPDATE
AS declare @trxId uniqueidentifier;
BEGIN TRY
SET NOCOUNT ON
[code]...
View 6 Replies
View Related
Mar 18, 2015
I have created mergse statement using SCD2.where I am inserting the data if my BBxkey is not matching with target and updating the rows if the bbxkey is matching and rowchecksum is different.
Working of Store procedure
There are 2 scenario covered in this procedure on the basis of that ETL happening.
There are 2 columns deriving from source table at run time, one is BBxkey which is nothing but a combination of one or more column or part of column and another column is a Rowchecksum column which is nothing but a Hashvalue of all the column of the tables for a row.
Merge case 1:-WHEN NOT MATCH THEN INSERT
If source BBxkey is not there in Archive table that means if BBxKey is null then those records are new and it will directly inserted into Archive table.
Merge case 2:-WHEN MATCH THEN UPDATE
If Source.BBxkey=Target.BBxkey && Source.Rowchecksum<>Target.Rowchecksum then this means source records are available in Archive table but data has been changed, in this case it will update the old record with latestversion 0 and insert the new record with latestversion 1.
my sp failing when source having more than 1 same bbxkey.
error [Execute SQL Task] Error: Executing the query "EXEC dbo.ETL_STAGE_ARCHIVE ?" failed with the following error: "The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.".
Sample store procedure
DECLARE @Merge_Out TABLE (Action_Taken varchar(8),
TimeIn datetime,
BBXKey varchar(100),
RowChecksum nvarchar(4000),Col001 varchar(8000),Col002 varchar(8000),
Col003 varchar(8000),Col004 varchar(8000),Col005 varchar(8000),
[code].....
How Can I avoid such failure of my sp.
I want to handle those case where S.bbxkey=T.bbxkey && s.rowchecksum=t.rowchecksum
View 2 Replies
View Related
Nov 29, 2013
I am trying to do a bulk import of data from XML into sQL.
My query returns no errors but no data gets imported.
Here is my XML
?xml version="1.0" encoding="utf-8"?>
<status>
<connection_status>successful</connection_status>
<operation_status>successful</operation_status>
<CustomerDeposits>
[code]....
View 2 Replies
View Related
