Big log file in backup

I have backup of database which have log file (LDF file) big around 7GB. At
the moment it seem that I don't have enough space to restore it, Is it
possible that I some hove restore only MDF file or reduce size of LDF in
backup?
Johny
If you don't 'need' the LOG file , you can detach the database , remove the
log file and attach the database with the option as single file (only .mdf)
I'd recommed you to start perfoming BACKUP LOG file on regular period of
time so you will be able to manage the size of the log as well as having a
posibility to restore the data at point of time .
"Johny" <goky2002@.beograd.com> wrote in message
news:%23VoAafBMGHA.532@.TK2MSFTNGP15.phx.gbl...
>I have backup of database which have log file (LDF file) big around 7GB. At
> the moment it seem that I don't have enough space to restore it, Is it
> possible that I some hove restore only MDF file or reduce size of LDF in
> backup?
>

Big log file in backup

I have backup of database which have log file (LDF file) big around 7GB. At
the moment it seem that I don't have enough space to restore it, Is it
possible that I some hove restore only MDF file or reduce size of LDF in
backup?Johny
If you don't 'need' the LOG file , you can detach the database , remove the
log file and attach the database with the option as single file (only .mdf)
I'd recommed you to start perfoming BACKUP LOG file on regular period of
time so you will be able to manage the size of the log as well as having a
posibility to restore the data at point of time .
"Johny" <goky2002@.beograd.com> wrote in message
news:%23VoAafBMGHA.532@.TK2MSFTNGP15.phx.gbl...
>I have backup of database which have log file (LDF file) big around 7GB. At
> the moment it seem that I don't have enough space to restore it, Is it
> possible that I some hove restore only MDF file or reduce size of LDF in
> backup?
>

Big log file in backup

I have backup of database which have log file (LDF file) big around 7GB. At
the moment it seem that I don't have enough space to restore it, Is it
possible that I some hove restore only MDF file or reduce size of LDF in
backup?Johny
If you don't 'need' the LOG file , you can detach the database , remove the
log file and attach the database with the option as single file (only .mdf)
I'd recommed you to start perfoming BACKUP LOG file on regular period of
time so you will be able to manage the size of the log as well as having a
posibility to restore the data at point of time .
"Johny" <goky2002@.beograd.com> wrote in message
news:%23VoAafBMGHA.532@.TK2MSFTNGP15.phx.gbl...
>I have backup of database which have log file (LDF file) big around 7GB. At
> the moment it seem that I don't have enough space to restore it, Is it
> possible that I some hove restore only MDF file or reduce size of LDF in
> backup?
>

Best way to test backup restores

