We have developed and deployed a database which contanis very sensitive
information. Our client is now concerned about the security of the back
ups. In essense, if someone in the organization can get hold of the
backup of the database, he can simply restore it on any sql server in
the world with sa permission.
I know Microsoft provides flexibility of adding the "Password" in the
Backup t-sql statement but it wouldn't be of much use if the back up
task is saved as a script and password will be written inside the
script.
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 have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:DATAMYTEST.DAT_BAK' WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF', MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF', REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
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?
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 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.
I have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
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.
I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
Hi All,I am facing this issue very frist time. I add a logical device asfollowing:USE masterGOEXEC sp_addumpdevice 'disk', 'AdvWorksData','C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLBACKUPAdvWorksData.bak'-- Create a logical backup device, AdvWorksLog.USE masterGOEXEC sp_addumpdevice 'disk', 'AdvWorksLog','C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLBACKUPAdvWorksLog.bak'-- Back up the full AdventureWorks database.BACKUP DATABASE AdventureWorks TO AdvWorksData-- Back up the AdventureWorks log.BACKUP LOG AdventureWorksTO AdvWorksLogThe database backup is completed successfully but log's backup failswith the following messages:Msg 4214, Level 16, State 1, Line 1BACKUP LOG cannot be performed because there is no current databasebackup.Msg 3013, Level 16, State 1, Line 1BACKUP LOG is terminating abnormally.We are moving from SQL 2000 to SQL 2005. I have ensured that databasehas full recovery mode on. We are using SQL 2005 on Windows 2003 ServerSP1.Further more, I also created a plan to test it, and plan also failswhen backing up the logs.Can anyone shed some light on this issue.Thanks in advance.Najm
I have a MS SQL Server 2005 Enterprise Edition 9.0.2153 which manages my BizTalk Server 2006 Databases. The BizTalk server installer automatically creates a job named Backup BizTalk Server (BizTalkMgmtDb) which should back up the databases and transaction protocolls.
The SQL Server Agent runs under domain-administrator account which has full access to my backup directory D:ackups.
The job executes the following steps:
Step 1: BackupFull:
Command:
exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, 'D:Backups' /* location of backup files */
Step 2: MarkAndBackupLog
Command:
exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, 'D:Backups' /* location of backup files */
My challenges are:
1. The job doesn't back up my databases(D:Backups is empty after executing the job)
2. I get every time the following error in error protocol of the job:
BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Fehler 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Fehler 3013).
I have already switched the Databases from full to simple recovery mode and vica versa, it didn't help. Also, the above stored procedures doesn't include neither the TRUNCATE nor the LOG parameters for the logfiles so I wasn't able to solve this issue by adjusting these flags.
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!
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error:Â
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Is there a way to set permissions for the contained user so that this could be done?
I had created 2 Sql server instance in 2 servers created using VMware. From the primary server I log shipped the required databases into the secondary. Both the servers were in the same domain whose active directory was also in another server in the same virtual lab environment. My question can we have the primary sql server in one domain and the secondary sql server to which the logs are shipped in another domain by including a router also between the 2 networks for connectivity?
I have installed new SQLServer2012 instance and my domain user have sysadmin privileges on this instance. I have a restore procedure and it will execute WITH EXECUTE AS 'domainmy username', for all the developers have exec permissions to this procedure. But newly installed server this procedure was failing with the following message. But the same procedure executing fine on other servers.
Msg 262, Level 14, State 1, Line 1 CREATE DATABASE permission denied in database 'master'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
I have a script that automates some db drop/restore operations and bringing the database to single user mode is part of it: ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE...I want this to executes under a login, that has restricted privileges, so I've created a login and granted it a dbcreator role + ALTER ANY DATABASE privileges.
Problem: When I run the script against a database with an active/sleeping connection:It fails when using the restricted login: "Msg 5061...ALTER DATA BASE failed because a lock could not be placed on database ..."It completes successfully when using a sysadmin login According to stackoverflow.com the solution is to kill the active/sleeping connections to the database, before ALTER-ing it, which works fine, but the question is....
Questions: Why the "ALTER DATABASE..." statement works under the sysadmin login, but not under a dbcreator one?Does this mean the sysadmin login kills the connections to the target database in the background?Is it possible to grant additional privileges to the restricted login, so the "ALTER DATABASE..." statement won't need preventive killing of the connections?
i have a development database that has updates and changes to a production database. rather than go through individually and alter all relevant tables and stored procedures, id like to back up the database on the development side and restore it on the production side as the production database. is there a way to restore the database on the production server but preserve all the security settings (ie logins and such)? i noticed on our development server, that if i try to restore the database with my development database, it overwrites the users and/or if the user is the same on both, it removes the login name for that user.
I have just built a new SQL7 server (SQL SP3 and NT SP6a) server and need a bit of advice on implementing database security.
This new SQL server will be used by several projects which require a SQL database, our Personnel system being one of them. Since the data in the Personnel system is obviously of a sensitive nature I need to restrict access to the data. Client access is controlled via a client application but I need to restrict Enterprise Manager, Query analyzer ... type of access.
We have a team of people who look after the servers on a day to day basis and these are all Domain Administrators. By default these people will be able to browse into the Personnel database and read the information.
How can I restrict access to a SQL database so that only named users (SQL and NT) can access a database ? If database level access is required then we would need to obtain the SQL user password from Personnel which will then allow mainenance access, after which the password si changed by Personnel.
I am working with a development team and right now there is no security on the server everybody uses the sa account including our dts and jobs. I have talked to my boss about it and he wants me to come up with a guidline
What will happen to our jobs if i change sa password. also dose anyone have a little guidline sample?
What is the standard for database security – windows login or sql server logins? User defined database roles?
We have earlier used a single sql server login with read/write permissions to tables & exec on stored procedures. Individual application users are stored in database tables & their permissions are handled by the application itself. But we are starting to get concerned about password issues with the sql login & also that when I see any connections on the database, all of hem show up with the same sql login. So I cannot make out which user exactly has a connection open.
To get around these issues, we are thinking to add NT logins for each user. These users will be part of an NT group and will be added as a user to the database. There will be a database role with exec permissions to required stored procedures (all read/write/update will be controlled through SPs) and the NT group will be part of this role. The stored procedures will be encrypted. So if a NT user logs in directly to the database, he won’t be able to read from the tables or SPs and access will be limited through the application. I will be able to see each login associated with a connection.
What do you all think about this security set up? What are other issues I need to be concerned about? What are other popular database security models?
Hi experts, I would like to ask if it is feasible to limit the accessibility of an SA account in SQL 2005 in a specific database. The reason of doing this procedure is since we are deploying a package software to our client(s) we want to secure our own database to get tampered by our client(s).
Is there a way to create and encrypted database file?What do people do when data security is important at the file level? Inother words, you don't want anyone to be able to take the database file (orfiles) and extract data from them.Ideally, I want a file the is absolutely encrypted on disk and that isdecrypted for data access. The problem, obviously, is that this would be avery costly (cpu time) approach as you couldn't create a decrypted image ondisk (this would expose the data).Are all database systems then, non-secure?Thanks,-Martin
i a the user of sql server 2005 on window server 2003. i want to knowthat how can i prevent my database from other user logins because allof them are sysadmin type.and i am also looking for database concurrency control methods.if any one know about this plz mail me answer on this mail idJoin Bytes!thanx in advance
Hi, does anyone have the name of the command-line utility that creates a security database on SQL Server 2005 with the corresponding tables (users, roles, applications)? I remember using it in an exercise some time ago and I can't find it anywhere.
Hi experts, I would like to ask if it is feasible to limit the accessibility of an SA account in SQL 2005 in a specific database. The reason of doing this procedure is since we are deploying a package software to our client(s) we want to secure our own database to get tampered by our client(s).
I am using sdf database file for stroing improtant data. That file is password protected. But I want to apply some more security constraintson on it. May I know What are the possible ways to secure my data? How I use encrypt feature? so that if anyone open sdf file he cant see actual data into it.
We are using VB as frontend and SQL Server 7.0 as backend applications in our company. The users are using 'User DSN' to connect the databse with NT logged in user security. User can connect the database if he is having the permissions on the database. We are not using SQL Server authontication like 'sa' user. Till here it is working fine. Our problem is, We want to avoid the users to connect the database using SQL Server Enterprise Manager. We have the SQL Server Enterprise Manager software installed on some machines. because some times we may need that. Right now the users (If he is having the permissions on the database) can directly open the database using Enterprise Manager and they can manipulate the data directly.
We need only specific users like The users who are in System Administrators group has to access the database using Enterprise Manager.
