Moving a log file

Hi,

I am trying to move a log file in SQL2005.

I have tried unattaching a DB, then reattaching it and giving it a path to a new log file but it refuses to mount the database and simply reconnects to the old log file.

if i delete the old log file then it recreats it in the old path.

Any one know what i am doing worng.

There are several ways to handle this.

When you attach the database, the server looks in the db file to determine where the transaction log file is located. It will then display the log file and location -you can change the log file location at this step BEFORE you confirm the ATTACH.

You can also use T-SQL, DETACH and ATTACH, taking care to specify where to find the log file. See Books Online for syntax specifics.

You could do a BACKUP and RESTORE, using the WITH MOVE options for RESTORE. Again, check Books Online for syntax specifics.

|||

Check Create database with ATTACH_REBUILD_LOG option...

Check BOL for more details.

http://msdn2.microsoft.com/en-us/library/ms176061.aspx

|||

Follow as Arnie suggested tomove the file before using SP_ATTACH_DB, you could use SP_ATTACH_SINGLE_FILE_DB in this case that will recreate fresh log file.

The ATTACH_REBUILD_LOG clause enables attaching a database without requiring all of the log files. For example, when detaching a database from a production server for use as a read-only database on a reporting server, the read-only environment will not require all of the log files used in production. ATTACH_REBUILD_LOG lets you copy the database to the reporting server without having to copy over all of the production log files.

|||

Thanks guys

that worked, I dettached the DB moved the Log file then re-attached it using the create command and the ATTACH_REBUILD_LOG and specifed teh location of the log file and it worked.

Move SQL Databse (in MSDE) to another path

I have a server W2K (and MSDE) with 2 partitions one for
system (3GB) and one for data (37GB).
An application creates ALL SQL databases, in the default
folder for MSDE 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data' in the system partition.
It intended to move the databases for the data partition?
Hi,
Login to SQL Server using OSQL -Usa -Ppassword -Sservername (Enter)
This will allow you to go to a SQL prompt there type the below command to
detach the database (Only for User databases)
1>sp_detach_db <dbname>
2>go
Now you can move the MDF and LDF files to new partition say D:\MSSQL\DATA
After that use the below command in the SQL prompt:-
1>sp_attach_db
'dbname','d:\mssql\data\dbname.mdf',''d:\mssql\dat a\dbname.ldf'
2>go
The above command will attach the database back to SQl Server but in new
partition.
Note: Do the above steps for all the other user databases as well as
Pubs,Northwind.
If you need to do the same for system databases (Master, model , tempdb,
msdb), follow the below link. The link also details movement of user
databases.
http://support.microsoft.com/default...224071&sd=tech
Thanks
Hari
MCDBA
"Batista" <carlos.batista@.cmz.pt> wrote in message
news:ac3b01c43692$bc1bf350$a501280a@.phx.gbl...
> I have a server W2K (and MSDE) with 2 partitions one for
> system (3GB) and one for data (37GB).
> An application creates ALL SQL databases, in the default
> folder for MSDE 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data' in the system partition.
> It intended to move the databases for the data partition?
>

Move SQL Databse (in MSDE) to another path

I have a server W2K (and MSDE) with 2 partitions one for
system (3GB) and one for data (37GB).
An application creates ALL SQL databases, in the default
folder for MSDE 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data' in the system partition.
It intended to move the databases for the data partition?Hi,
Login to SQL Server using OSQL -Usa -Ppassword -Sservername (Enter)
This will allow you to go to a SQL prompt there type the below command to
detach the database (Only for User databases)
1>sp_detach_db <dbname>
2>go
Now you can move the MDF and LDF files to new partition say D:\MSSQL\DATA
After that use the below command in the SQL prompt:-
1>sp_attach_db
'dbname','d:\mssql\data\dbname.mdf',''d:\mssql\data\dbname.ldf'
2>go
The above command will attach the database back to SQl Server but in new
partition.
Note: Do the above steps for all the other user databases as well as
Pubs,Northwind.
If you need to do the same for system databases (Master, model , tempdb,
msdb), follow the below link. The link also details movement of user
databases.
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&sd=tech
Thanks
Hari
MCDBA
"Batista" <carlos.batista@.cmz.pt> wrote in message
news:ac3b01c43692$bc1bf350$a501280a@.phx.gbl...
> I have a server W2K (and MSDE) with 2 partitions one for
> system (3GB) and one for data (37GB).
> An application creates ALL SQL databases, in the default
> folder for MSDE 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data' in the system partition.
> It intended to move the databases for the data partition?
>

