.bak file not fully restored

I restored a .bak file (to a different server) to find missing a lot of the data I thought it contained (and by the size of the file appears to contain).

Is there any other process to acertain the content of the bak file?

You could view the backup header to ensure which backups have been made to this media.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

*_log.ldf became giant...

excuse me for posting again but this was one of the missing.
how can i shrink _log.ldf file? it is about 1,39 gb. thanks...You can always do a quick bodge ... shut down the database service (not machine!) delete the log and then start the service again.

If you can also use a maintainence script or stored procedure that will backup the database and then 'flush the log' file.|||i tried that but it did not create a new log file and the working programs halted. so i had to restore it from backup.
by the way where will i write the script? never mind, :) i understand nothing from sql server.|||Run

BACK TRAN DBNAME WITH NOLOG
AFTER THIS

USE DBNAME
DBCC SHRINKFILE(LOGFILENAME,TRUNCATEONLY)|||in the command prompt?|||Thats a Transact-SQL Reference.

nice one sqlserver2k .|||It would be BACKUP TRAN...|||yet i do not know what to do. could you please tell me step by step like telling to a dummy, eheh. i am just a student and the guys who are responsible from server are on vacation. thanks..|||Go to the Query Analyzer and
run the query,also make sure that you replace the DBNAME by your database name and Log file name with the name of the Log file of the database.|||This ONLY works for the TEMPDB log file. Be careful of the advice you give.

Originally posted by WingMan
You can always do a quick bodge ... shut down the database service (not machine!) delete the log and then start the service again.

If you can also use a maintainence script or stored procedure that will backup the database and then 'flush the log' file.|||Hi,

I replied for this some days back, but I dont know why the info. is missing here?

Here is the simple and Quick Solution. But the database will not be available for awhile.

Use detach and attachdb

1. First detach the database by using

Example : EXEC sp_detach_db 'pubs', 'true'

2. Delete the log file (or) To be safe ,Rename the log file to some name

3. Attach the database without log file.

EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

Make sure the paths are correct.

When you attach without log file, the SQL Server will automatically create a log file with small in size , ithink it will be 1mb.

Try it . I will appreciate a response.

Have fun.

Varad01|||hi to all.

i opened the query analyzer and wrote the code:

BACK TRAN DBNAME WITH NOLOG
AFTER THIS

USE DBNAME
DBCC SHRINKFILE(LOGFILENAME,TRUNCATEONLY)

instead of dbname i wrote CariH. instead of logfilename, I wrote CariH_log, this is the big file. I got the following message: incorrect syntax near the keyword tran

friend varad01, in the announcement there is written why some posts are missing. and i did not tried your solution yet will i write this one also in the query analyzer?

thanks...Regards...|||Use the Dump tran DataBaseName with no_log ... This would do the trick for the query u asked ...|||Basically "Dump Tran" is a Command used in SQL Server 6.5 which is equivalent to "Backup Tran" in SQL Server 7.0 and 2000.

Dump Tran/Backup Tran with "NO_LOG" will clear the log file which must be used only at critical times if you dont worry about losing data.

Dump Tran/Backup Tran will not reduce the size of the log file, unless you have enabled "Auto Shrink" for the database. I can say in 2 steps the same thing, 1. Clear transactions in log files and then 2. Shrink the log file.Thats the concept involved on this.

Varad01|||Originally posted by cutcopypaste
excuse me for posting again but this was one of the missing.

how can i shrink _log.ldf file? it is about 1,39 gb. thanks...

Assuming that you are using SQL 2000, what may be happening is that the log file will not shrink unless the database is backed up. Microsofts new "feature" that makes you use the SQL backup.

The best way to keep the log file from ever becoming large is to create a backup device and then set a regularly scheduled backup for your database.

When we upgraded to SQL 2000 we had a similar problem on a database.

To setup a backup device:

Open Enterprise Manager, open Management
right click on backup and select new backup device
pick a name, then select Filename and select where you want the file stored then select ok.

Now that you have a backup device.

