.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Asad Ali
Fauzul Azmi
Post New Web Links

how to prevent security/permissions affected by backup/restore

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server
 
I create a copy of a production database (for reporting purposes) by using the backup and restore commands.  The copy is on a seperate server. (replication wont work in my case)
The reporting server allows more read-only access then the production server.

I do a full backup and restore, and of course all permissions/security settings come with all the data.

How can I do a backup and restore of only data without altering permissions on the reporting server? 
As more of my users want to run reports, I give them access on the reporting server but not the production server, and of course it gets wiped out every night when the backup-restore runs.

Using SQL Server 8.0 and 9.0.
Thanks.


View Complete Post


More Related Resource Links

SQL Server Backup and Restore (video)

  
Restore your full backup, then run a few queries before you keep restoring transaction logs. Brent shows how to use the Standby option for restores.

In this five minute video, Brent demonstrates how to do it by creating a database, populating data, and then showing what disaster recovery is like by doing RESTORE WITH STANDBY.

Security in .NET: The Security Infrastructure of the CLR Provides Evidence, Policy, Permissions, and

  

The common language runtime of the .NET Framework has its own secure execution model that isn't bound by the limitations of the operating system it's running on. In addition, unlike the old principal-based security, the CLR enforces security policy based on where code is coming from rather than who the user is. This model, called code access security, makes sense in today's environment because so much code is installed over the Internet and even a trusted user doesn't know when that code is safe.In this article, Don Box explains how code access security works in the CLR. He discusses the kinds of evidence required by policy, how permissions are granted, and how policy is enforced by the runtime.

Don Box

MSDN Magazine September 2002


SMO Backup and Restore Help

  
Hello Friends, I am new to SMO, Till now I have only worked with Selct,Insert/update Queries.. Now I want to Implement some backup and restore feature for my application. I looked at some documents of SMO and found it is possible using SMO. First of all I will explain my situation. My application wants to store data belongs to different companies(which is maintained with in application) in to single database server, Sql server 2008. I am planning to use Partition Scheme function and create filegroup for each company. Advantage here is, taking backup of each company will be easy. I am generating primary key for each table without taking into consideration of companies. In other words Particular table's ID 1,2 belongs to Company A, 3 Belongs to Company B and 5 belongs to Company A... But these data will be split in to Different filegroups using Company ID.(horizontal partitioning) Ok... Now the Problem.. My backup for a particular company was taken successfully. Now I want Restore function. When I am trying to restore this backup to somewhere else. Target database may or may not contain Companies installed. When Restoring Will there be any Primary key Violation? I know one solution is to generate primary key by taking into consideration Company Id. But this will cause a complete rewriting of code, as it contains lot of edit/delete queries.  I want to know, whether the

SQL2008 - Cannot Restore a new database from a full backup of a different one, database in use

  
I am trying to restore a brand new database from a copy of another one and am encountering an error message stating: System.Data.SqlClient.SqlError: The file <Backup source database file location> cannot be overwritten.  It is being used by database <Backup Source Database>. (Microsoft.SqlServer.Smo) I am running the Restore procedure with the REPLACE option and am wondering why it is stating that the source database is the database in use when I am trying to restore and overwrite a completely different database. This issue happens when running the replace both in C# using SMO and when manually restoring with SqlServer Management Studio Interesting note is, the original source database is created / deployed using a database project within VisualStudios 2010 and then deployed to SqlServer through VisualStudios. Any thoughts on why this is happening? Thanks in advance for any help!

What happened to the backup and restore classes in namespace Microsoft.SqlServer.Management.Smo vers

  
I do not see these two classes in version 10 of this namespace and I am not finding any documentation explaining what happened to them.

Backup and Restore

  
Hi. I am creating a vb 2010 application and I am using sql server express as my database. I need to create a backup and restore feature for the user so that the user can backup or restore the database while using the program. I have nop clue where to start. Does anyone have some code for or any suggestions?Thanks in advance.

Upgrading platform - Report Services DBs backup /restore

  
We are upgrading the platform for our production SQL 2005 server.  Of course, I have done this task numerous times....however this is the first time I will be dealing with Reporting Services.  I am concerned that it may not be a simple backup databases and then restore on new server, because of the encryption keys. What do I need to do ? Thanks!

Can't restore database backup file in my database ? using sql server 2005. please help

  
VERY IMPORTANT i am trying to restore database.bak in sql server 2005 (i know the database.bak was also generated in sql 2005 server) i am trying to restore back up database .bak into the new database i just created in sql server 2005 i have saved my database .bak into c drive and when i select database .bak "From Device", it doesn't get populated in the list below and i see nothing and it keeps on prompting a message "You must select a restore source" Here's the screen shot:   PLEASE HELP..it's really important (i tried restoring database in sql server 2008 and it was sucessful but i am facing this problem in sql server 2005 only)  

