Backup For One New DB Failing In Regular Maintenance Job Error 3201
Oct 7, 2005
Recently I created a new db in production and restored it from test. I
have a nightly backup job that backs up all user databases. The backup
for this new db is failing.
The message in the error log is
2005-10-04 00:13:47.65 backup
BACKUP 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, NOFORMAT
I notice that the file name seems to have a space in it between the
database name and the end of the backup _db and a space also appears at
the end of the directory name.
I can perform a backup successfully using the backup task under all
tasks under the database via Enterprise Manager.
Anyone seen this? Any suggestions other than backuping up the db
manually, dropping the database, recreating it, and restoring it from
the backup?
-- Mark D Powell --
View 3 Replies
ADVERTISEMENT
Aug 5, 1998
Problem:
when running the backup an redirecting it to another server, the backup
fails giving error message 3201 status 16 "dbsvolopen backup device
"servernamedirname$kdevnam.dat" fail to open.
Setup:
HD is shared, have another set of backups running into the same logical
device into a created folder name MSSQLBACKUP.
solution: ?????????
View 1 Replies
View Related
Mar 21, 2008
When i try to restore my DataBase in SQL, my SQL2005 Express show me an error image it say: An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.Express.ConnectionInfo)
Aditional Information: no se puede abrir el dispositivo de copia de seguridad 'C:DBMuOnline.bak' error del sistema
operativo 5 (Acces is denied)
FIN ANOMALO de RESTOREHEADERONLY. (Microsoft SQL Server, Error: 3201)
View 3 Replies
View Related
Nov 20, 2001
Hi, I recently migrated from SQL*Server v7.0 to v2000. The maintenance routines on the new v2000 was set up (this is also a new server - completely new machine) are not running. When they attempt to run, I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
Consequently, no backups no repair, the only thing in the maintenance routine that does work is the rebuilding of indexes. Any ideas would be greatly appreciated.
Thanks in advance,
Nancy
View 4 Replies
View Related
Jan 26, 2001
Hi. I'm using the db maint. plan to do database & trans.log backups, and it's failing on my databases that are set to use the 'simple' recovery method (similar to 'trunc. log on checkpoint'). The SQL Agent job then shows up as failed, although what's really happened is that it's just skipped over the databases that are in that mode.
To clarify this, regular database backups complete successfully, while trans.log backups fail due to the databases set to simple recovery.
Does anyone know how to 'fix' this?
Thx,
Larry
View 2 Replies
View Related
Apr 1, 2008
Hello. I setup a new maintenance plan for my SQL2005 SP2 server to do a full backup and transaction log backup on all databases (System and 3 User defined).
Every time I execute the maintenance plan, I get the following error in the job history, and no backups are made:
Message
Executed as user: AD-ENTMKTSQLService. ...00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:13:35 PM Error: 2008-04-01 12:13:45.09 Code: 0xC00291EC Source: {AE791AB6-1317-4803-9C53-444B45DAD533} 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. End Error Warning: 2008-04-01 12:13:45.09 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2008-04-01 12:13:45.14 Code: 0xC0024104 Source: Back Up Database (Full) ... The package execution fa... The step failed.
_____________________________________________________
The MKTSQLSERVICE account is the account setup for the SQL Server Agent Service to use, is in the Administrators group of the server OS, and has been granted SA in SQL2005.
What am I missing???
Thanks!
View 4 Replies
View Related
Sep 2, 2002
Hi all,
I've just set up 2 new SQL 7.0 servers, and my new maintenance jobs - backups, optimisations, consistency check jobs etc - are all mysteriously failing. I've created them both with the Maintenance Wizard, and again by hand. I've attempted manual and scheduled runs. All to no avail. Nor do they populate the sysmainthistory table, although they're configured to do so. The mystery is that we have successfully installed some user DTS processes, and THEIR jobs work. In order to resolve the problem, I've:
(1) ensured the Agent service is running;
(2) ensured adequate space on the drive etc;
(3) verified that sqlmaint.exe exists in Mssqlinn;
(4) unchecked the 'attempt to repair minor errors' box (I read this was a known cause of the problem);
I've got some clues to go on, but nothing conclusive;
'sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.'
Executed as user: NT AUTHORITYSYSTEM. sqlmaint.exe
failed. [SQLSTATE 42000] (Error 22029). The step
failed.
Additionally, both servers hold databases that I imported from another server-INCLUDING the msdb, and BOTH have the same problems and same error messages. This cannot be a coincidence.
Anyone have any ideas?
Thanks in advance,
JB.
View 1 Replies
View Related
Jan 17, 2005
Hi there,
I have a Database maintenance plan (DMP) that always fails!
The plan reorganises data and index pages and checks database integrity.
The plan covers several databases, and it always fails on the same database.
The error message (in the DMP history) is the following: "[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: This server has been disconnected. You must reconnect to perform this operation."
The odd thing is that the DMP is locally executed, so I don't see why the network could be an issue here.
Thank you for your time!
gilles25
View 1 Replies
View Related
May 14, 2007
Suppose I have a 3rd party SQL backup software to backup my databases, can I also setup a stand-by server for log shipping?
I'm not sure if this is possible? If not, is SQL mirroring a choice instead of log shipping?
Thanks a lot!
View 1 Replies
View Related
May 1, 2008
Environment: SQL Server 2005 Enterprise Edition x64, 3 server cluster. Two active servers with seperate instances and one passive server. SQL Server was installed on the two active servers.
Problem: When I fail over either of my instances to the passive server in the cluster my maintenance jobs fail to run and there are error messages in the application event viewer "SSIS Subsystem failed to load". I am guessing that all of the needed components are not installed on the passive server? Is this a close guess? If so, exactly what components are missing and do you have to have another license to install them?
Thanks in advance for any advice.
View 2 Replies
View Related
Mar 6, 2015
One of my programmers changed their database from full to Simple recovery. Saw that my job that backs up the Full Recovery mode databases failed, so I moved that database to my Simple database backup job plan and removed it from the Full Recovery job. I am unable to remove the db from the Transaction Log task on the Full Plan because when I try to edit that job "Databases with Simple Recovery will be excluded"
My transaction log backups are still failing with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. 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.Just want to remove that database so my Full Recovery backup job does not try to back it up.
View 2 Replies
View Related
Nov 28, 2007
Maintenance plan for bakcup is failing with "Login failed for user 'sa'. [CLIENT: <local machine>]"
I went to the Maintenance Plan and opened the Subplan. I clicked the "Manage Connections"
It has three tabs:
Name: Local Server Connection
Server: prod
Authentication: SQL Server Authentication
I clicked the Edit and it shows the Connection Properties:
It says: Enter information to logon to the server. "Use a specific Username and Password" is checked. Username is set as "sa" while the password is empty. I typed in the correct password and pressed Ok. When I go back again, the password still shows empty. I tried to run the plan and it again fails. Do you know why it is not showing the password as blank even if I try to save the password.
View 1 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
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
Dec 28, 2007
hello
here is my code int a = Convert.ToInt32(Request.QueryString["ProductID"]);
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand command4 = new SqlCommand("myStoredProcedure", conn);
command4.CommandType = CommandType.StoredProcedure;
DbParameter param2 = command4.CreateParameter();
param2.ParameterName = "@ProductID";
param2.DbType = DbType.Int32;
param2.Value = a;
command4.Parameters.Add(param2);
param2 = command4.CreateParameter();
param2.Direction = ParameterDirection.Output;
param2.ParameterName = "@UserName";
param2.DbType = DbType.String;
command4.Parameters.Add(param2);
conn.Open();
command4.ExecuteReader();
conn.Close();
Label3.Text = command4.Parameters["@UserName"].Value.ToString();
}Exception Details: System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.Line 48:
Line 49: conn.Open();
Line 50: command4.ExecuteReader();
Line 51: conn.Close();
and here is my stored procedure ALTER PROCEDURE dbo.myStoredProcedure
--
(@ProductID int,
@UserName varchar(255) OUTPUT)
AS
SET @UserName = (SELECT UserId FROM Products WHERE ProductID = @ProductID)
what is wrong? i never seen that error and i don't know from where could it came from
please help me,
thanks
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
Aug 26, 2003
I have log shipping setup to 2 different servers. Every once in a while I get the
Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'E:fac.dat'. Device error or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.
Checked MSDN which said it was a permissions error, which isn't right, because the same account works for this db and several others all the time. Anyone else seen this?
View 1 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
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
View Related
Jan 24, 2002
Are there any known issues using Maintenance Plans to backup DBs in SQL Server 7.0
Thanks
View 1 Replies
View Related
Feb 23, 2000
I am doing full backups on 25 databases nightly using the SQL maintenance plan. Recently I have started getting the backup process hung on one database, so the whole plan does not finish. The job shows as executing, but nothing is being done, and nothing has been written to disk for this database. The SQL logs show a DBCC TRACEON 208, but the process it refers to is using another database. The NT logs do not show anything going on either. Additionally the backup process will not die when it is killed. They only way to make it go away is to stop and start the SQL Server. The system is a quad Pentium 450, 2Gig of ram, and plenty of disk space. It is NT SP5, SQL7 SP1. The DB is about 8.2 Gig. There are no other jobs scheduled to run at the time the backups run. Does anyone have any ideas what would be causeing the process to hang? Also what is a trace flag 208, it is not listed in BOL. Thanks for your help.
View 1 Replies
View Related
Feb 7, 2003
I have a maintenance plan that I use to backup some of my databases and transaction logs. I have "Check Database Integrity" checked. If the database is being used by anyone when the backups are being performed, the check issues an error because the DB cannot be put into single user mode and then skips the backup. In the Plan, I have unchecked the box for "Perform these checks before backing up the database or transaction file" and apply the change. When I go back into the plan, the option is checked again. How can I get around this problem?
Thanks,
Ken
View 1 Replies
View Related
Mar 21, 2002
I have a maintenance plan where I backup the system databases and my application databases every night. Once a week I do a Check Database Integrity. For the Master and MSDB databases, it errors out telling me that the database must be in single user mode. I thought that the maintenance plan would switch the DB to single user mode when it needed to perform its checks. Any suggestions on how to get this to work successfully?
Thanks,
Ken Nicholson
View 1 Replies
View Related
Sep 6, 2001
I'm running sql server 2000. All the backups created through the Maintenance Plan failed (no specific error code), whereas backups created by itself (through database name => All Tasks => Backup database...) run fine. Does anybody have a clue why the Maintenance backup failed or how to trouble shout the problem?
The reason I want to make the backups from maintenance plan to work is that each backup will have a unique name and I can schedule to remove the old backup files from the system.
View 5 Replies
View Related
Jun 21, 2006
Hi,
I have made a maintenance plan to backup a database.
The file name is like this: name_db_200606211209.BAK
isn 't it possible to get the filename to be always the same
(without 200606211209)
I have unchecked the checkbox to remove files older than x-days
but it still appends 2006... to it
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