I used sp_detach_db to back up the database temporarily. I used xp_cmdshell to stop SQLServerAgent, but when I restarted, I was unable to. Then, I tried reattaching the Database running sp_attach_db.
!!!HELP HELP!!! I AM GETTING THE FOLLOWING ERROR:
Server: Msg 5105, Level 16, State 4, Line 1 Device activation error. The physical file name 'Mssql7dataTestDB.mdf' may be incorrect.
Please forgive my ignorance - I'm completely new to MSSQL.
Our office bought a set of Laserfiche licenses and had to also buy MSSQL to handle the database end of things. Part of my job is to make sure everything gets backed up. I want to use Enterprise Manager to back up the databases which seems straightforward enough - even though I've never done it.
Our Network Admin has installed a temporary USB drive to hold our back-ups until a more permanent form of media can be installed. He is using drive compression to maximize the space available. The server sees it as Drive F:
I created a folder on F: to contain my MSSQL Back-ups. Then I opened Enterprise Manager and clicked on SQL Server Group, which contains two databases:
(local) (Windows NT)
and
CYWD_IMAGE/LASERFICHE (Windows NT)
My understanding is that they both have to be backed up.
I clicked on the first one, then selected the Database Maintenence Plan Wizard from the 'Tools' menu. I stepped through the process (choosing 'all databases') until I got to the point where I could specify the back-up directory. I was able to browse right to the folder I created and choose it. I could then finish the process normally. The problem is that when I selected the second database (CYWD_IMAGE/LASERFICHE) and launched the wizard I was unable to browse to my folder. I could choose the F: Drive but no folder showed up under it. Oddly, it would not even show me my D: partition (just thought I'd mention that in case it is somehow relevent).
I don't see any difference in the properties tabs between these two databases. The obvious difference is the name, and that one says (local) and the other does not.
I really don't want to choose to dump the back-up into the root of F:
I am currently investigating how to safely back up the seven related databases that drive Microsoft Team Foundation Server. Microsoft is suspiciously vaigue and inconsistent about how to perform a safe TFS database backup.
This article states "Although SQL Server Management Studio allows you to back up individual databases at a time, restoring from such back ups can cause unexpected results because the databases are related and you risk restoring outdated versions." Here, that requirement is strengthened (search for text in article 'Backing up Databases') "Because this data is stored in seven related databases, you might want to use marked transactions in the transaction logs of each database. Transaction-log marks help guarantee absolute consistency in the distributed data set." but then it weakens the case again a few sentences later... "Databases for Team Foundation Server are loosely coupled; any latency issues generally are limited to relationships between things such as work items and change sets that are created during the backup window." There are various other TFS administrators with the same worry about backing up the TFS databases - see here and here.
This requirement to back up related databases to a consistent point in time is not new in Microsoft products. It seems that BizTalk server had exactly the same complication. The nice thing for the BizTalk administrators was that Microsoft included a stored procedure to manage backup of related databases in this way. Team Foundation server administrators don't seem to be so lucky.
I've read the documentation for distributed transaction marking but it doesn't give me that high-level overview that I'm looking for. In our installation, all of the seven databases are hosted on the same machine and same instance of SQL Server.
My initial misconception about transaction marking was that I assumed TFS itself would routinely insert marks into the transaction logs of the seven databases during its normal operation, at points in time where it knows that their state is consistent. This doesn't appear to be the case. The explanation of the BizTalk stored procedure indicates that the log mark insertions are initiated by SQL server as a DBA task. It seems that the insertion of these named marks is done just prior to performing database log backups. This makes sense, so presumably once SQL Server is told to make the marks, it waits for a point in time at which all transactions are closed across all related databases before inserting the marks simultaneously.
If I've got that overview correct, then my real problem is that I can't find an example of the T-SQL that would actually do this. In pseudocode I'd expect it to be something like...
begin distributed transaction across (list of 7 databases), timeout 300 seconds
insert transaction log mark 'safe to restore to this point' end distributed transaction
I suspect that this page will probably end up having all the information on it I need, but after reading it several times I can't quite nail it. If anyone can help then I'd be most grateful.
I have set up a Database Maintenance Plan that does a nightly backup of all of my databases (about 12 of them) to a network folder. The plan works for about 95% of the job, but most nights there will be at least 1 database which will fail with the following error... BackupDiskFile::RequestDurableMedia: failure on backup device '\myfileserverBackupSQLDatabaseDatabaseNameD atabaseName_db_200610081749.BAK'. Operating system error 64(The specified network name is no longer available.). I know that this is not a permission or storage problem, because it works for most of the job. And a database that fails one night, may work fine the next night only to have a DIFFERENT database fail that night, and sometimes all databases work 100%.
Is there a way to fix this problem? And if not, is there a way to be notified of which specific database in the maintenance plan is failing. The message on the job itsself is very non-descriptive and I have to manually search the logs to find out which databases were successful & which where not. It is very time consuming. Any help with this would be greatly appreciated.
I have a database i am m oving to another server, during the process Iam moving the data and log files to another drive.(Which I have donecountless times before with no problems)The problem I am having is after I restore the database using thefollowing statement :RESTORE DATABASE CMAMSPROD FROM DISK = ’C:Databasename.BAK’WITH STANDBY = ’D:Program FilesMicrosoft SQLServerMSSQLBACKUPDatabasenamedatabasename.STAN DBY’,MOVE ’Databasename_Data’ TO ’D:Program FilesMicrosoft SQLServerMSSQLDataDatabasename_Data.mdf’,MOVE ’Databasename_Log’ TO ’D:Program FilesMicrosoft SQLServerMSSQLDataDatabasename_Log.ndf’I get these errors when trying to restore transaction logs :[SQLSTATE 42000] (Error 3156) Device activation error. The physicalfilename ’C:Program FilesMicrosoft SQLServerMSSQLDataDatabasename_log.ldf’ may be incorrect.[SQLSTATE 42000] (Error 5105) File ’Databasename_Log’ cannot berestored to ’C:Program FilesMicrosoft SQLServerMSSQLDataDatabasename_log.ldf’. Use WITH MOVE to identifya valid location for the file.Has anyone encountered the same problem? This has me stumped,although restoring the tlog with move, and standby works....this isnot how it should work.--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict259104.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=893187
We're trying to create a .bak file to send it to our site's admin to restore it on the server: 1. When I try to do that get this error:http://www.radiorient.net/errors/export_sql.gif Why is that? and how can I fix it? 2. Should we add both files of the db (.MDF, and .LDF) or only the .MDF file? Can please someone help me with this, Thanx.
I just got SQL 7.0 and was curious how other environments run their backup. I know ArcServe has a product that backs up open SQL databases and not sure how reliable this product was. Any thoughts would be appreciated .. thanks.
HiI'm hoping that some could point me in the right direction for "bestpractice" in:1) securely backing up a complete sql7 and 2000 server containing manydatabases.2) backing up an individual database3) Securing the transaction on the hour as a mile stone to return to in theevent of error or system failure.ThanksMark
Hey, please forgive me if my infomation source was incorrect. But a friend of mine mentioned to me he had hurd of a program, or code that will make an "install script" of your database. Basically it`ll back up all the tables on your server and if the worst case senario happens then you can run the file like an install script. Is this true? It sounds invalueable! If it isnt true, can someone tell me if it is possible to use code to create a "install script" of your own database? I am using "Microsoft SQL Server".Thanks in advance John
I have August22 full backup and August22 and August23 transactions log backups . I want to get the August23 and August23 full backup. So I performed a restore backup with August22 full backup and all the August22 trn backups. Then I backed up giving it August23 name and repeated the process with the August23 trn backups. But, I get an error saying that the transactions haven't been rolled foward far enough. But, I am not missing any trns! I will really be grateful if anyone would help. thanks.
i want to take a backup a database which is on a different machine to my machine.Iam trying to give unc naming conventions i.e backup to //servername/sharename/file.bak but its not working.iam getting an error saying device error or device offline-access denied.
Anyone could tell me how to do it step by step plz.
When backing up a database in MS SQL 7.0, there is a window that appears in the destination section of the backup window. In this window is where the filename appears that the backup will proceed to and to the right of this window are "add", "remove" and "contents" buttons.
If there are multiple names in this window, will the entire backup go to both files or does it split the backup between the two filenames.
I use SQL Server 2000 and I create scripts on a daily basis to do the following
1. Rename a table 2. Create table with original name and add new field 3. Copy data from renamed table to new table 4. Build indexes ----------------------------------------- execute sp_rename ORIGINAL, OLD GO
CREATE TABLE [dbo].[ORIGINAL] ( [A] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [B] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [C] [int] NOT NULL , [Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
INSERT INTO ORIGINAL (A, B, NOTES) SELECT A, B, NOTES FROM OLD
CREATE CLUSTERED INDEX [ORIGINAL_A_B] ON [dbo].[ORIGINAL]([A], [B] DESC ) WITH FILLFACTOR = 90 ON [PRIMARY] GO -----------------------------------------
ON A TABLE WITH 100,000 RECORDS THIS KIND OF SCRIPT TAKES A LONG TIME TO DO THE INSERT/SELECT PART
I WOULD USE ALTER/ADD TO UPDATE THE TABLES, BUT MY SUPERIOR INSISTS THAT NOT HAVING TEXT FIELDS AS THE LAST FIELD IN THE TABLE WILL CAUSE QUERKY PROBLEMS WITH HIS SQL STATEMENTS DOES ANYONE HAVE ANY KNOWLEDGE THAT THIS COULD BE TRUE?
IF SO, DOES ANYONE HAVE ANY IDEAS FOR DOING THIS SAME PROCESS TO ADD A NEW FIELD (C) BEFORE THE FINAL FIELD (NOTES - TEXT FIELD), AND BACKUP THE TABLE BUT WITHOUT IT TAKING SUCH A LONG TIME ON THE INSERT/SELECT PART?
Forgive me if I'm asking a lot, but I'm lost and these scripts take way too long for what seems like such a simple task.
Here is the situation, I have a web application (asp.net/vb.net) with ms sql 2000 as the database. I have two database server. I would like to backup database 1 with database 2. I would like to do it at intervals and through code. If anyone can give me some ideas on how to do this or any research material, please do.
Sorry this is so trivial, but I don't work with SQL Server too much. I just want to back up a table in one of my databases. I'm using SQL Server 2005, the management studio. Can someone walk me through how to back up a table? Thanks!
Hi, I've recently created a maintenance plan, which backs up my database and transaction log. However I also have a Veritas (Backup Exec for Windows Servers) tape backup which runs at night and backs up my files. It is setup to backup the whole of my MSSQL DATA and BACKUP folders.
Today I went to do a test restore, and found that the tape backup contained nothing in the MSSQL DATA folder, but everything that was in the BACKUP folder. Is this because everything in the DATA folder is in an open state? On checking back, the tape backup has never held anything in the DATA folder. Should I be worried by this, or is it what you would expect, and is the SQL backup sufficient?
I got a .bak file from my friend I want to restore the data base (presently I don’t have any database in my system).
But while I try to restore it I am getting an error
“ System.Data.SqlClient.SqlError: Directory lookup for the file "D:Microsoft SQL Server 2005mydatabase.mdf" failed with the operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)
�
I have installed Microsoft sql server in a different directory Not where he is installed
I can successfully restore the back up files of data base on my own system But not the backup files created from others systems
Every time i try to back up a specific DB i get an error saying it cannot backup because the full text catalog is not permitted because itis not online. It does not matter if i try to backup the db throughSQL of my Backup software. here is the sql log.LogJob History (DB File level Backup.Subplan_1)Step ID1ServerServer NameJob NameDB File level Backup.Subplan_1Step NameSubplan_1Duration00:00:01Sql Severity0Sql Message ID0Operator EmailedOperator Net sentOperator PagedRetries Attempted0MessageExecuted as user: DomainUSer. ...ecute Package Utility Version9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. Allrights reserved. Started: 7:07:43 AM Progress: 2007-06-0107:07:44.14 Source: {B6285A6A-0066-416F-AD9F-F31ED5A68F14}Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTEmsdb..sp".: 100% complete End Progress Error: 2007-06-0107:07:44.49 Code: 0xC002F210 Source: Back Up Database (Full)Execute SQL Task Description: Executing the query "BACKUP DATABASE[DBName] FILEGROUP = N'PRIMARY' TO DISK = N'E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupDBName_200706010707.ba k'WITH NOFORMAT, NOINIT, NAME = N'DBName_backup_20070601070744', SKIP,REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "Thebackup of full-text catalog 'ftcat_documentindex' is not permittedbecause it is not online. Check errorlog file for the reason that full-text catalog became offline and . The step failed.
I'm having an ongoing fallout with the Operations team where I work about database backup strategy. Â They use vSphere (VM ware) to backup all the disks on the servers and included in this are the SQL backups. Â Now I don't really trust this because whenever I ask for a restore, which is very infrequently, they give me an mdf/ldf pair. Â To my mind this shows they are just backing up files and although these files can be re-attached this is not safe, as they were open when the backup was taken. Â Â What I would like to do is use native (or RedGate) backup to create a full backup and have the vSphere pick up the .BAK files.Â
I am currently in a situation where I have been fed to the wolves to say the least.
My task is to take the current SQL server 05 we have running on a drive array partioned as C & D and rebuild it.
I need to have the current database backed up, as well as the unique permissions that are currently present on the database.
I then have to remove the hard drives from the RAID array, and replace them with Brand new ones. The OS will then have to be reinstalled, followed by the nessary updates and finally with SQL server.
I then have to import the database back to the new server build w/ the data and the permissions all intact.
Anyone have any idea of how I can go about doing this?
I have writen a small program in a Query window that runs fine if I hilight and run small chuncks. (I have listed the statements with out the clauses so it is more easly viewed here.)
The problem is if I simply execute the Query window I get massive errors that don’t make sence. I am guessing I need some GO and BEGIN/END statements? But I don’t know where I should use them.
I would really appreciate a few pointers so I can just run the Query window.
drop table SourceFile drop table ReferenceFile
SELECT TOP INTO SourceFile FROM
SELECT TOP INTO ReferenceFile FROM
-- test data INSERT ReferenceFile (AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip) values () INSERT SourceFile (AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip) values (22) -- test data
drop table MATCHTEST SELECT TOP 1 AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip INTO MATCHTEST FROM AddressCleaned_npidata_20050523_20071112
DELETE MATCHTEST
ALTER TABLE MATCHTEST ADD REF_ML VARCHAR(2)
INSERT MATCHTEST () SELECT FROM ReferenceFile r inner join SourceFile s on s.New_Last_Name = r.New_Last_Name
I'm very new in SSIS. I've created 3 packages in the project. sometimes when i modified the project and save/save-all it, when i tried to build (isn't this used to deploy?), I am being asked by this:
Package 1 has been modified outside the source editor. Do you want to reload it?
When I press on yes, all my modifications were not save. If i answered no, the build process stops. i dunno if this is because the build process is already finished or it was terminated because i chose 'NO'. When i tried to rebuild it again, it will ask me the same question.
What is the proper way to save and build the project? When it says ' do you want to reload it?', does it mean reloading the old copy before modification?
Is there an easy simple way to backup all my personal Stored Procedures. In the stored procedures collection, my stored procedures start with "DEF". I would like to have the stored procedures places as text files in a personal backup folder. Is this possible?
Hi. im shortly going to have to submit my project for uni which ive created using sql server. How can i copy everything that ive made so i can submit everything and it can be replicated if necessary. Do i use the backup database task in enterprise manager or do i have to do that and export data or..? ive used tables and stored procedures and a diagram btw. thanks for any advice
