Data files & Transaction log recovery

How do I backup a data file & transaction log from one instance of SQL to a different sql server. I have done a complete backup and now I want to restore the database but on a different SQL server, but the *.mdf and *.ldf is missing, so how do I restore them also?When you did the full backup you created a backup file. When you restore
this the mdf and ldf will be created.
On the instance you want to restore to restore from the device. On the
second tab will be the location of the mdf and ldf - this will be the
file names from the other instance so you will have to overtype with
another filename or path.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||If you restore to another SQL server you also have to think about databaseusers. The users from the database you restore stil are in the DB when it's on the new server but you not see them. You can either run the drop user or use sp_change_users_login 'Auto_Fix', 'username', NULL. This is call ophran users
/Joel|||Thanks a million. I completed the backup and did the restore on the other instance of SQL server and it worked like a charm. You're a life saver!

Data Base Shrink

I have SQL Server 2000 on WIN2k Advanced Server And I have
a Database Consists of:-
1-Single Database File .
2-single transaction log file(initial size 2 MB).
I have Observed that the transaction log file'Capacity
reaches 23 Giga Byte so I Made a Backup for the whole
database and then I tried to shrink the log file using
enterprise manager shrink database wizard.then I
discovered that the physical file capacity was not
reduced, although enterprise manager gave me a message
that the file has been shrinked.
I tried More And More But No result.
Help will be so much appreciated
Best Regards:-
Ahmed NourGood shrink article can be found :-
http://www.mssqlserver.com/faq/logs-shrinklog.asp
--
HTH
Ryan Waight, MCDBA, MCSE
"Ahmed Nour" <a_m_nour@.hotmail.com> wrote in message
news:0d7201c393e7$706c2590$a401280a@.phx.gbl...
> I have SQL Server 2000 on WIN2k Advanced Server And I have
> a Database Consists of:-
> 1-Single Database File .
> 2-single transaction log file(initial size 2 MB).
> I have Observed that the transaction log file'Capacity
> reaches 23 Giga Byte so I Made a Backup for the whole
> database and then I tried to shrink the log file using
> enterprise manager shrink database wizard.then I
> discovered that the physical file capacity was not
> reduced, although enterprise manager gave me a message
> that the file has been shrinked.
> I tried More And More But No result.
> Help will be so much appreciated
> Best Regards:-
> Ahmed Nour|||Ahmed ,
Refer to following urls
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow and AutoShrink
--
- Vishal

Data Available in a Trigger

I am creating a transaction log trigger for a table.

I would like to log the following data

The user login id: SYSTEM_USER

The User's Computer name: ?

The servers time and date: GetDate()

The trigger Action: Update, Delete, or Insert

The unique record ID for the affected table:

One column for the deleted row in xml raw:

One column for the inserted row in xml raw:

Is there a way to get the users computer name?

Consider this:

"select * from inserts for xml raw"

This is nice because i want to store the inserted and/or deleted table information as xml columns. But I would like to handle the transaction log in a way that created one transaction row add per row in the inserted or deleted table.

My end goal is to insert into the transaction log table as follows:

Lets say that my update trigger contains an inserted table with two rows and the deleted table would have the same, two rows.

I would like to insert two rows into my transaction log. with the username, date time, action and one column for the inserted row as xml raw, and one column for the deleted row as xml raw.

Anyone know how to do this?

It would be nice if i could impliment something like this:

'select * as xml raw from inserted' And it ould return as many rows as is in the inserted table, each row having one column wich represents that row in xml format.

'select * from inserted for xml raw' This is not good because it returns one row with one column whose value is an xml representation of the entire inserted table.

Thanx

Jerry Cicierega

The host_name() function will return this information, but it is not always set. Try looking this up in books online.|||

Thank you for responding. Yes this works on my system. The computer name part of my issue is solved.

The xml part of my question still stands.

Thank you !

Daily Reporting of Database and its Transaction Log Files.

Hello -

I have a database server with over 300 databases. I want that MS-SQL Server should daily report me the sizes of SQL databases along with Transaction log files by sending me an email on my address.

How can I do that. Does someone have any script which can help me to do that.

Any help will be appreciated.

Kind Regards,

Rubal JainDo you have SQL Mail configured on your server|||What is SQL Mail .. I m not very sure about it. How to installed and use that. How should I implement daily reporting.

Please help me out.

Thanks|||If you were asking abt CDO and CDONTS .. I have both installed on the server.

Is there any script available which can help me out .|||http://support.microsoft.com/default.aspx?scid=kb;de;312839&sd=tech for information about mail without using SQLMail.

http://www.searchdatabase.com/tip/1,289483,sid13_gci826453,00.html - further information.

HTH|||But what about reporting of database and transaction log files.. Can you help|||You can find out how much space a database is occupying on the hard disk by using the sp_spaceused function. However, if you want to find all database sizes at once, you have to use sp_spaceused for all databases.
USe this script:
--
CREATE PROCEDURE Usp_FindAllDBSizes
AS
SET NOCOUNT ON
DECLARE @.counter SMALLINT
DECLARE @.counter1 SMALLINT
DECLARE @.dbname VARCHAR(100)
DECLARE @.size INT
DECLARE @.size1 DECIMAL(15,2)
SET @.size1=0.0

SELECT @.counter=MAX(dbid) FROM master..sysdatabases
IF EXISTS(SELECT name FROM sysobjects WHERE name='sizeinfo')
DROP TABLE sizeinfo
CREATE TABLE sizeinfo(fileid SMALLINT, filesize DECIMAL(15,2), filename VARCHAR(1000))
WHILE @.counter > 0
BEGIN
SELECT @.dbname=name FROM master..sysdatabases WHERE dbid=@.counter
TRUNCATE TABLE sizeinfo
EXEC ('INSERT INTO sizeinfo SELECT fileid,size,filename FROM '+ @.dbname +'..SYSFILES')
SELECT @.counter1=MAX(fileid) FROM sizeinfo
WHILE @.counter1>0
BEGIN
SELECT @.size=filesize FROM sizeinfo WHERE fileid=@.counter1
SET @.size1=@.size1+@.size
SET @.counter1=@.counter1-1
END
SET @.counter=@.counter-1
SELECT @.dbname AS DBNAME,CAST(((@.size1)*0.0078125) AS DECIMAL(15,2)) AS [DBSIZE(MB)]
SET @.size1=0.0
END
SET NOCOUNT OFF
--|||Hey Satya Thanks ..

Can you integrate it with CDONTS so it can send me daily emails ??

Your Help will be really appreciated.

Kind Regards,

Rubal|||ONce you create the given SP, save the results to the text file and attach the same to mail.|||How can i save results in Text file ??|||Can anyone help ?|||Satya .. U r my friend .. I know you'll help me out for this ;)

Daily jobs do not resubmit after failure

I have a Microsoft SQL 2000 server which early each morning runs a host of jobs which append transaction data to a transaction data warehouse. On a Saturday morning the 8th the jobs failed to execute due to a network failure and according to the SQL Enterprise\SQL Server Agent\Jobs window the next run date was the 9th. Well it's Monday the 10th, the jobs have not attempted to run since the 8th and it still shows then next run date as the 9th. The Server Agent is running and the daily SQL backups have continued to run as scheduled. Does anyone know why the jobs, scheduled to run daily, are not at least attempting to do so?

Thanks for your helpHowdy

WHat version of Service Pack are you running on the server? Should be 3A if you can...

Cheers,

SG.