Ok, so I'm using SQL Server 2000, and I'm just wondering what the best way
to test my backups is.
I don't want to restore over the actual database because it's still being
used. I realise I can just take a copy of the database and use that as the
new database - thus allowing me to restore the original, but this is not
actually what I want.
Is there any way to just 'verify' that the backup will be useable? Or to
restore it to a different database?
Thanks in advance
Amanda
Only way to really verify a SQL Server backup is to restore it. You don't need to create the
database beforehand, as the new database will be created with the RESTORE command. Pay attention to
the MOVE option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Amanda Toogood" <amanda@.autumncare.com.au> wrote in message
news:OkQj1kOXEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Ok, so I'm using SQL Server 2000, and I'm just wondering what the best way
> to test my backups is.
> I don't want to restore over the actual database because it's still being
> used. I realise I can just take a copy of the database and use that as the
> new database - thus allowing me to restore the original, but this is not
> actually what I want.
> Is there any way to just 'verify' that the backup will be useable? Or to
> restore it to a different database?
> Thanks in advance
> Amanda
>
|||Hi,
Use the VERIFYONLY option in RESTORE DATABASE comamnd. This verifies the
backup but does not restore the backup. THis command Checks to see that the
backup set is complete and that all volumes are readable. However, RESTORE
VERIFYONLY does not attempt to verify the structure of the data contained in
the backup volumes.
RESTORE VERIFYONLY from disk='c:\backup\dbname.bak'
This command executes for the backup file provided and If the backup is
valid, Microsoft SQL Server 2000 returns the message:
"The backup set is valid."
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e8r#3yOXEHA.2716@.tk2msftngp13.phx.gbl...
> Only way to really verify a SQL Server backup is to restore it. You don't
need to create the
> database beforehand, as the new database will be created with the RESTORE
command. Pay attention to[vbcol=seagreen]
> the MOVE option of the RESTORE command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Amanda Toogood" <amanda@.autumncare.com.au> wrote in message
> news:OkQj1kOXEHA.1764@.TK2MSFTNGP10.phx.gbl...
way[vbcol=seagreen]
being[vbcol=seagreen]
the
>
|||I do not recommend using VERIFYONLY, as it basically only verify that the backup file is readable.
I.e., it gives us false security, IMO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OQ2iGFQXEHA.1440@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Use the VERIFYONLY option in RESTORE DATABASE comamnd. This verifies the
> backup but does not restore the backup. THis command Checks to see that the
> backup set is complete and that all volumes are readable. However, RESTORE
> VERIFYONLY does not attempt to verify the structure of the data contained in
> the backup volumes.
> RESTORE VERIFYONLY from disk='c:\backup\dbname.bak'
> This command executes for the backup file provided and If the backup is
> valid, Microsoft SQL Server 2000 returns the message:
> "The backup set is valid."
>
> Thanks
> Hari
> MCDBA
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:e8r#3yOXEHA.2716@.tk2msftngp13.phx.gbl...
> need to create the
> command. Pay attention to
> way
> being
> the
>
|||I have to agree with Tibor on this, the only true way to test is to actually
do the complete restore. I've certainly see restore issues with backups that
passed a verifyonly. Better safe than sorry :-)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eD6n3bQXEHA.3640@.TK2MSFTNGP11.phx.gbl...
> I do not recommend using VERIFYONLY, as it basically only verify that the
backup file is readable.[vbcol=seagreen]
> I.e., it gives us false security, IMO.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OQ2iGFQXEHA.1440@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
RESTORE[vbcol=seagreen]
contained in[vbcol=seagreen]
in[vbcol=seagreen]
don't[vbcol=seagreen]
RESTORE[vbcol=seagreen]
best[vbcol=seagreen]
as[vbcol=seagreen]
not[vbcol=seagreen]
Or to
>
|||I have to agree with Tibor on this, the only true way to test is to actually
do the complete restore. I've certainly see restore issues with backups that
passed a verifyonly. Better safe than sorry :-)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eD6n3bQXEHA.3640@.TK2MSFTNGP11.phx.gbl...
> I do not recommend using VERIFYONLY, as it basically only verify that the
backup file is readable.[vbcol=seagreen]
> I.e., it gives us false security, IMO.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OQ2iGFQXEHA.1440@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
RESTORE[vbcol=seagreen]
contained in[vbcol=seagreen]
in[vbcol=seagreen]
don't[vbcol=seagreen]
RESTORE[vbcol=seagreen]
best[vbcol=seagreen]
as[vbcol=seagreen]
not[vbcol=seagreen]
Or to
>
|||I will also strongly agree. You might even wish to run a DBCC CHECKDB on it
after the restore. Of coarse if there was corruption in the original db it
will be in the restored one as well.
Andrew J. Kelly SQL MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23K4hwWTXEHA.644@.tk2msftngp13.phx.gbl...
> I have to agree with Tibor on this, the only true way to test is to
actually
> do the complete restore. I've certainly see restore issues with backups
that
> passed a verifyonly. Better safe than sorry :-)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:eD6n3bQXEHA.3640@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
> backup file is readable.
the[vbcol=seagreen]
that[vbcol=seagreen]
> the
> RESTORE
> contained in
is[vbcol=seagreen]
wrote[vbcol=seagreen]
> in
> don't
> RESTORE
> best
still[vbcol=seagreen]
that[vbcol=seagreen]
> as
is
> not
> Or to
>
|||I will also strongly agree. You might even wish to run a DBCC CHECKDB on it
after the restore. Of coarse if there was corruption in the original db it
will be in the restored one as well.
Andrew J. Kelly SQL MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23K4hwWTXEHA.644@.tk2msftngp13.phx.gbl...
> I have to agree with Tibor on this, the only true way to test is to
actually
> do the complete restore. I've certainly see restore issues with backups
that
> passed a verifyonly. Better safe than sorry :-)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:eD6n3bQXEHA.3640@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
> backup file is readable.
the[vbcol=seagreen]
that[vbcol=seagreen]
> the
> RESTORE
> contained in
is[vbcol=seagreen]
wrote[vbcol=seagreen]
> in
> don't
> RESTORE
> best
still[vbcol=seagreen]
that[vbcol=seagreen]
> as
is
> not
> Or to
>
|||Thanks for the advice everyone, I'll probably end up doing a verify only AND
an actual restore. Definitely better to be safe than sorry.
Thanks again
Amanda
"Amanda Toogood" <amanda@.autumncare.com.au> wrote in message
news:OkQj1kOXEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Ok, so I'm using SQL Server 2000, and I'm just wondering what the best way
> to test my backups is.
> I don't want to restore over the actual database because it's still being
> used. I realise I can just take a copy of the database and use that as the
> new database - thus allowing me to restore the original, but this is not
> actually what I want.
> Is there any way to just 'verify' that the backup will be useable? Or to
> restore it to a different database?
> Thanks in advance
> Amanda
>
|||Thanks for the advice everyone, I'll probably end up doing a verify only AND
an actual restore. Definitely better to be safe than sorry.
Thanks again
Amanda
"Amanda Toogood" <amanda@.autumncare.com.au> wrote in message
news:OkQj1kOXEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Ok, so I'm using SQL Server 2000, and I'm just wondering what the best way
> to test my backups is.
> I don't want to restore over the actual database because it's still being
> used. I realise I can just take a copy of the database and use that as the
> new database - thus allowing me to restore the original, but this is not
> actually what I want.
> Is there any way to just 'verify' that the backup will be useable? Or to
> restore it to a different database?
> Thanks in advance
> Amanda
>

best way to scrub data

We are not allowed to restore databases to the test environments that have
sensitive data like user info ,credit card info,etc..
Whats the best way to scrub this data that can be automated as opposed to
deleting these values ? Just want to hash them ..Hassan,
I've seen a post lately from Martin Bell on the topic you need pointing to
this nice article:
http://www.windowsitpro.com/Article...876/25876.html.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender. This message does not imply endorsement from Solid Quality Learning,
and it does not represent the point of view of Solid Quality Learning or any
other person, company or institution mentioned in this message
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OcpN4QbGGHA.208@.tk2msftngp13.phx.gbl...
> We are not allowed to restore databases to the test environments that have
> sensitive data like user info ,credit card info,etc..
> Whats the best way to scrub this data that can be automated as opposed to
> deleting these values ? Just want to hash them ..
>

Best way to make a database not in use?

Hi.

I am almost wrapping up a project of my own and I will have the option to restore the database, so dropping the existing one and then running the SQL script to create the database, tables, stored procedures and so on.

My question is, (using .NET 2.0 C#) - what is the best way to resolve the "cannot drop database as it is currently in use" message, or some message of the similar sort?

Even though I specified "USE MASTER" in the commandText string, there will be somewhere, in a situation, where the database will still be in use.

How can I forcefully make it NOT in use, drop it and do other things I like to?

What is the best way?

Many thanks!

Commonly we just cursor through open spids and execute KILL session_id/spid:

--2005
select session_id
from sys.dm_exec_requests
where db_name(database_id) = 'your database'

--2000 and earlier
select spid
from master..sysprocesses
where db_name(dbid) = 'your database'

|||Are you using SQL 2005? If so, in Management Studio, in Object Explorer, navigate to [Database Name]\Management\Acticity Monitor. Double click Activity Monitor - the Activity Monitor appears.

Using the Activity Monitor (with "Process Info" selected in the left-hand pane), you can see all active processes. As you have noticed, you won't be able to drop your database with active processes. Simply right-click processes that you wish to terminate, and choose "Kill Process". Note that you can see the database name as part of each process' information.

--

If you are using SQL 2000, (from my memory) an easy way to terminate all processes is to right-click the database, choose "Detach", and then when the dialog appears, there's an option to clear all existing users. After this has finished, you can drop the database.|||

Many thanks, I will give this a shot.

It's just amazing, how we can do ALL of this using SQL Server, kill processes, query the master tables as well as each of our own created tables/databases - fasinates me always :)

|||If you have any problems, or need to post again (SQL or otherwise), it's always helpful to specify product & the version of the software you are using. (SQL 2000 or SQL 2005, VB, VB.NET, C#, VS.NET 2003/2005 ... etc.)|||

Just:

use [YourDb]

alter database [YourDb] set single_user with rollback immediate

-- if there are no users with dbo permissions, you may use restricted_user instead

go

-- do your restore here

go

alter database [YourDb] set multi_user

|||gavrilenko_s - good suggestion. |||

ok, thanks for that

one more Q

if we have more than 1 SPID for a database, how can I kill them all?

I was thinking about doing this, however I think it's bad practice and prefer to do it the proper way!

DECLARE @.numberOfProcess int
SET @.numberOfProcess = 0
SET @.numberOfProcess = (select COUNT (spid) from master..sysprocesses where db_name(dbid) = 'db')
--SELECT @.numberOfProcess
IF (@.numberOfProcess > 0)
BEGIN
WHILE (@.numberOfProcess IS NOT NULL)
BEGIN
SET @.numberOfProcess = (select TOP 1 (spid) from master..sysprocesses where db_name(dbid) = 'db')
-- SELECT @.numberOfProcess
DECLARE @.SqlStatement NVARCHAR (20)
SET @.SqlStatement = 'KILL ' + CAST(@.numberOfProcess AS CHAR(2))
SELECT @.sqlStatement
EXEC sp_executesql @.SqlStatement
END

END

|||

Hi,

The method gavrilenko_s posted before is the best (which is always use for kicking users).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

best way to copy database

I need to move a database from sql 2000 to sql 2005. My research suggests that I can backup a sql 2000 db from enterprise manager and restore it with sql 2005 management studio. Are there step by step directions for this or can someone tell me how to do this?
what about logins? Are they transfered in this process. My research also suggests the I use copy database wizard by this failed on me and I can't find anything in the event log or anywhere else to tell me why. Please help. ThanksIMO, I would probably suggest backup and restore tactic for user databases. As for logins, either you dump the data into a file by doing a BCP out (bulk copy program) on master..syslogins table of the old server and BCP IN to the same table of the new server, however, since I've no experience on SQL2005, please check the structure of master..syslogins table if they're similar on both versions. As for the sysusers of user databases, you can make sp_adduser statements by doing a SELECT 'sp_adduser ' + NAME + ', ' + NAME + ' FROM MASTER..SYSLOGINS

As for syntax reference, try BOL. :)

HTH|||The BCP of syslogins trick quit working in SQL server 7.0. SQL 2005 has an all new system table structure, so I greatly doubt it would work here. For the logins, look up the CREATE LOGIN command. You will want to pay especial attention to the SID parameter. If you keep the SID for each user the same for SQL 2005 as it was on SQL 2000, they will match up with no problems. If you do not supply a SID for the CREATE USER command, a new random SID will be generated, and you will have to drop and recreate the user in the database (or recreate the login with the right SID).

Any jobs/maintenance plans that will have to come over?|||I got it over with backup and restore and all the logins are there. No job or maintenance plans need to be restored. Thanks