I make a backup file from my database and than I opened it in notepad.
I find some data that I can't reach with query. I searched for this data in every varchar and text column but I don't find that.I scripted all database with data but still not find.
This database is very old and this unavailable data is from 2000. Now the database run on sql 2012 server.
I want to remove this data from the database. I tried to shrink the database or allocate the free space to 0% but nothing happened, the data is still there in the backup and the mdf file too.
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?
We have a situation where we need to restrict access to a sql server data file. That is, to prevent users viewing any tables, stored procedures, etc, in sql server or another tool. We are providing a our database as part of an application install to a customer's site which will run isolated on the customers network. However the application will have sql server logins and the system must still be able to execute stored procedures.
The setup unfortunately cannot be changed and we are trying to think of best implementations for this. Our customers are also working with competitors so we are very conscious about exposing our data structures to anyone outside of our company, hence trying to restrict access. If deployed to a sql instance on the customer site then they will have database administrator accounts on the server.
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?
Hello,All: I have two sql 2000 servers,one for production and the other is for backup server,I used the sql agent to create bakup daily in Sql server Enterprise Manager. Now I want to import the backup data(generated by production server),I don't know how to do it. Is it possible to do it ? anyone can give me a soluation ? thanks in advanced!
I am trying to create sql code that restores a backup of a master database to a new database on the same server. It “seems” to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!
Any and all help is sincerely appreciated.
The master databases from which the backups are made start with “MODTRNMaster”
The databases which are created from the restores start with “M1_” and “M2_”. (We call them training room databases.)
My script for backing up the master databases:
-- Backup the master training database
backup database MODTRNMaster to disk = 'f:kupMODTRNMaster.bak'
backup database MODTRNMaster_IMG to disk = 'f:kupMODTRNMaster_IMG.bak'
backup database MODTRNMaster_MNC to disk = 'f:kupMODTRNMaster_MNC.bak'
backup database MODTRNMaster_VM to disk = 'f:kupMODTRNMaster_VM.bak'
go
This is the restore script for restoring the first training room databases. I’m hoping that there is just something simple that I’m overlooking in these restore statements! J
-- Restore the backup of the master training database into the
-- training room #1 database.
use master go
drop database M1_MSLH go
restore database M1_MSLH from disk = 'f:kupMODTRNMaster.bak' with move 'DEV5_Data' to 'f:mssqldataM1_MLSH.mdf', move 'MM' to 'f:mssqldataM1_MLSH_1.mdf', move 'AMB' to 'f:mssqldataM1_MLSH_2.mdf', move 'DM' to 'f:mssqldataM1_MLSH_3.mdf', move 'IMM' to 'f:mssqldataM1_MLSH_4.mdf', move 'ED' to 'f:mssqldataM1_MLSH_5.mdf', move 'DEV5_Log' to 'f:mssqllogM1_MLSH_log.ldf', recovery
i found that database log file can contain more records after performing backup database statement.
for example:
i create a database and limit the log file to 2mb. then i create a table and insert data.
If i backup the database before i insert data , the database file can contain 192 records unitl the log file is full.
If i don't perform the 'backup database' statement. The 'dbcc sqlperf(logspace)' indicate the utilization ratio is less than 40% after inserting 192 records
why?
I list my code:
Code Snippet create database db_test on primary ( name=db_test, filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf' ) log on ( name=db_test_log, filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf', maxsize=2mb ) go backup database db_test to disk='db_test.bak' --- if i don't execute this line, log file can contain a lot of record go create table db_test..table1(col char(8000)) --insert data to fill up the database log declare @n int set @n=0 while @n<192 begin insert into db_test..table1 values(replicate('a',8000)) set @n=@n+1 end
question 2: i create a database and limit the log file to 2mb. Then i create a table and insert data in an endless loop.
After the inserting operation executing for a while, the 9002 error occurs, indicate the log file for the database is full. But the 'dbcc sqlperf(logspace)' command indicate the unilization ratio is low, and log_reuse_wait_desc in sys.database is 'CHECKPOINT' And I can insert data , and i'm sure the state of log_use_wait_desc is 'CHECKPOINT'.
As i known, the checkpoint can't truncate log under full recovery model. Only the back log operation can truncate the transaction log. So log is not full, why 9002 error is encounterd. and why the log_reuse_wait_desc return 'CHECKPOINT'?
I list my code:
Code Snippet create database db_test on primary ( name=db_test, filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf' ) log on ( name=db_test_log, filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf', maxsize=2mb ) go create table db_test..table1(col char(8000))
--insert data to fill up the database log declare @n int set @n=0 while @n<>-1 begin insert into db_test..table1 values(replicate('a',8000)) end
HiWe have developed and deployed a database which contanis very sensitiveinformation. Our client is now concerned about the security of the backups. In essense, if someone in the organization can get hold of thebackup of the database, he can simply restore it on any sql server inthe world with sa permission.I know Microsoft provides flexibility of adding the "Password" in theBackup t-sql statement but it wouldn't be of much use if the back uptask is saved as a script and password will be written inside thescript.your suggestions are really appreciated!Thanks
We are using sql server 2012. We have configured backup in our setup. We wanted to enable loggigng in such a way that when ever backup job runs it should log the status in error log. How to enable this. As of now it is creating seperate log file in log location after every execution. We wanted them to be logged in Error Log file it self.
I got full backup on daily schedule its taking more space on Drive because each file has more than 25GB.I am using SLQ server 2008R2 so I'm looking to take the backup with compression instead of uncompressed Backup. What are the impacts of compressed backup. Is there any problems with compressed backup while restoring the backup file.
I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write restore filelistonly from disk = 'path and filenam.bak' restore database. zy from disk = 'path and filename.bak' with replace, move..... move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC 2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original 3) Took the new database offline 4) deleted the MDF and LDF files of the new database 5) put "MyDB_0.LDF" in the place of the LDF file I just deleted 6) put the database back on-line 7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)" 8) tried to do Tail Log Backup with this command   BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak' WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3 Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
If my backup starts at 8PM and take 1 hour to complete, will the changes made to the database during that hour be captured in the full backup?
Stated another way, will my backup be a snapshot of: a) 8PM when the backup started b) 8PM with some of the changes made between the hour c) 9PM when the backup finished?
Anybody know the exact way SQL Server handles that logic?
I checked quickly one network  and I ran into the folders; I found out the folder MSSQL backup, nothing strange so far. Within that folder, anyway, I found out several backup and one very huge file (datawarehouse) with extension FILE. I am wondering what can be? I got datawarehouse mdf of course and datawarehouse log but what is that huge file (1 TB)?
I want to make data changes in read_only database , that's why i must set database read_write. While database is at read_write mode, i want to be sure that no one makes change in database.
For this aim, i write the code below, but i suspect that after setting the database read_write, till the setting database single_user ,is it possible get DML script from another user. Is the code below enough for this operation. Or is there another way?
Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.
The code;
use master alter database xxx set read_write with rollback immediate alter database xxx set single_user with rollback immediate
use xxx update tablexxx set columnxxx=yyy use master alter database xxx set read_only with rollback immediate alter database xxx set multi_user with rollback immediate
I'm having some problems with SQL Server 2005. I installed the Personal Starter kit but don't have the Express edition installed so I attached the .mdf file to SQL Server 2005. That's ok and I also changed the connection string to integrated security.<add name="Personal" connectionString="Data Source=MyTestServer;Initial Catalog=Personal;Integrated Security=True" providerName="System.Data.SqlClient"/>I tried setting the security settings in the Management studio under Security | Logins | BUILTINAdministrators where I select the User mapping and check the checkbox before the Personal.mdf file. But this doesn't seem to do the trick. How can I set the correct security settings?
The publisher could not be verified. Are you sure you want to run this software?This is my first SSIS package and the first for my employer. Under DTS we run some overnight tasks that bin with a .Net console application that downloads data. Several of these run in parallel.
Looked to me that the Execute Process Task was a good fit for the requirements. When I executed the step, the program did begin to execute in that a DOS window opened. But when, I clicked the run button on the warning dialog, the task step ended.
Questions: 1.- How to tell the Execute Process Task not to open the Security Warning dialog? 2.- Have you any suggestions about my approach?
I have a SQL job that calls a command file that executes a vbscript. I am getting "Open File - Security Warning" whenever the command file calls the vbs as it goes through a loop. Is there a way to suppress the warning dialog box?
I am selectively insert data from serverA databaseX to serverB databaseX. (serverA is linked server on B.)
I could insert parent and child tables. But get error when insert to grand-child tables: Server: Msg 8501, Level 16, State 3, Line1 MSDTC on server B is unavailable.
Don't think I need to turn on MSDTC on B. But I could copy and paste from source A server to target B server. Confusing on some table could insert while others with error....
Having to do contigency planning for a log shipping environment. Have the scenario covered for mdf's not being available (on a separate array), or complete server death. But what if the array the log files are on dies? Has anyone had to deal with this? I was wondering if you could just do a full backup of the current DB's and copy them over (I have my log files coming oer about evey 5 minutes) so I am not sure even if I can if I will gain much. Or if you lose your trans logs are you pretty much SOL. Thanks
I work on a SQL 2000 Standard server that is part of our SBS package. Two days ago we ran a set of MS updates on the server and the residual symptom is that DTS packages, the Import or Export wizard all will not run. The Sql Server Agent service runs under the local system account, however that was true prior to the updates.
The error when attempting to run an import is that the OLEDB connection object is not available. The error occurs when trying to use the Access driver or a DSN. Further investigation indicates that msdb could not be contacted.
Does anyone have an idea as to what might cause this and a workaround? Thanks,
The following code:DECLARE @tsql varchar(1000)CREATE TABLE #Test (...)SET @tsql = 'SELECT * FROM OPENQUERY(MyDataSource, ...)'INSERT INTO #Test EXEC(@tsql)generates the following error message at the insert statement:Server: Msg 8501, Level 16, State 3, Line 4MSDTC on server 'MyComputer' is unavailable.Why?Thanks,Tony PerovicCompumation, Inc.
Do we have any way of hiding SDF file from end-user? When we are installing our application on user's machine, we have to keep SDF file somewhere but anyone who knows about SDF file can open that file in management studio and that exposes our database structure. Is there a workaround for this ? How can we prevent visibility of SDF file ?
So I've had some issues upgrading to SQL Server Mgmt Studio Dev from Express. I finally got everything upgraded / installed and I see the instances that I was using with Express in my Registered Servers window but I can't connect to any of them. After looking in my Microsoft SQL Server directory I noticed that there are no MSSQL.# folders. How can I recreate these or create a new instance on my local machine?
help.... whenever i do processing from my sql server, while the program is processing halfway the Server Application Unavailable occur.
************************************************************************** Server Application Unavailable The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.
Administrator Note: An error message detailing the cause of this specific request failure can be found in the system event log of the web server. Please review this log entry to discover what caused this error to occur **********************************************************************
i go to the IIS and it shows ************************************************************************ aspnet_wp.exe (PID: 1692) was recycled because it was suspected to be in a deadlocked state. It did not send any responses for pending requests in the last 180 seconds. ************************************************************************
i suspect that the processing took longer than 180 seconds. my connection string is as below. What could be the problem???. thanks in advance
data source=127.0.0.1;initial catalog=test;persist security info=False;user id=sa;workstation id=test;packet size=4096;password=password;TimeOut=100000; connection reset=true;
Hi. I'm installing SQL Server 2005 on Vista Home Premium machines (I tried 2 machines and same problem).
It keeps giving the warning IIS not available / installed.
On both mahcines I've installed all IIS components via Control Panel > Programs & Features > Turn Windows Features On / Off. That includes the v 6.0 compatibility services
I can't find anything on MS besides "How to install IIS..." which provides no answer. I have installed Vista SP1 and all updates are installed. No other messages are given in the SQL installation.
Trying to install SQL 2005 Express Advanced on Vista x64 Business. IIS 7 installed, setup for 32-bit mode. No warnings, pass 15/15 tests, but when choosing components, Reporting services not available. Please help.