Move SQL Databse (in MSDE) to another path

I have a server W2K (and MSDE) with 2 partitions one for
system (3GB) and one for data (37GB).
An application creates ALL SQL databases, in the default
folder for MSDE 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data' in the system partition.
It intended to move the databases for the data partition?Hi,
Login to SQL Server using OSQL -Usa -Ppassword -Sservername (Enter)
This will allow you to go to a SQL prompt there type the below command to
detach the database (Only for User databases)
1>sp_detach_db <dbname>
2>go
Now you can move the MDF and LDF files to new partition say D:\MSSQL\DATA
After that use the below command in the SQL prompt:-
1>sp_attach_db
'dbname','d:\mssql\data\dbname.mdf',''d:\mssql\data\dbname.ldf'
2>go
The above command will attach the database back to SQl Server but in new
partition.
Note: Do the above steps for all the other user databases as well as
Pubs,Northwind.
If you need to do the same for system databases (Master, model , tempdb,
msdb), follow the below link. The link also details movement of user
databases.
http://support.microsoft.com/defaul...;224071&sd=tech
Thanks
Hari
MCDBA
"Batista" <carlos.batista@.cmz.pt> wrote in message
news:ac3b01c43692$bc1bf350$a501280a@.phx.gbl...
> I have a server W2K (and MSDE) with 2 partitions one for
> system (3GB) and one for data (37GB).
> An application creates ALL SQL databases, in the default
> folder for MSDE 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data' in the system partition.
> It intended to move the databases for the data partition?
>

Move MS SQL 2005 Express Edition Data Path

Hi!
I just installed Web Developer express edition on my Windows 2000 server
machine. I just wanted to know how may I change the default data path of MS
SQL Server 2005 express edition.
MeIf you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRoot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.|||If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi crosoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRo ot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.

Move MS SQL 2005 Express Edition Data Path

Hi!
I just installed Web Developer express edition on my Windows 2000 server
machine. I just wanted to know how may I change the default data path of MS
SQL Server 2005 express edition.
Me
If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRoot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.
|||If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi crosoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRo ot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.

Move MS SQL 2005 Express Edition Data Path

Hi!
I just installed Web Developer express edition on my Windows 2000 server
machine. I just wanted to know how may I change the default data path of MS
SQL Server 2005 express edition.
MeIf you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\MSSQL.1\Setup\SQLDataRoot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.|||If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi crosoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRo ot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.

move file task using variable

Hi,

Using the 'for each' container, I am setting a user variable (type = string) which will hold the source file name and path of files to process and then move to an archive directory.

In the move file task (which is in the for each loop container) I have set the destination to a file connection, and the source to : Issourcepathvariable=True; sourcevariable=user::sourcefilename

BUT

when I run the package, I get the following error : Error at File System Task : "Source Path" is not valid on operation type "move file".

I have tried all sorts of things relating to expressions etc. but cannot get this to work. I am obviousley doing something fundementally wrong...can anyone help me?

Thanks

Kevin

Kevin,

It looks like you may have a problem in the user::sourcefilename variable. If I were you I would put a breakpoint on the file system task and, when it breaks at debug-time, put a watch on user::sourcefilename. See what teh value in there actually is.

-Jamie

|||

thanks Jamie.

I have removed the file system file task from the container, and run the package with a breakpoint on the begining of every iteration of the loop. By looking at the watch window, I can see that the variable is being set correctly with the first filename in the filesystem folder.

When I add the filemove task however and add the variable as the source, the package seems to want to validate the variables contents before it has been set by the container, and I am therefore getting an error message.

Does this make sense?

Cheers, Kevin

|||

Kevin,

did you try to enter a "default" or "dummy" filename for that? I remember that I had some problems in a similar case...

|||

kevin w wrote:

thanks Jamie.

I have removed the file system file task from the container, and run the package with a breakpoint on the begining of every iteration of the loop. By looking at the watch window, I can see that the variable is being set correctly with the first filename in the filesystem folder.

When I add the filemove task however and add the variable as the source, the package seems to want to validate the variables contents before it has been set by the container, and I am therefore getting an error message.

Does this make sense?

Cheers, Kevin

Kevin,

Yes, that makes perfect sense. Set DelayValidation=TRUE on your File System Task. I *think* that will solve the problem.

-Jamie

|||

Cheers Jamie,

It's always the little things the trip me up!!!!

Your solution worked a treat.

Kevin

|||

Kevin and Jamie