Cannot Restore from backUp

  
Hi,     I am using SQL Server 2008. I made a backup of a database. its size is about 1000MB. I did a verify the backup and it does not give me any error. I copy the backup file to my Machine(Development Machine). I am using the exact same version of SQL server. When I try restoring from back i get the error. TITLE: Microsoft SQL Server Management Studio ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ ADDITIONAL INFORMATION: The media family on device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\dcaRms1-05Bkp.bak' is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241) When I tried doing in from code: just a Restore Verifyonly I get this error: Msg 3241, Level 16, State 7, Line 1 The media family on device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\dcaRms1-05Bkp.bak' is incorrectly formed. SQL Server cannot process this media family. Msg 3013, Level 16, State 1, Line 1 VERIFY DATABASE is terminating abnormally. I really do not understand. It is not like I am trying to restore the backup created in 2008 to 2005 version. and I am pretty sure the backup file is not corrupt. I can restore it in the same database.

Backup SQL Server 2008 R2 Restore on SQL 2008

  
I have a SQL Server 2008 R2 instance that I need to backup and restore onto a SQL 2008 instance. When I attempt to restore the back up (.bak) database from the R2 SQL server onto the SQL 2008 server I get the following message: The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo) How can i backup the SQL 2008 R2 database to be compatible with SQL 2008?Ian Ceicys

How to fix permissions after a restore

  
I did a backup and restore of a site collection in 2010. Now subsites are unavailable with access denied error messages. How do correct this? I've done preliminary searchin for powershell commands but I'm not finding anything quickly... Anyone have a link that will explain how to reset permissions on a site collection and/or its subweb's?Kevin W. Gagel

Replication Subscriptions by using Backup and Restore

  
I thought this would work, but obviously I've overlooked something. I have SQL Server 2008 on 2 servers. Have database on publisher server and want to merge replicate to subscriber, but didn't want to take days like it did last time. 1. I published the database and ran the snapshot agent. 2. I backed up the database to a .bak file. 3. I copied the .bak file to the subscriber computer. 4. I restored the database on the subscriber computer from the .bak file. 5. Back at the publishing server, I added the subscriber under subscriptions, but I unchecked the "Initialize" box since that was the point of doing the backup, copy and restore. And. Oops. The replication monitor says it has a syntax error at "AccountPK." That would be the primary key for the account table. That wasn't what I hoped for. Suggestions? You can't be successful at this unless you're at least 1/2 a bubble off level.

Copy sharepoint 2007 folder (with sub folders) with all the security permissions

  
I am looking to copy a common sharepoint folder(sub folders) in 2007 with all the security permissions intact, to a different location in the same site, Does anyone know how to do this?

PDF Rendering from RS2005 web service affected by recent security patching.

  
p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;font-family:'Calibri','sans-serif';} span.EmailStyle15 {font-family:'Calibri','sans-serif';color:windowtext;} .MsoChpDefault {;} @page Section1 {size:612.0pt 792.0pt;margin:72.0pt 72.0pt 72.0pt 72.0pt;} div.Section1 {page:Section1;} p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;font-family:'Calibri','sans-serif';} span.EmailStyle15 {font-family:'Calibri','sans-serif';color:windowtext;} .MsoChpDefault {;} @page Section1 {size:612.0pt 792.0pt;margin:72.0pt 72.0pt 72.0pt 72.0pt;} div.Section1 {page:Section1;} One of our web services makes a call to the RS2005 ReportingServiceSoapClient Render method in order to generate reports as PDFs. This PDF is then copied to a fileshare where it is picked up by a third party document delivery product and distributed to our clients. The third party process uses ghostscript in order to convert the PDF to TIFF format to allow further processing. This has been working successfully for months (and in a legacy project using an older version of SQL RS2003 for a few years now).Recently one of our report servers was patched with the SP2 / Hotfix for the ActiveX printing issue. Since this has happened we have a problem where the PDFs being generated (while still readable in Adobe reader) now cont

how to restore msdb database on sql 2008 from sql 2005 backup?

  
Currently, I am unable to do it. It gives me the error than I cannot be restored because it was created by a different version of the server. What is the work-around? I hate scripting all my maintenance plans and alerts, there are tonns of themJulieShop

"The backup set holds a backup of a database other than existing database. Restore Database is term

  
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:\DATA\MYTEST.DAT_BAK' WITH MOVE 'VZAI_DATA' TO D:\PROGRAM FILES\..\MSSQL\TEST.MDF', MOVE 'VZAI_LOG' TO D:\PROGRAM FILES\..\MSSQL\TEST.LDF', REPLACE   And also i tried like   RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:\DATA\MYTEST.DAT_BAK' WITH REPLACE   When i use like this,   RESTORE FILELISTONLY FROM DISK = 'D:\DATA\MYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.   Can i anyone please help me out?   Thanks in Advance, Anand Rajagopal

Copy sharepoint 2007 folder (with sub folders) with all the security permissions

  
I am looking to copy a common sharepoint folder(sub folders) in 2007 with all the security permissions intact, to a different location in the same site, Does anyone know how to do this?
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend