Few years daily backup-job was fine
but now it gives error:
Msg 3023, Level 16, State 1, Server srvr1, Procedure , Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC, bulk copy,
SELECT INTO, and file manipulation (such as CREATE FILE) operations on a
database must be serialized. Reissue the statement after the current backup,
CHECKALLOC, or file manipulation operation is completed.
Msg 3013, Level 16, State 1, Server srvr1, Procedure , Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation
terminating abnormally.
==================
any ideas what could be reason of this error... and maybe how to fix it?
DB file size - about 5GB
free disk space - about 8GB (+5GB for backup file)
Hi I'm an experienced programmer, but just don't have much experience with ASP or MSSQL. I inherited a website and now need to set up an automated backup system for a fairly large (40MB) MSSQL database hosted by a 3rd party provider of reasonable quality with ASP and SQL Server 2000. Let's avoid ASP.NET solutions since the website I inherited is written in "straight up" ASP (old school).
I have access to the database, login etc. I'm thinking I should combine an ASP script with the database connectivity and write daily/weekly backups to a dir on the hosting server.
ok, here is an interesting one. We have multiple jobs that run the following SQL Backups to Permanent Backup devices.
Backup Database DBX to DBXFull with NoInit - 1 am Backup Database DBX to DBXDiff with Differential, NoInit - 12 pm Backup Log DBX to DBXLog with NoInit - every hour on the hour
the devices are in path E:SQLBackups<databasename> E: is on a SAN
Every Night E: is backed up by legato, When Legato Starts to backup the directory, at the hour a transaction log backup occurs. The Backup Fails and The Database Is Taken OFFLINE.
Questions are as follows. What is causing the db to be taken offlline? How can i stop the db from being taken offline while still mainting my current backup strategy.
Temporarily we have stopped tlog backups during the directory backup phase (legato) and then resume them when it is complete.
Difficulty Level: we will not be using Legato's SQL Plugin for the backups, they have to be performed with jobs and SQL Backup.
Can SQL 2000 initiate backups to 3rd party applications (like LiteSpeedand BrightStor's SQL Backup Agent)? My goal is to be able to managebackup and restores completely through SQL Enterprise Manager and QueryAnalyzer without having to logon to the Server and using the 3rd partybackup application's software. Is this possible through SQL?Thank you,Stephanie
Hello, we are running MSSQL 2005 express on a windows 2003 machine. I have looked in SQL Server Management Studio for close to an hour and could not find a way to setup automatic backups of databases. Where is this feature, and short tutorial would be great. Thanks!
Hi, I am using an Enterprise Manager and i have created a Database Maintainance Plan, can anybody guide me as to how to create backup on a remote machine.
I also mapped that device(say M/c B) onto my server box(M/c A) but i am not able to see that particular location in my backup activity screen.
My name is W. Curtis Preston, and I'm the author of "Backup & Recovery"(formerly "Unix Backup & Recovery") from O'Reilly & Associates.(http://www.oreilly.com/catalog/unixbr/ )We're updating the book for 2006 and adding a chapter on SQL Serverbackup and recovery. Someone has already written what appears to be astrong chapter. The problem is that I'm not qualified to review it,since I'm not that familiar with SQL ServerCan anyone in this group step up to the plate and review the SQL Serverchapter? You would be mentioned in the acknowledgments of the book andreceive an autographed copy of the book.We're looking for two tech reviewers.
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
i was planning to create a database migration tool .. its a certain database of a DMS (document management system) to another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..
they have different table structures and names . . :D
i was thing of what language shall i use.. or what language is the best to work on this kind of project :)
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
I am currently work on mssql 6.5. On my workstation, I have mssql 6.5 cient software.
However, I would like to install mssql 7.0 server on my nt workstation and work with it to become familiar with 7.0. Can I install mssql 7.0 server on my nt workstation? Can mssql 6.5 client coexist with mssql 7.0 on the same machine if they are in different directories?
Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.
The scrpt looks like this:
--------------------------------------------------------------------------------------------------- USE [master] GO IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME') EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD' GO GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME] GO GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME] GO GRANT CONNECT SQL TO [SSDBUSERNAME] GO GRANT CONTROL SERVER TO [SSDBUSERNAME] GO GRANT CREATE ANY DATABASE TO [SSDBUSERNAME] GO USE [master] GO If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME') ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD' GO GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME] GO GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME] GO GRANT CONNECT SQL TO [SSDBUSERNAME] GO GRANT CONTROL SERVER TO [SSDBUSERNAME] GO GRANT CREATE ANY DATABASE TO [SSDBUSERNAME] GO USE [master] GO IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ') DROP DATABASE [ISIZ] GO USE [SurveyData] GO exec sp_adduser 'SSDBUSERNAME' GRANT INSERT, UPDATE, SELECT, DELETE TO SSDBUSERNAME GO USE [SurveyManagement] GO exec sp_adduser 'SSDBUSERNAME' GRANT INSERT, UPDATE, SELECT, DELETE TO SSDBUSERNAME ---------------------------------------------------------------
I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.
I was wondering if somebody there could help me with this problem?!
I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.
Current Environment is MSSQL 2000 32-bit with current Service Packs.
I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.
I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.
Upgrade options:
1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via: a. DETACH / ATTACH b. BACKUP / RESTORE c. Is one method more advantageous relating to the end result? 2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases? 3. If you have performed this migration and have relevant experience please pass them along.
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
DB is developed on local computer with MSSQL 2005 Express. My host is on MSSQL 2005 workgroup. Are they compatible, because I am getting errors? Is my approach wrong?
I have tried several approaches.
A) I created a backup of database on my local, then placed a copy on the server. Then I tried to restore through Server Management Studio. I get this error.
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
B: I also have tried copying the database. I put it in the same path as the other databases that can be read with server management studio on the server. Then, tried to get to it through server managements studio and it did not appear. So I tried to attach it. Then I received this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'MROACH1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'LodgingDB'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
C: I have also tried opening the Database, and back up file through Server Management Studio. without success.
D: I also tried Windows and Software update at microsoft update, but no updates were recommended for Version on Server.
I'm surprised this is so hard. My original data base was created in same family of software. 2005 MS SQL Express. I could use some direct help from someone experienced with this. Am I doing it wrong or are the DB versions incompatible.
On the SQL Server the Event Viewer shows the same messages and errors every evening between 22:05:00 and 22:08:00. The following information messages are shown for every database:
"I/O is frozen on database <database name>. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."
"I/O was resumed on database <database name>. No user action is required."
"Database backed up. Database: <database name>, creation date(time): 2003/04/08(09:13:36), pages dumped: 306, first LSN: 44:148:37, last LSN: 44:165:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{A79410F7-4AC5-47CE-9E9B-F91660F1072B}4'}). This is an informational message only. No user action is required."
After the 3 messages the following error message is shown for every database:
"BACKUP failed to complete the command BACKUP LOG <database name>. Check the backup application log for detailed messages."
I have added a Maintenance Plan but these jobs run after 02:00:00 at night.
Where can I find the command or setup which will backup all databases and log files at 22:00:00 in the evening?
SQL Server 2008 r2 - 6 GB memory...I attempted a backup on a 500GB database but it was taking way too long. I checked the resources on the box and saw the CPU at 100%. I checked the SQL Server activity log and saw a hung query (user was not even logged on) that had multiple threads so I killed it and now the CPU utilization is back to normal.
Trouble is, now all of the threads in the activity monitor for the backup show 'suspended' and the backup appears to be not doing anything.
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?
Using Ola Hallengren's scripts I do a full backup of a database on a Sunday. Then differential backups every 6 hours and log backups every hour. I would like to keep a full week of backups based off the full backup done on Sunday. Is there a way for me to clear out the diff and log folders after the successful full backup on Sunday nights?
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
Windows 2003 backup utility uses the shadow copy option that allows it to copy open files. Therefore, can I use this utility to backup the .mdf and .ldf files for my SQL 2000 database? I can then attach the .mdf files if I need to restore the database to another server. Can anyone tell me if this is safe? I've tried it and it worked but I'm worried there maybe some lurking danger in using this approach.
We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin] GO IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL DROP PROC usp_DeleteBackup GO
Using SQL Server 2005 Server Management Studio, I attempted to back up a database, and received this error:
Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the satement after the current backup or file manipulation is completed (Microsoft.SqlServer.Smo)
Program location:
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv) at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)
Backup Options were set to:
Back up to the existing media set
Overwrite all existing backup sets
I am fairly new to SQL 2005. Can someone help me get past this issue? What other information do I need to provide?
I'm looking to schedule a maintenance plan for my databases which I have done.I'd like this database to be copied to another folder and the name altered to include the file name and the current date time stamp.Is this possible in the scheduled maintenance plan?
