Backup On Local Disk Failing! Urgent Help Plz!
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
ADVERTISEMENT
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
Nov 6, 2007
Hello,
SQL 2000 backup to C dirve fails with event 17055 Operating system error = 5 access is denied. Creating and running a backup job wizards errors with SQL State 42000, Deveice error or device off line. SQL is running as LocalSystem. The backup destination has every Windows group added with Full Control.
Thanks
View 4 Replies
View Related
May 12, 2015
Can we backup a DB from SQL Server 2012 (Ent. edition) two node cluster to a local disk ?. Is it possible ?
View 7 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
Feb 13, 2002
Sql Guru's,
I have some DTS packages some times failing.one day sucess and next day it's failing. The following error showing.
DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147217900 (80040E14) Error string: OLE DB provider 'SQLOLEDB' reported an error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 7399 (1CE7) Error string: OLE DB provider 'SQLOLEDB' reported an error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7312 (1C90) Error string: [OLE/DB provider returned message: Timeout expired] Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRu... Process Exit Code 1. The step failed.
Any body help me. This is very urgent
View 2 Replies
View Related
Feb 9, 2004
Hi,
I use the Transact-SQL BACKUP statement in Visual Basic to backup my local MSSQL Database. It give me this error
Error 3041
BACKUP failed to complete the command BACKUP DATABASE [BCFPC] to BCFPCBKP
I already created a backup device called BCFPCBKP and it is backup to the disk.
I tried to run the same BACKUP statement in SQL Query Analyzer and it worked fine. I tried to run my VB application in another PC. It worked fine when i use this command remotely. Can anyone tell me what's the problem?
Thanks in advance
regards,
M.Y. Yap
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
Sep 25, 2000
Is there any benefit in creating a backup device and using that in the backup statement over just using a disk file in the backup statement. It seems like extra work to create the backup device with sp_addumpdevice with the file location. Whereas I could just specify the file location right in the BACKUP statement by specifying DISK as the backup device.
View 1 Replies
View Related
Jul 20, 2005
In SQL 2000, is there an advantage to defining and using a backupdevice versus just backing up to disk?Currently, I've got a SQL Backup job set to run once per day, withtransaction log backups running every hour. The db and backups arewritten directly to folders on an external disk array. I've neverconfigured an actual Backup Device within SQL. Does creating a BackupDevice offer any advantages not available with my current backupmethod?Thank you for any help.Jason
View 1 Replies
View Related
Jul 27, 2001
When I change the SA password on my server the current Scheduled SQL Jobs no longer work. If I run DTS packages by themselves and not from the Scheduled job window they work fine. I am using the sa as the Owner of the jobs and my SQL Agents services use a Domain Account. If I rebuild the jobs after the sa password is change they work fine. However, I don't want to rebuild 100 jobs every time I change my SA password.How can I fix or get around this problem without rebuilding my jobs?
View 5 Replies
View Related
Oct 10, 2001
Hello,
Yesterday, our server 'failed over' to the passive node in our clustered environment. We are running Windows NT, with MSSQL 7.0. What triggered the fail over, according to the logs, was an error in tempdb. Basically, the error said that tempdb could not be expanded, (could not create any more space for tempdb).
The drive that tempdb database is located on has more than enough room. The tempdb is set at 300M, and can grow to over 700M. The drive that tempdb is on
has over 19Gig of space on it.
One other important piece of information is that the drive where our production database is on and the tempdb is quite full. The 19 gig that is left over represents only about 5 percent of the disk.
Can anyone shed some light on why the sql server would have failed over when trying to expand tempdb, when there was sufficient resources to handle that growth? Am I barking up the wrong tree? Could this be another error? When we restarted the server (both nodes and the disk array) the production database came back as suspect. Most of the stuff in BOL points to the space issue. The production database is around 37 gig.
Any help would be appreaciated
thanks,
jim
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 15, 2007
hi gurus,
can any bdy tell me the procedure to create the Backup Device which is a DISK OF REMOTE PC..
i want to take database backup on remote pc's disk not on local disk or tape
i am using sql server 2005
thanks
in advance
View 3 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 21, 2000
I am getting occasional failures of a SQL Server 7.0 complete backup to disk on a production database. The errors seem to indicate that another process has the disk file open at the time of the backup. The errors contain the following texts : -
'Cannot open backup device'
'Operating System Error=32 Process cannot access file because it is being used by another process'.
The only other process that should access the disk file is an ARCserveIT scheduled job to copy the disk backup to tape but this is completing long before.
Any ideas or suggestions?
View 2 Replies
View Related
Jul 23, 2005
If databases on a physical drive [G:] are fragmented, and the drive isextended by adding more hard drives to the array, does it make sense tobackup and restore the fragmented databases?The Windows Server should be able to find contiguous space for eachdatabase, since it shows 75% free space on the SQL Data drive withoutany file fragments on it.Or will it restore to the original location, in which case does it makesense to delete the databases and restore them from the backups?Thank you very much!
View 1 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
Feb 12, 2007
In SQL Server 2005, via the GUI, I wish to backup a database to an additional disk file (there is already an existing backup disk file for this database), so that I can have more than one backup. I've added the new disk file name, highlighted it, and clicked OK.
I get an immediate error (see below). Note, the 2nd error message is specifying the existing backup disk file, not the new one I'm attempting to create.
"Backup failed for Server 'WCS-DEV-TPA'. (Microsoft.SqlServer.Smo)"
"System.Data.SqlClient.SqlError: The volume on device 'D:Program FilesMicrosoft SQL ServerMSSQLBACKUPWCS_ADV_Longmont.bak' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. (Microsoft.SqlServer.Smo)"
Does anyone know what causes this and how to correct it?
View 11 Replies
View Related
Dec 7, 2001
How do we know the list of all the backups that are present in a particualr backup device like file# and time of backup taken etc for the purose of restore?
When I run :
RESTORE HEADERONLY FROM BackupDeviceName
it doesn't give that info. Can anyone tell me the exact command to list all the files of a backup device so that I can restore the right one??
Thanks.
Shalini.
View 2 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
Dec 28, 2006
hello,all
I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) .
is it possible to do it ?
if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ?
thanks in advanced!
View 1 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
Aug 30, 2001
I am trying to set up a new sql server registration to my local computer.
and there's this error - "General Network Error,Check your network documentation.ConnectionOpen(Connect()).
I have already tried to register the server(the available servers field)
are entered as my computer name,local IP address,(local),"."(ignore the double quote), and even 127.0.0.1,but the same error occured.
When i first installed it into my computer, it work for a while, but ultimately MSSQLServer and SQLServerAgent service stopped even though the MSDTC service is still running
For ur info, the installed software is MSSQL7 7.0 Desktop Edition, in Windows 98 Platform
View 1 Replies
View Related
Feb 4, 2008
What is the percentage of FREE disk space that is needed for a backup? I have backups that are failing with no disk space errors. But there is enough disk. Does SQL Server need a percentage of free space all the time?
Thanks
geri
View 11 Replies
View Related
Mar 1, 2006
I'm getting "Access denied" error when I try to connect to SSIS from my desktop (Database Engine is fine). I'm a member of sysadmin within SQL, as well as an administrator on the server. I don't have any problem when I log on to the server directly via Remote Desktop and open SSIS.
I went into the surface area config tool, and I see that Database Engine and Analysis Services both expand and have a sub tabs to enable Services and Remote Connections, but Integration Services does not expand to a Remote Connections option, just Services. We're running MSDN Enterprise edition.
Does it matter if all the SQL services are using "Local System" for the logon ? I use an administrative logon on my SQL2000 boxes, and was fiddling with them on the 2005 box yesterday, but it seemed to cause more problems, so they are all Local System for now.
Am I missing something obvious ?
View 3 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