open databases
right click on your database, all tasks, backup database
select add...
Click on backup device, then select your device from the list, select ok

Select Database complete (or database differential if size is a concern and using this as an additional restore option is unimportant).
Select Overwrite existing media
click in the schedule checkbox then open the schedule screen
select recurring then setup how often you want this to occur. Keep in mind that this will overwrite your existing file, but sounds like this is not your primary backup.

"ok" out of everything and you should be set.

Brent|||Originally posted by cutcopypaste
excuse me for posting again but this was one of the missing.

how can i shrink _log.ldf file? it is about 1,39 gb. thanks...

Make sure you back up your dayabase before you start and immediaetl after you finish. Use the following procedure:

Using Enterprise manager:

RH click on database required.

1 All Tasks: Backup Database

2 All Tasks: Shrink Database

3 All Tasks: Truncate Log

4 All Tasks: Backup database.

This will shrink you transaction log to your pre defined optione.

(urgent)strange behaviour in excel file when previewed in SqlServer (importData)

Hi,

I am trying to import a excel file in to my database... The improt works fine.. But some of the data is missing though it is present in the excel spread sheet.

I have some data for cusip which are 9 characters and they be a combination of numbers wiht a letter.. for eg.. 123456789 or 12345R789. And in the my spread sheet there are around 73 rows.. until the 62 row it has numbers like 123456789 and from the 62 to 73 it has 12345R789 this is an just example but the data is in that format.

I went to sqlServer 2005 and imported the data using Tasks -ImportData and selected my excel spreadsheet, the user name and password for the database and i selected the sheet i want to import.. and when i preview the data ... Until the 62 row i can see the numbers and after that i cannot see any data in that column.... and when i import the data from 62 row the value is NULL...

So can some please tell me what going on why isnt that data been recognised by the importer in sql server.

Any help will be appreciated.

Regards

Karen

Seems to me that the data being imported is being imported as an int and hence the values which are not integer (row 62 and beyond) do not get imported into the table. However, this is just my guess. You might get better answer from Integration Services forum and I am moving this thread to the forum for you to get better assistance.

HTH,

|||

Yes that is correct. SSIS will look at the first few rows of Excel data to determine what the data type of the column is. As far as I know there is no easy way around that. Your best option is to ensure that one of the "R" values is in the first few rows.

Otherwise, you could do an import from .csv isntead of from Excel. That would give you more control.

Dylan.

|||

I struggle with the strange behaviour of excel imports for days now..

what helped a lot was this articel:

http://blog.lab49.com/?p=196

I hope it helps you too...

Mybe someone has a solution to my urgent excel import problem too... please look here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2129460&SiteID=1

if you have a minute left...

Thanks

Jens

|||

Thank you every one for your input... I solved the problem by changing the cell format as text and then copied all the information back.. to the excel spread sheet as text and everything works fine...

Regards

Karen

(urgent)strange behaviour in excel file when previewed in SqlServer (importData)

Hi,

I am trying to import a excel file in to my database... The improt works fine.. But some of the data is missing though it is present in the excel spread sheet.

I have some data for cusip which are 9 characters and they be a combination of numbers wiht a letter.. for eg.. 123456789 or 12345R789. And in the my spread sheet there are around 73 rows.. until the 62 row it has numbers like 123456789 and from the 62 to 73 it has 12345R789 this is an just example but the data is in that format.

I went to sqlServer 2005 and imported the data using Tasks -ImportData and selected my excel spreadsheet, the user name and password for the database and i selected the sheet i want to import.. and when i preview the data ... Until the 62 row i can see the numbers and after that i cannot see any data in that column... and when i import the data from 62 row the value is NULL...

So can some please tell me what going on why isnt that data been recognised by the importer in sql server.

Any help will be appreciated.

Regards

Karen

A short answer(work around): import throgh an Access table will address this issue.

Or change the numbers in this column to text by adding an ' in front of these numbers before your import.

I hope someone can provide more elegant solution(s) to this issue.