Showing posts with label dba. Show all posts
Showing posts with label dba. Show all posts

Sunday, March 25, 2012

Database Size question

Hi DBA's -

Please comment on my understanding of this concept -

The current size of the database I am building is 321 GB. Of the 321 GB, 175 GB is the size of the actual data. The remainder of the size is the unallocated space (Space that is not reserved for any objects). I used the sp_spaceused to obtain the statistics.

When a full backup occurs it is the portion containing the data (175 GB) that gets backed up along with the size information of the overall database (321 GB).

Now say I want to test my backup solution on a test machine.

When a restore of the database would occur, the size of the restored database would be 321 GB and not 175 GB which is the size of the actual data. So restore the database successfully I would need a test machine that would accommodate the database size (321 GB) and not just the actual data size (175 GB).

Please add your expert comments.

Thanks.utterly true, the size of the backup set will be around 175 GB, but the size of restored db on test server will be exactly the same as on production machine.
mojza|||If you're looking to save space you could so in one of two ways, shrink your source database before you back it up - won't reduce the size of your backup file, but will reduce the size of the restored database - or just shrink the restored database. I'm not a big proponent of shrinking databases - if it needed that space once, it will typically need it again, if it's shrunk, it will have to spend processor and memory to get it back to the proper size. Just my $.02 :)

Nick

Wednesday, March 21, 2012

Database setup for backup and recovery

I am new to SQLServer but a DB2 DBA.

I want to be able to backup a specific table and restore it. Actually, i may want to backup/restore several tables - a sub-set of tables on the database.

I understand that backup-recovery is at a database level, not a table-file-filrgroup level. Therefore i have to backup-restore a database, but i only want a table.

How do sites handle this. Are many databases created based on backup recovery requirements.

If so, then how do developers know what database tables reside in - given that there are now many databases created to handle recovery requirements. A synonymns/alias/views added ?

tia

glenn

If you put the table on its own filegroup, you can restore just that filegroup/table. Else, you will need to restore the database to a staging db and copy/transfer the table from the staging db to the real db.|||

Can i restore a single filegroup to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore?

tia

Database setup for backup and recovery

I am new to SQLServer but a DB2 DBA.

I want to be able to backup a specific table and restore it. Actually, i may want to backup/restore several tables - a sub-set of tables on the database.

I understand that backup-recovery is at a database level, not a table-file-filrgroup level. Therefore i have to backup-restore a database, but i only want a table.

How do sites handle this. Are many databases created based on backup recovery requirements.

If so, then how do developers know what database tables reside in - given that there are now many databases created to handle recovery requirements. A synonymns/alias/views added ?

tia

glenn

If you put the table on its own filegroup, you can restore just that filegroup/table. Else, you will need to restore the database to a staging db and copy/transfer the table from the staging db to the real db.|||

Can i restore a single filegroup to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore?

tia

Monday, March 19, 2012

Database security

How can sql server ensure that the data that is P&C cannot be accessed even by the dba? Is there a way to encrypt these data without comprising on the flexibility of sql?Man, that's a big question. Here's a good place to start reading:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_93u6.asp|||It should be possible to set up a role for your DBA that allows them to do basic maintenance, such as backups and restores, but does not allow them to access tables or procedures. Can't say I've done this, though.

If your DBA logs in as sa or dbo, then there is no way to hide anything that you don't encrypt yourself. DBO is database god (small g), and SA is server God (big g).

blindman|||hmm... in that case anybody that gotten hold of any of my full backup files too can just restore it and definately he/she will have sa authority and be able to view everything....|||Where are you leaving your backups? Shouldn't they enjoy the same security (file system, locked cabinet for tapes, etc) as your live database files? It's like leaving photocopies of you credit card statements around.

Did you read the article on encryption? It does provide a method of protecting your data from direct viewing, but it needs to be set up that way initially.|||bpdWork,

I don't see where in the article it talks about protecting your data from viewing within the database, just from viewing intercepted transmissions across the network. Have you done this?

debcwong,

The BACKUP command allows to supply a password that is then required in order to restore the file, although the sqlmaint Utility (used by the Maintenance Plan wizard) does not.

blindman|||No, I haven't. In fact, you can probably tell just how lazy I am by the fact that I didn't read the whole article.

Though, the second sentence says: "Encryption ensures that data remains secure by keeping the information hidden from everyone, even if the encrypted data is viewed directly," I cannot find any way of actually doing this.

I am able to encrypt Stored rocedures and Views so that their definitions are encrypted, but that doesn't help much.