I am trying to move a file from a folder to an archive folder. I do not know how to create an expression. The file name will change everyday. It is a txt file. How can I specify to move a file *.txt from a folder to archive folder. Any help will be greatly appreciated.

Thanks

Annie

|||

AYK wrote:

Kevin and Jamie

I am trying to move a file from a folder to an archive folder. I do not know how to create an expression. The file name will change everyday. It is a txt file. How can I specify to move a file *.txt from a folder to archive folder. Any help will be greatly appreciated.

Thanks

Annie

Annie,

Are you trying to move all files which match the pattern "*.txt"? If so, look into using the MULTIFILE connection manager.

Here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/10bdc56e-c5cd-4ddb-b2f7-375fe57fe8b2.htm

-Jamie

|||

Jamie

Yes I am trying to move all files with *.txt into an archive folder. I understand the use of multifile connection manager but I am not sure how I can associate the connection manager to a File System task to move file. In the file system task, for the source it asks for a single file name and does not allow me to specify the multifile connection manager

Thanks

Annie

|||

Ah yeah, you're right. Sorry about that.

In this case then you will need to use a ForEach loop with the FileEnumerator. Loop over all the *.txt files and move them one by one to your destination.

-Jamie

move file task using variable

Hi,

Using the 'for each' container, I am setting a user variable (type = string) which will hold the source file name and path of files to process and then move to an archive directory.

In the move file task (which is in the for each loop container) I have set the destination to a file connection, and the source to : Issourcepathvariable=True; sourcevariable=user::sourcefilename

BUT

when I run the package, I get the following error : Error at File System Task : "Source Path" is not valid on operation type "move file".

I have tried all sorts of things relating to expressions etc. but cannot get this to work. I am obviousley doing something fundementally wrong...can anyone help me?

Thanks

Kevin

Kevin,

It looks like you may have a problem in the user::sourcefilename variable. If I were you I would put a breakpoint on the file system task and, when it breaks at debug-time, put a watch on user::sourcefilename. See what teh value in there actually is.

-Jamie

|||

thanks Jamie.

I have removed the file system file task from the container, and run the package with a breakpoint on the begining of every iteration of the loop. By looking at the watch window, I can see that the variable is being set correctly with the first filename in the filesystem folder.

When I add the filemove task however and add the variable as the source, the package seems to want to validate the variables contents before it has been set by the container, and I am therefore getting an error message.

Does this make sense?

Cheers, Kevin

|||

Kevin,

did you try to enter a "default" or "dummy" filename for that? I remember that I had some problems in a similar case...

|||

kevin w wrote:

thanks Jamie.

I have removed the file system file task from the container, and run the package with a breakpoint on the begining of every iteration of the loop. By looking at the watch window, I can see that the variable is being set correctly with the first filename in the filesystem folder.

When I add the filemove task however and add the variable as the source, the package seems to want to validate the variables contents before it has been set by the container, and I am therefore getting an error message.

Does this make sense?

Cheers, Kevin

Kevin,

Yes, that makes perfect sense. Set DelayValidation=TRUE on your File System Task. I *think* that will solve the problem.

-Jamie

|||

Cheers Jamie,

It's always the little things the trip me up!!!!

Your solution worked a treat.

Kevin

|||

Kevin and Jamie

I am trying to move a file from a folder to an archive folder. I do not know how to create an expression. The file name will change everyday. It is a txt file. How can I specify to move a file *.txt from a folder to archive folder. Any help will be greatly appreciated.

Thanks

Annie

|||

AYK wrote:

Kevin and Jamie

I am trying to move a file from a folder to an archive folder. I do not know how to create an expression. The file name will change everyday. It is a txt file. How can I specify to move a file *.txt from a folder to archive folder. Any help will be greatly appreciated.

Thanks

Annie

Annie,

Are you trying to move all files which match the pattern "*.txt"? If so, look into using the MULTIFILE connection manager.

Here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/10bdc56e-c5cd-4ddb-b2f7-375fe57fe8b2.htm

-Jamie

|||

Jamie

Yes I am trying to move all files with *.txt into an archive folder. I understand the use of multifile connection manager but I am not sure how I can associate the connection manager to a File System task to move file. In the file system task, for the source it asks for a single file name and does not allow me to specify the multifile connection manager

Thanks

Annie

|||

Ah yeah, you're right. Sorry about that.

In this case then you will need to use a ForEach loop with the FileEnumerator. Loop over all the *.txt files and move them one by one to your destination.

-Jamie