In the past, I have always written an encryption routine that things such as Credit Card numbers were passed through on their way into and out of the database. .NET has an encryption class that makes that approach a lot easier, and more secure.

Sorry for being misleading there. I guess I'm the naked, following blindman around... ;-)|||..that makes me feel a little nervous... :rolleyes:|||yeah, it scares the heck out of me.|||err guys..or gals...there's always the icq or msn for those kind of thing i believe ;)

neway sometimes it's not perfectly true in the sense that most of the software developed might be for customers and usually customers will DEMAND for the rights to access to everything and also to restore it.
That's was the whole reason y I asked the question in the first place =)

Neway am thinking of the payroll system that is currrently under development stage... I'm sure you might be a bit interested to know the pay your superior's getting ...|||Sorry. Just a little crazy from the workload.

I do unserstand what you are trying to accomplish. If you use your front-end application (or middleware) to perform the encryption, that would solve your data visibility problem. If you ultimately find a way for SQL to do it for you, I would love to know about it.

Also, as for blindman's idea to password protect the backups, you could let the end-users control the backup password protection. You can even impliment code in your front end to perform the backup and restores.|||thanks to both of you.

will try that bit on the backup thingy at work tomorrow|||As far as sensitive data is concerned (such as salaries), tell your people that the same confidentiality rules apply to DBAs as to priests.

...except that the celibacy is implied rather than enforced...|||Hey guys I have been looking into this lately myself for a db that needs to be secured. I ran across this plugin, but I haven't actually tested it.

http://www.appsecinc.com/products/dbencrypt/mssql/

When I looked over the info on this product it does exactly what you are looking for. You select the users that should get access to the information, and you can set it to encrypt only a specific column.

Do you guys know of a good way to send data from one remote computer to another? I need to send credit info from an online server to the companies internal server in the most secure way possible. These two servers will have a vpn link and the online db will have an SSL cert attached to it as well. Any thoughts?

Thanks for you help...|||Thanks for the link, 6SC.

blindman|||We implemented this slightly differently:

- Put your payroll system on a separate server
- Install SQLLiteSpeed with Encryption
- Create scheduled jobs to run custom backup
- Create alerts on vital system counters
- Setup email notification
- Create notification job
- Remove Builtin Admins and Domain/Enterprise admins from sysadmin server role
- Ask your accounting boss to change SA password, because even you should not have routine access to this server
- Make sure your accounting boss shares SA password with your CIO.|||thanks rdjabarov


Probably I'll propose to have it in another database but without additional software such as sqllitespeed. Will try to implement the database backup. Probably dts can help in schedulling it since the maintainence plan doesn't have this password feature.|||You can use T-SQL fired by a SQL Server Agent job to perform your backups, and specify passwords. See the following link on MSDN (yes, I've read this one and use it quite a bit!):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp

Using T-SQL will allow you to be more dynamic in your code. Also, you won't be going out of process (running DTSRUN.exe) which can cause it's own problems with production reliability.

Thursday, March 8, 2012

Database restore onto another machine

Hi,
I'm a Sybase DBA on UNIX and I don't know that much about MS SQL Server
2000, but I was just given responsibility for an MS SQL Server 2000 and my
first step was to install MSDE on my desktop and take a backup from the SQL
Server 2000 and try to restore it onto MSDE. so I can play with it.
1. Is it possible to take a backup from SQL Server and load it into MSDE
2. If yes, in Sybase all I have do is create the database on another machine
and load it from the backup file as follow:
Load database dbname from file
This same syntax does not work in MS SQL Server 2000.
I'm going over the online books, but so far I'm very confused. Can anyone
give me a simple summary of whatI need to do.
ThanksHi Germano
In SQL Server 2000, you don't even need to create the db before loading.
What error are you getting when you run the command?
In SQL Server, we use RESTORE instead of LOAD:
RESTORE DATABASE mydb
FROM DISK = 'c:\....
Also, the default is to not recover the database so that tlog backups can be
applied, so you must add WITH RECOVERY when you are done with the restore
operations, to make the db accessible.
Please feel free to ask more questions after thoroughly reading Books
Online. There are some important differences between Sybase backup/restore
and SQL Server backup/restore.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Germano" <Germano_Silva@.Brown.edu> wrote in message
news:uwf6h1CjDHA.556@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm a Sybase DBA on UNIX and I don't know that much about MS SQL Server
> 2000, but I was just given responsibility for an MS SQL Server 2000 and my
> first step was to install MSDE on my desktop and take a backup from the
SQL
> Server 2000 and try to restore it onto MSDE. so I can play with it.
> 1. Is it possible to take a backup from SQL Server and load it into MSDE
> 2. If yes, in Sybase all I have do is create the database on another
machine
> and load it from the backup file as follow:
> Load database dbname from file
> This same syntax does not work in MS SQL Server 2000.
> I'm going over the online books, but so far I'm very confused. Can anyone
> give me a simple summary of whatI need to do.
> Thanks
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01F0_01C38C0D.8D14F410
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Check out RESTORE DATABASE in the BOL. Also, beware of logins being out =of synch with users in the database. You can correct this with =sp_change_users_login.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Germano" <Germano_Silva@.Brown.edu> wrote in message =news:uwf6h1CjDHA.556@.TK2MSFTNGP11.phx.gbl...
Hi,
I'm a Sybase DBA on UNIX and I don't know that much about MS SQL Server
2000, but I was just given responsibility for an MS SQL Server 2000 and =my
first step was to install MSDE on my desktop and take a backup from the =SQL
Server 2000 and try to restore it onto MSDE. so I can play with it.
1. Is it possible to take a backup from SQL Server and load it into MSDE
2. If yes, in Sybase all I have do is create the database on another =machine
and load it from the backup file as follow:
Load database dbname from file
This same syntax does not work in MS SQL Server 2000.
I'm going over the online books, but so far I'm very confused. Can =anyone
give me a simple summary of whatI need to do.
Thanks
--=_NextPart_000_01F0_01C38C0D.8D14F410
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Check out RESTORE DATABASE in the =BOL. Also, beware of logins being out of synch with users in the database. =You can correct this with sp_change_users_login.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Germano" =wrote in message news:uwf6h1CjDHA.556@.T=K2MSFTNGP11.phx.gbl...Hi,I'm a Sybase DBA on UNIX and I don't know that much about MS SQL =Server2000, but I was just given responsibility for an MS SQL Server 2000 and =myfirst step was to install MSDE on my desktop and take a backup from the =SQLServer 2000 and try to restore it onto MSDE. so I can play with it.1. Is it =possible to take a backup from SQL Server and load it into MSDE2. If yes, in =Sybase all I have do is create the database on another machineand load it =from the backup file as follow: Load =database dbname from fileThis same syntax does not work in MS SQL Server 2000.I'm going over the online books, but so far I'm very =confused. Can anyonegive me a simple summary of whatI need to do.Thanks

--=_NextPart_000_01F0_01C38C0D.8D14F410--|||Check out this link ->
http://support.microsoft.com/default.aspx?scid=kb;en-
us;307775
You can move the backup file to the destination machine
and use Enterprise Manager to restore it to the new
location. Just highlight the Databases node and select All
Tasks and select Restore Database. Follow the wizard after
that.
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>Hi,
>I'm a Sybase DBA on UNIX and I don't know that much about
MS SQL Server
>2000, but I was just given responsibility for an MS SQL
Server 2000 and my
>first step was to install MSDE on my desktop and take a
backup from the SQL
>Server 2000 and try to restore it onto MSDE. so I can
play with it.
>1. Is it possible to take a backup from SQL Server and
load it into MSDE
>2. If yes, in Sybase all I have do is create the database
on another machine
>and load it from the backup file as follow:
> Load database dbname from file
>This same syntax does not work in MS SQL Server 2000.
>I'm going over the online books, but so far I'm very
confused. Can anyone
>give me a simple summary of whatI need to do.
>Thanks
>
>.
>|||That did not work. Here's the error
D:\DV Backup>osql -Sdev340\NetSDK -E
1> use master
2> go
1> restore database DialVision from disk = 'd:\Dv
Backup\Dialvision_db_200309260300.bak'
2> go
Msg 5105, Level 16, State 2, Server DEV340\NETSDK, Line 1
Device activation error. The physical file name 'e:\Program Files\Microsoft
SQL
Server\mssql\Data\dialvision_data.mdf' may be incorrect.
Msg 3156, Level 16, State 1, Server DEV340\NETSDK, Line 1
File 'DVision_Data' cannot be restored to 'e:\Program Files\Microsoft SQL
Server\mssql\Data\dialvision_data.mdf'. Use WITH MOVE to identify a valid
location for the file.
Msg 5105, Level 16, State 2, Server DEV340\NETSDK, Line 1
Device activation error. The physical file name 'e:\Program Files\Microsoft
SQL
Server\mssql\Data\dialvision_log.ldf' may be incorrect.
Msg 3156, Level 16, State 1, Server DEV340\NETSDK, Line 1
File 'DVision_Log' cannot be restored to 'e:\Program Files\Microsoft SQL
Server\mssql\Data\dialvision_log.ldf'.
Use WITH MOVE to identify a valid location for the file.
Msg 3013, Level 16, State 1, Server DEV340\NETSDK, Line 1
RESTORE DATABASE is terminating abnormally.
1>
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OovDu6CjDHA.2492@.TK2MSFTNGP12.phx.gbl...
> Hi Germano
> In SQL Server 2000, you don't even need to create the db before loading.
> What error are you getting when you run the command?
> In SQL Server, we use RESTORE instead of LOAD:
> RESTORE DATABASE mydb
> FROM DISK = 'c:\....
> Also, the default is to not recover the database so that tlog backups can
be
> applied, so you must add WITH RECOVERY when you are done with the restore
> operations, to make the db accessible.
> Please feel free to ask more questions after thoroughly reading Books
> Online. There are some important differences between Sybase backup/restore
> and SQL Server backup/restore.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Germano" <Germano_Silva@.Brown.edu> wrote in message
> news:uwf6h1CjDHA.556@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I'm a Sybase DBA on UNIX and I don't know that much about MS SQL Server
> > 2000, but I was just given responsibility for an MS SQL Server 2000 and
my
> > first step was to install MSDE on my desktop and take a backup from the
> SQL
> > Server 2000 and try to restore it onto MSDE. so I can play with it.
> >
> > 1. Is it possible to take a backup from SQL Server and load it into MSDE
> > 2. If yes, in Sybase all I have do is create the database on another
> machine
> > and load it from the backup file as follow:
> >
> > Load database dbname from file
> >
> > This same syntax does not work in MS SQL Server 2000.
> >
> > I'm going over the online books, but so far I'm very confused. Can
anyone
> > give me a simple summary of whatI need to do.
> >
> > Thanks
> >
> >
> >
>|||Here's thesyntax that worked:
restore filelistonly
from disk = 'd:\Dv Backup\Dialvision_db_200309260300.bak'
restore database myDV
from disk = 'd:\Dv Backup\Dialvision_db_200309260300.bak'
with move 'DVision_data' to 'd:\DV Backup\myDV.mdf',
move 'DVision_log' to 'd:\DV Backup\myDV.ldf'
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OovDu6CjDHA.2492@.TK2MSFTNGP12.phx.gbl...
> Hi Germano
> In SQL Server 2000, you don't even need to create the db before loading.
> What error are you getting when you run the command?
> In SQL Server, we use RESTORE instead of LOAD:
> RESTORE DATABASE mydb
> FROM DISK = 'c:\....
> Also, the default is to not recover the database so that tlog backups can
be
> applied, so you must add WITH RECOVERY when you are done with the restore
> operations, to make the db accessible.
> Please feel free to ask more questions after thoroughly reading Books
> Online. There are some important differences between Sybase backup/restore
> and SQL Server backup/restore.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Germano" <Germano_Silva@.Brown.edu> wrote in message
> news:uwf6h1CjDHA.556@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I'm a Sybase DBA on UNIX and I don't know that much about MS SQL Server
> > 2000, but I was just given responsibility for an MS SQL Server 2000 and
my
> > first step was to install MSDE on my desktop and take a backup from the
> SQL
> > Server 2000 and try to restore it onto MSDE. so I can play with it.
> >
> > 1. Is it possible to take a backup from SQL Server and load it into MSDE
> > 2. If yes, in Sybase all I have do is create the database on another
> machine
> > and load it from the backup file as follow:
> >
> > Load database dbname from file
> >
> > This same syntax does not work in MS SQL Server 2000.
> >
> > I'm going over the online books, but so far I'm very confused. Can
anyone
> > give me a simple summary of whatI need to do.
> >
> > Thanks
> >
> >
> >
>

Tuesday, February 14, 2012

database ownership question

Our SQL Server DBA has left the company with many of the databases owned by his domain user. I understand that I could change database ownership using sp_changedbowner. Does anyone know what would be a better option, from security point of view?

1. Let databases owned by 'sa' like all system databases.
2. Let databases owner by the domain account that runs SQL Service.
3. Create a local/domain account which owns all databases on this SQL Server instance.
4. Let databases owned by Administrator account.

I am new to SQL Server. Help would be appreciated.Howdy

'sa' is the best way to go.

By all means, set up SQL & SQL Agent to run using a domain account unless it needs to be a highly secure box.

By the way, any account who is in the local Administrators group ( this is NT security I'm talking about ) on the box, is automatically sa ( potential LARGE security hole ).

Post back if any more questions. Happy to answer.

Cheers

SG|||Thanks for the info, will try that.