Filling out missing data in subsequent records?

Hi

I receive several datafiles from another system that are more or less in a Excel pivot table like format.
That is the first row representing the current data is fully filled, while subsequent rows, representing historic data are left partly empty.
Current rows and historic rows have different identifiers, e.g. rectype=0 or 1
Filling out the missing data on the historic record should be simple, if only all current rows would be filled.
Some current rows aren't filled, so the stuff like the following doesn't work:
update t1
set t1.colA =
(
select top 1 t2.colA
from mytable AS t2
where t2.rowid <= t1.rowid
and t2.cola <> 0
order by t2.rowid desc
)
from mytable AS t1

Somehow I need to check for the rectype, so I don't fill out rows with data from a previous entity

Any suggestions before I revert to using a cursor?

And while we are at it: I am in for an easy way to do this for all (about 60) colums in one move?

Before you ask:
After filling everything out we process the file to arrive at a few handy fromto tables, so we can use the correct data about the entity's status at a particular point in time elsewhere

I am using MS SQL Server 2005, and solutions are allowd to use any specific trickery that MSSQL allows.

Many thanks for any constructive thoughts

Cheers

DrioWould this do the job? My changes are highlighted

UPDATE t1
SET t1.colA =
(
SELECT TOP 1 t2.colA
FROM MyTable AS t2
WHERE t2.rowid < t1.rowid
AND t2.colA <> 0
ORDER BY t2.rowid DESC
)
FROM mytable AS t1
WHERE rectype = 0

I havn't tested this code - it's only in my head (/on the screen) so don't use it on your live data ;)|||Thnak you georgev:
for the tagline (I won't do it again; a real eye-opener)
for the small correction in my code and for the direction.

It goes wrong where there are history record after the current reccord with no data.Obvioulsy they get filled from the previous current record that hadd data.

My interim solution
1. update all current records wh data with a dummy value
2. use the fill out query
We then have to check the marked records and see if we can find a pattern that allows us the handle them in code (otherwise someone has to go through them manually; only .25% of total)

Thanks for you swift response

Cheers

Drio

FileSystem Deployment

I am still a little confused on how the file system deployment works. When I deploy my files they end up in {ROOT}/Microsoft SQL Server\90\DTS\Packages\{PKGBUNDLE}, but the connection managers still reference the original dev path. Why does it copy to a seperate directory but still reference the old path, what's the significance? Is using pkg_vars the only way to update conn mgrs to reflect the new path? (outside of using a grep util to search and replace)

I guess I am looking for design patterns, how are others handling the deployment process when deploying on the same server?

P.S. When we port this to the production server, we plan on using the same directories and steps as in staging...What you are probably after is configurations. These are designed such that you store all of the parameters which change between deployments in a configuration, or related store, and just pass the correct configuration into the package each time you use it. The location of the configuration is what may change, but the way you specify this would be chosen such that is common accross all machines.

The package storage location has no bearing on connections and neither should it. Just because my packages move the connection location an still be teh same, teh same SQL server, the same smpt server, and the same file path, if local to a machine or if using a UNC path, they can still all be the same. File system deployment vs any other deployment is just about the storage location really, not the package internals and settings.|||

Yah, I understand your point, I was more so wondering if I could have a seperate custom stored location other then under the default SQl Server\90\DTS....

|||Sorry, I get you. Not sure if it is supported, but playing with MsDtsSrvr.ini.xml may be what you want C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Some related links-

SSIS and SQL Server Instances
(http://www.sqlis.com/default.aspx?57)

Modification of MsDTSSrvr.ini.xml does not work in June CTP - Microsoft Technical Forums - It does work, see post.
(http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=61179)

Try a BOL search for that file as well.

Filegroups question

Hello everyone -

Currently the setup is a single filegroup sql machine
with each company as a new database in a single system.

Would breaking the companies into thier own filegroups
be benefitial?

or perhaps creating a instance of SQL for each company be a better approach?

thoughts / ideas would be appreciated

thanks
tonyWould breaking the companies into thier own filegroups
be benefitial?

Only if those filegroups reside on different actual drives, you might gain the performance benefit of different companies not impacting eachother performance.

or perhaps creating a instance of SQL for each company be a better approach?

Only if you have given the customer sysadmin or someother sql server wide access which I would sincerely discourage. That way the differrent customers could not access eachothers stuff.|||Thanks for the reply

I appreciate it!

take care
tony

Filegroups & I/O balacing

We're using SQL2005 standard edition.
We have a system with two data I/O devices, each having one data file we
where wondering what the best aproach is in assigning tables/data to these
groups:
- Make one filegroup with two files (one per I/O device) each equally sized
and all tables in the db are assigned to this group
According to BOL SQL Server will fill both files in the group
in a round robin way -extend files equally-
- Make two filegroups each with one file and assign individual tables to the
files in
the group manually, and try to figure out the best 'balance' ourselves.
Downside: takes a lot of time.
any hints on approach/practices?
The best performance is going to be determined by eliminating as much
contention as possible. This depends on your application and as you stated
takes a lot of time and effort.
There's still a lot more we could ask about this system but is a common
configuration that me give you some ideas.
For flexibility create three filegroups. Primary, DATA, and INDEX Create
tables and clustered indexes on the DATA filegroup. Nonclustered indexes on
the INDEX filegroup.
For performance create equally sized files within the DATA and INDEX
filegroups. Within each filegroup (except primary) create one file per
physical processor.
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>
|||What exactly are these "I/O" devices? Are these in addition to the device
the OS resides on?
Andrew J. Kelly SQL MVP
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>
|||"I/O" devices: 2x a RAID 5 config
"Andrew J. Kelly" wrote:

> What exactly are these "I/O" devices? Are these in addition to the device
> the OS resides on?
> --
> Andrew J. Kelly SQL MVP
>
> "VLNL" <VLNL@.discussions.microsoft.com> wrote in message
> news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
>
>

Filegroups & I/O balacing

We're using SQL2005 standard edition.
We have a system with two data I/O devices, each having one data file we
where wondering what the best aproach is in assigning tables/data to these
groups:
- Make one filegroup with two files (one per I/O device) each equally sized
and all tables in the db are assigned to this group
According to BOL SQL Server will fill both files in the group
in a round robin way -extend files equally-
- Make two filegroups each with one file and assign individual tables to the
files in
the group manually, and try to figure out the best 'balance' ourselves.
Downside: takes a lot of time.
any hints on approach/practices?The best performance is going to be determined by eliminating as much
contention as possible. This depends on your application and as you stated
takes a lot of time and effort.
There's still a lot more we could ask about this system but is a common
configuration that me give you some ideas.
For flexibility create three filegroups. Primary, DATA, and INDEX Create
tables and clustered indexes on the DATA filegroup. Nonclustered indexes on
the INDEX filegroup.
For performance create equally sized files within the DATA and INDEX
filegroups. Within each filegroup (except primary) create one file per
physical processor.
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>|||What exactly are these "I/O" devices? Are these in addition to the device
the OS resides on?
--
Andrew J. Kelly SQL MVP
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>|||"I/O" devices: 2x a RAID 5 config
"Andrew J. Kelly" wrote:
> What exactly are these "I/O" devices? Are these in addition to the device
> the OS resides on?
> --
> Andrew J. Kelly SQL MVP
>
> "VLNL" <VLNL@.discussions.microsoft.com> wrote in message
> news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> >
> > We're using SQL2005 standard edition.
> > We have a system with two data I/O devices, each having one data file we
> > where wondering what the best aproach is in assigning tables/data to these
> > groups:
> >
> > - Make one filegroup with two files (one per I/O device) each equally
> > sized
> > and all tables in the db are assigned to this group
> > According to BOL SQL Server will fill both files in the group
> > in a round robin way -extend files equally-
> >
> > - Make two filegroups each with one file and assign individual tables to
> > the
> > files in
> > the group manually, and try to figure out the best 'balance' ourselves.
> > Downside: takes a lot of time.
> >
> > any hints on approach/practices?
> >
>
>

Filegroups & I/O balacing

We're using SQL2005 standard edition.
We have a system with two data I/O devices, each having one data file we
where wondering what the best aproach is in assigning tables/data to these
groups:
- Make one filegroup with two files (one per I/O device) each equally sized
and all tables in the db are assigned to this group
According to BOL SQL Server will fill both files in the group
in a round robin way -extend files equally-
- Make two filegroups each with one file and assign individual tables to the
files in
the group manually, and try to figure out the best 'balance' ourselves.
Downside: takes a lot of time.
any hints on approach/practices?The best performance is going to be determined by eliminating as much
contention as possible. This depends on your application and as you stated
takes a lot of time and effort.
There's still a lot more we could ask about this system but is a common
configuration that me give you some ideas.
For flexibility create three filegroups. Primary, DATA, and INDEX Create
tables and clustered indexes on the DATA filegroup. Nonclustered indexes on
the INDEX filegroup.
For performance create equally sized files within the DATA and INDEX
filegroups. Within each filegroup (except primary) create one file per
physical processor.
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>|||What exactly are these "I/O" devices? Are these in addition to the device
the OS resides on?
Andrew J. Kelly SQL MVP
"VLNL" <VLNL@.discussions.microsoft.com> wrote in message
news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
> We're using SQL2005 standard edition.
> We have a system with two data I/O devices, each having one data file we
> where wondering what the best aproach is in assigning tables/data to these
> groups:
> - Make one filegroup with two files (one per I/O device) each equally
> sized
> and all tables in the db are assigned to this group
> According to BOL SQL Server will fill both files in the group
> in a round robin way -extend files equally-
> - Make two filegroups each with one file and assign individual tables to
> the
> files in
> the group manually, and try to figure out the best 'balance' ourselves.
> Downside: takes a lot of time.
> any hints on approach/practices?
>|||"I/O" devices: 2x a RAID 5 config
"Andrew J. Kelly" wrote:

> What exactly are these "I/O" devices? Are these in addition to the devic
e
> the OS resides on?
> --
> Andrew J. Kelly SQL MVP
>
> "VLNL" <VLNL@.discussions.microsoft.com> wrote in message
> news:97A17D39-A2B7-44D2-A9E2-0EA2D2AAF2BD@.microsoft.com...
>
>

Filegroup Restore

I need to start using filegroups, because i have a very large Database.
I want to use the primary for the system tables and to have on filegroup by
year.
Example:
Year 2002 tables goes to filegroup 2002
Year 2003 tables goes to filegroup 2003
Year 2004 tables goes to filegroup 2004
If we acidently delete records on the 2004 tables,
is posssible to only restore the filegroup 2004 and the last Log backup?It doesn't work that way. If you deleted the rows in that table, and have committed, filegroup
backup will not help you. This is because a filegroup restore work in the way that you restore the
filegroup. Then all subsequent log backups until now. And you have already committed the delete...
However, you can restore an fg from a full backup into a new database (the desired user fg along
with the PRIMARY fg). You do this using the PARTIAL option of the RESTORE command. Then you can copy
the desired data to your production database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"José Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in message
news:09FE6709-6219-4B81-AFA5-AF4D54BC1C14@.microsoft.com...
>I need to start using filegroups, because i have a very large Database.
> I want to use the primary for the system tables and to have on filegroup by
> year.
> Example:
> Year 2002 tables goes to filegroup 2002
> Year 2003 tables goes to filegroup 2003
> Year 2004 tables goes to filegroup 2004
> If we acidently delete records on the 2004 tables,
> is posssible to only restore the filegroup 2004 and the last Log backup?
>|||Thanks for your reply.
I understant that i can't only restore 2004 fg.
My idea now is to have a backup of 2002 and 2003 on tape on a safeplace.
And during the Year, i will do backups of the 2004 fg and the log.
Then if in the middle of the year, i acidentely delete some records.
I will restore 2002, 2003 fg and the last 2004 and the last log.
This will work, right?|||Please re-read my earlier reply. You cannot go back in time for a part of the database using
filegroup backup/restore.IMO, your most viable option is what I mentioned earlier and the PARTIAL
option of the RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"José Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in message
news:94F4FCAC-3E42-4391-9AD7-B9BA7F79896D@.microsoft.com...
> Thanks for your reply.
> I understant that i can't only restore 2004 fg.
> My idea now is to have a backup of 2002 and 2003 on tape on a safeplace.
> And during the Year, i will do backups of the 2004 fg and the log.
> Then if in the middle of the year, i acidentely delete some records.
> I will restore 2002, 2003 fg and the last 2004 and the last log.
> This will work, right?
>|||Thanks again for your reply, I'm on my own on this.
There is no clear documentation about this
But i have tested and it seams that it works.
Can you check this.
Here is the script:
USE MASTER
GO
CREATE DATABASE SALES
GO
ALTER DATABASE SALES ADD FILEGROUP F2002
ALTER DATABASE SALES ADD FILEGROUP F2003
ALTER DATABASE SALES ADD FILEGROUP F2004
GO
ALTER DATABASE SALES ADD FILE
(NAME='2002',
FILENAME='c:\2002.dat1')
TO FILEGROUP F2002
go
ALTER DATABASE SALES ADD FILE
(NAME='2003',
FILENAME='c:\2003.dat1')
TO FILEGROUP F2003
go
ALTER DATABASE SALES ADD FILE
(NAME='2004',
FILENAME='c:\2004.dat1')
TO FILEGROUP F2004
go
use SALES
CREATE TABLE T_2002 (id int) ON F2002
CREATE TABLE T_2003 (id int) ON F2003
CREATE TABLE T_2004 (id int) ON F2004
INSERT INTO T_2002 VALUES (1)
INSERT INTO T_2003 VALUES (1)
INSERT INTO T_2004 VALUES (1)
GO
--
--BACKUP FOR TAPES--
--
USE master
BACKUP DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
TO disk ='C:\a\Sales_PRIMARY.bak'
BACKUP DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
TO disk ='C:\a\Sales_F2002.bak'
BACKUP DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
TO disk ='C:\a\Sales_F2003.bak'
--
--RECORDS OK--
--
INSERT INTO T_2004 VALUES (2)
INSERT INTO T_2004 VALUES (3)
INSERT INTO T_2004 VALUES (4)
BACKUP DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
TO disk ='C:\a\Sales_F2004.bak'
BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log'
GO
--
--Acidental Delete--
--
use Sales
delete from T_2004
--TAIL LOG BACKUP
BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
---
---
---
--MUST DO ALL THE RESTORE--
---
USE master
RESTORE DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\a\Sales_PRIMARY.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
FROM DISK = 'C:\a\Sales_F2002.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
FROM DISK = 'C:\a\Sales_F2003.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
FROM DISK = 'C:\a\Sales_F2004.bak'
WITH noRECOVERY
RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY|||Yes, your script work, but you did indeed restore all parts of the database up to the point in time
prior to the accidental DELETE. I thought that you wanted to restore only a part of the database,
the part where you did the accidental delete? Perhaps I misunderstood you.
I've modified your script slightly below, so you can run it several times without needing to clean
up. Please continue on my modified script if you want to elaborate further. :-)
drop database sales
go
USE MASTER
GO
CREATE DATABASE SALES
GO
ALTER DATABASE SALES ADD FILEGROUP F2002
ALTER DATABASE SALES ADD FILEGROUP F2003
ALTER DATABASE SALES ADD FILEGROUP F2004
GO
ALTER DATABASE SALES ADD FILE
(NAME='2002',
FILENAME='c:\2002.dat1')
TO FILEGROUP F2002
go
ALTER DATABASE SALES ADD FILE
(NAME='2003',
FILENAME='c:\2003.dat1')
TO FILEGROUP F2003
go
ALTER DATABASE SALES ADD FILE
(NAME='2004',
FILENAME='c:\2004.dat1')
TO FILEGROUP F2004
go
CREATE TABLE Sales..T_2002 (id int) ON F2002
CREATE TABLE Sales..T_2003 (id int) ON F2003
CREATE TABLE Sales..T_2004 (id int) ON F2004
INSERT INTO Sales..T_2002 VALUES (1)
INSERT INTO Sales..T_2003 VALUES (1)
INSERT INTO Sales..T_2004 VALUES (1)
GO
--
--BACKUP FOR TAPES--
--
BACKUP DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
TO disk ='C:\a\Sales_PRIMARY.bak'
WITH INIT
BACKUP DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
TO disk ='C:\a\Sales_F2002.bak'
WITH INIT
BACKUP DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
TO disk ='C:\a\Sales_F2003.bak'
WITH INIT
--
--RECORDS OK--
--
INSERT INTO sales..T_2004 VALUES (2)
INSERT INTO sales..T_2004 VALUES (3)
INSERT INTO sales..T_2004 VALUES (4)
select * from sales..T_2004
BACKUP DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
TO disk ='C:\a\Sales_F2004.bak'
WITH INIT
BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log' WITH INIT
GO
--
--Acidental Delete--
--
delete from sales..T_2004
INSERT INTO sales..T_2003 VALUES (2)
--TAIL LOG BACKUP
BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
---
---
---
--MUST DO ALL THE RESTORE--
---
RESTORE DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\a\Sales_PRIMARY.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
FROM DISK = 'C:\a\Sales_F2002.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
FROM DISK = 'C:\a\Sales_F2003.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
FROM DISK = 'C:\a\Sales_F2004.bak'
WITH noRECOVERY
RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY
SELECT * FROM sales..T_2004
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"José Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in message
news:16AC95DA-C148-4CF9-A338-D9C2F11F0B8B@.microsoft.com...
> Thanks again for your reply, I'm on my own on this.
> There is no clear documentation about this
> But i have tested and it seams that it works.
> Can you check this.
> Here is the script:
> USE MASTER
> GO
> CREATE DATABASE SALES
> GO
> ALTER DATABASE SALES ADD FILEGROUP F2002
> ALTER DATABASE SALES ADD FILEGROUP F2003
> ALTER DATABASE SALES ADD FILEGROUP F2004
> GO
> ALTER DATABASE SALES ADD FILE
> (NAME='2002',
> FILENAME='c:\2002.dat1')
> TO FILEGROUP F2002
> go
> ALTER DATABASE SALES ADD FILE
> (NAME='2003',
> FILENAME='c:\2003.dat1')
> TO FILEGROUP F2003
> go
> ALTER DATABASE SALES ADD FILE
> (NAME='2004',
> FILENAME='c:\2004.dat1')
> TO FILEGROUP F2004
> go
> use SALES
> CREATE TABLE T_2002 (id int) ON F2002
> CREATE TABLE T_2003 (id int) ON F2003
> CREATE TABLE T_2004 (id int) ON F2004
> INSERT INTO T_2002 VALUES (1)
> INSERT INTO T_2003 VALUES (1)
> INSERT INTO T_2004 VALUES (1)
> GO
> --
> --BACKUP FOR TAPES--
> --
> USE master
> BACKUP DATABASE SALES
> FILE = 'Sales',
> FILEGROUP = 'PRIMARY'
> TO disk ='C:\a\Sales_PRIMARY.bak'
> BACKUP DATABASE SALES
> FILE = '2002',
> FILEGROUP = 'F2002'
> TO disk ='C:\a\Sales_F2002.bak'
> BACKUP DATABASE SALES
> FILE = '2003',
> FILEGROUP = 'F2003'
> TO disk ='C:\a\Sales_F2003.bak'
> --
> --RECORDS OK--
> --
> INSERT INTO T_2004 VALUES (2)
> INSERT INTO T_2004 VALUES (3)
> INSERT INTO T_2004 VALUES (4)
> BACKUP DATABASE SALES
> FILE = '2004',
> FILEGROUP = 'F2004'
> TO disk ='C:\a\Sales_F2004.bak'
> BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log'
> GO
> --
> --Acidental Delete--
> --
> use Sales
> delete from T_2004
> --TAIL LOG BACKUP
> BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
> ---
> ---
> ---
> --MUST DO ALL THE RESTORE--
> ---
> USE master
> RESTORE DATABASE SALES
> FILE = 'Sales',
> FILEGROUP = 'PRIMARY'
> FROM DISK = 'C:\a\Sales_PRIMARY.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2002',
> FILEGROUP = 'F2002'
> FROM DISK = 'C:\a\Sales_F2002.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2003',
> FILEGROUP = 'F2003'
> FROM DISK = 'C:\a\Sales_F2003.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2004',
> FILEGROUP = 'F2004'
> FROM DISK = 'C:\a\Sales_F2004.bak'
> WITH noRECOVERY
> RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY
>
>|||Yes, on the bigining that was my idea.
But i saw that that was not possible.
At least with this script, i can do one backup of the past year and store it
on one tape on a safe place.
And daily just do a backup of the current year.
with this i will save time and disk space of doing full Backups.
This is possibles, right?
Can you point out some links regarding Filegroups Backups?
Thanks for your help.|||Well, with filegroups backup, you need to apply all subsequent transaction log backups after the
filegroup backup occurred. Say you do a fg backup Jan 1 2002. Then at Feb 23 2004 you want to
restore that filegroup backup. After restoring that filegroup backup, you need to restore all
transaction log backups you have dine since Jan 1 2002 until Feb 23 2004! Until all those
transaction log backups has been restored, the database is *not* available. So, make sure that you
*really* test these scenarios well. What I've learned about filegroup backups, I have learned from
Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"José Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in message
news:86095755-1364-49F6-8450-51A26DC1DC56@.microsoft.com...
> Yes, on the bigining that was my idea.
> But i saw that that was not possible.
> At least with this script, i can do one backup of the past year and store it
> on one tape on a safe place.
> And daily just do a backup of the current year.
> with this i will save time and disk space of doing full Backups.
> This is possibles, right?
> Can you point out some links regarding Filegroups Backups?
> Thanks for your help.
>|||I will pay attention to that.
Thanks for your help.

Filegroup Restore

I need to start using filegroups, because i have a very large Database.
I want to use the primary for the system tables and to have on filegroup by
year.
Example:
Year 2002 tables goes to filegroup 2002
Year 2003 tables goes to filegroup 2003
Year 2004 tables goes to filegroup 2004
If we acidently delete records on the 2004 tables,
is posssible to only restore the filegroup 2004 and the last Log backup?It doesn't work that way. If you deleted the rows in that table, and have co
mmitted, filegroup
backup will not help you. This is because a filegroup restore work in the wa
y that you restore the
filegroup. Then all subsequent log backups until now. And you have already c
ommitted the delete...
However, you can restore an fg from a full backup into a new database (the d
esired user fg along
with the PRIMARY fg). You do this using the PARTIAL option of the RESTORE co
mmand. Then you can copy
the desired data to your production database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jos Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in mess
age
news:09FE6709-6219-4B81-AFA5-AF4D54BC1C14@.microsoft.com...
>I need to start using filegroups, because i have a very large Database.
> I want to use the primary for the system tables and to have on filegroup b
y
> year.
> Example:
> Year 2002 tables goes to filegroup 2002
> Year 2003 tables goes to filegroup 2003
> Year 2004 tables goes to filegroup 2004
> If we acidently delete records on the 2004 tables,
> is posssible to only restore the filegroup 2004 and the last Log backup?
>|||Thanks for your reply.
I understant that i can't only restore 2004 fg.
My idea now is to have a backup of 2002 and 2003 on tape on a safeplace.
And during the Year, i will do backups of the 2004 fg and the log.
Then if in the middle of the year, i acidentely delete some records.
I will restore 2002, 2003 fg and the last 2004 and the last log.
This will work, right?|||Please re-read my earlier reply. You cannot go back in time for a part of th
e database using
filegroup backup/restore.IMO, your most viable option is what I mentioned ea
rlier and the PARTIAL
option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jos Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in mess
age
news:94F4FCAC-3E42-4391-9AD7-B9BA7F79896D@.microsoft.com...
> Thanks for your reply.
> I understant that i can't only restore 2004 fg.
> My idea now is to have a backup of 2002 and 2003 on tape on a safeplace.
> And during the Year, i will do backups of the 2004 fg and the log.
> Then if in the middle of the year, i acidentely delete some records.
> I will restore 2002, 2003 fg and the last 2004 and the last log.
> This will work, right?
>|||Thanks again for your reply, I'm on my own on this.
There is no clear documentation about this
But i have tested and it seams that it works.
Can you check this.
Here is the script:
USE MASTER
GO
CREATE DATABASE SALES
GO
ALTER DATABASE SALES ADD FILEGROUP F2002
ALTER DATABASE SALES ADD FILEGROUP F2003
ALTER DATABASE SALES ADD FILEGROUP F2004
GO
ALTER DATABASE SALES ADD FILE
(NAME='2002',
FILENAME='c:\2002.dat1')
TO FILEGROUP F2002
go
ALTER DATABASE SALES ADD FILE
(NAME='2003',
FILENAME='c:\2003.dat1')
TO FILEGROUP F2003
go
ALTER DATABASE SALES ADD FILE
(NAME='2004',
FILENAME='c:\2004.dat1')
TO FILEGROUP F2004
go
use SALES
CREATE TABLE T_2002 (id int) ON F2002
CREATE TABLE T_2003 (id int) ON F2003
CREATE TABLE T_2004 (id int) ON F2004
INSERT INTO T_2002 VALUES (1)
INSERT INTO T_2003 VALUES (1)
INSERT INTO T_2004 VALUES (1)
GO
--BACKUP FOR TAPES--
--
USE master
BACKUP DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
TO disk ='C:\a\Sales_PRIMARY.bak'
BACKUP DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
TO disk ='C:\a\Sales_F2002.bak'
BACKUP DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
TO disk ='C:\a\Sales_F2003.bak'
--RECORDS OK--
--
INSERT INTO T_2004 VALUES (2)
INSERT INTO T_2004 VALUES (3)
INSERT INTO T_2004 VALUES (4)
BACKUP DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
TO disk ='C:\a\Sales_F2004.bak'
BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log'
GO
--Acidental Delete--
--
use Sales
delete from T_2004
--TAIL LOG BACKUP
BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
---
---
---
--MUST DO ALL THE RESTORE--
---
USE master
RESTORE DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\a\Sales_PRIMARY.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
FROM DISK = 'C:\a\Sales_F2002.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
FROM DISK = 'C:\a\Sales_F2003.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
FROM DISK = 'C:\a\Sales_F2004.bak'
WITH noRECOVERY
RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY|||Yes, your script work, but you did indeed restore all parts of the database
up to the point in time
prior to the accidental DELETE. I thought that you wanted to restore only a
part of the database,
the part where you did the accidental delete? Perhaps I misunderstood you.
I've modified your script slightly below, so you can run it several times wi
thout needing to clean
up. Please continue on my modified script if you want to elaborate further.
:-)
drop database sales
go
USE MASTER
GO
CREATE DATABASE SALES
GO
ALTER DATABASE SALES ADD FILEGROUP F2002
ALTER DATABASE SALES ADD FILEGROUP F2003
ALTER DATABASE SALES ADD FILEGROUP F2004
GO
ALTER DATABASE SALES ADD FILE
(NAME='2002',
FILENAME='c:\2002.dat1')
TO FILEGROUP F2002
go
ALTER DATABASE SALES ADD FILE
(NAME='2003',
FILENAME='c:\2003.dat1')
TO FILEGROUP F2003
go
ALTER DATABASE SALES ADD FILE
(NAME='2004',
FILENAME='c:\2004.dat1')
TO FILEGROUP F2004
go
CREATE TABLE Sales..T_2002 (id int) ON F2002
CREATE TABLE Sales..T_2003 (id int) ON F2003
CREATE TABLE Sales..T_2004 (id int) ON F2004
INSERT INTO Sales..T_2002 VALUES (1)
INSERT INTO Sales..T_2003 VALUES (1)
INSERT INTO Sales..T_2004 VALUES (1)
GO
--BACKUP FOR TAPES--
--
BACKUP DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
TO disk ='C:\a\Sales_PRIMARY.bak'
WITH INIT
BACKUP DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
TO disk ='C:\a\Sales_F2002.bak'
WITH INIT
BACKUP DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
TO disk ='C:\a\Sales_F2003.bak'
WITH INIT
--RECORDS OK--
--
INSERT INTO sales..T_2004 VALUES (2)
INSERT INTO sales..T_2004 VALUES (3)
INSERT INTO sales..T_2004 VALUES (4)
select * from sales..T_2004
BACKUP DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
TO disk ='C:\a\Sales_F2004.bak'
WITH INIT
BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log' WITH INIT
GO
--Acidental Delete--
--
delete from sales..T_2004
INSERT INTO sales..T_2003 VALUES (2)
--TAIL LOG BACKUP
BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
---
---
---
--MUST DO ALL THE RESTORE--
---
RESTORE DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\a\Sales_PRIMARY.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
FROM DISK = 'C:\a\Sales_F2002.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
FROM DISK = 'C:\a\Sales_F2003.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
FROM DISK = 'C:\a\Sales_F2004.bak'
WITH noRECOVERY
RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY
SELECT * FROM sales..T_2004
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jos Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in mess
age
news:16AC95DA-C148-4CF9-A338-D9C2F11F0B8B@.microsoft.com...
> Thanks again for your reply, I'm on my own on this.
> There is no clear documentation about this
> But i have tested and it seams that it works.
> Can you check this.
> Here is the script:
> USE MASTER
> GO
> CREATE DATABASE SALES
> GO
> ALTER DATABASE SALES ADD FILEGROUP F2002
> ALTER DATABASE SALES ADD FILEGROUP F2003
> ALTER DATABASE SALES ADD FILEGROUP F2004
> GO
> ALTER DATABASE SALES ADD FILE
> (NAME='2002',
> FILENAME='c:\2002.dat1')
> TO FILEGROUP F2002
> go
> ALTER DATABASE SALES ADD FILE
> (NAME='2003',
> FILENAME='c:\2003.dat1')
> TO FILEGROUP F2003
> go
> ALTER DATABASE SALES ADD FILE
> (NAME='2004',
> FILENAME='c:\2004.dat1')
> TO FILEGROUP F2004
> go
> use SALES
> CREATE TABLE T_2002 (id int) ON F2002
> CREATE TABLE T_2003 (id int) ON F2003
> CREATE TABLE T_2004 (id int) ON F2004
> INSERT INTO T_2002 VALUES (1)
> INSERT INTO T_2003 VALUES (1)
> INSERT INTO T_2004 VALUES (1)
> GO
> --
> --BACKUP FOR TAPES--
> --
> USE master
> BACKUP DATABASE SALES
> FILE = 'Sales',
> FILEGROUP = 'PRIMARY'
> TO disk ='C:\a\Sales_PRIMARY.bak'
> BACKUP DATABASE SALES
> FILE = '2002',
> FILEGROUP = 'F2002'
> TO disk ='C:\a\Sales_F2002.bak'
> BACKUP DATABASE SALES
> FILE = '2003',
> FILEGROUP = 'F2003'
> TO disk ='C:\a\Sales_F2003.bak'
> --
> --RECORDS OK--
> --
> INSERT INTO T_2004 VALUES (2)
> INSERT INTO T_2004 VALUES (3)
> INSERT INTO T_2004 VALUES (4)
> BACKUP DATABASE SALES
> FILE = '2004',
> FILEGROUP = 'F2004'
> TO disk ='C:\a\Sales_F2004.bak'
> BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log'
> GO
> --
> --Acidental Delete--
> --
> use Sales
> delete from T_2004
> --TAIL LOG BACKUP
> BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
> ---
> ---
> ---
> --MUST DO ALL THE RESTORE--
> ---
> USE master
> RESTORE DATABASE SALES
> FILE = 'Sales',
> FILEGROUP = 'PRIMARY'
> FROM DISK = 'C:\a\Sales_PRIMARY.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2002',
> FILEGROUP = 'F2002'
> FROM DISK = 'C:\a\Sales_F2002.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2003',
> FILEGROUP = 'F2003'
> FROM DISK = 'C:\a\Sales_F2003.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2004',
> FILEGROUP = 'F2004'
> FROM DISK = 'C:\a\Sales_F2004.bak'
> WITH noRECOVERY
> RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY
>
>|||Yes, on the bigining that was my idea.
But i saw that that was not possible.
At least with this script, i can do one backup of the past year and store it
on one tape on a safe place.
And daily just do a backup of the current year.
with this i will save time and disk space of doing full Backups.
This is possibles, right?
Can you point out some links regarding Filegroups Backups?
Thanks for your help.|||Well, with filegroups backup, you need to apply all subsequent transaction l
og backups after the
filegroup backup occurred. Say you do a fg backup Jan 1 2002. Then at Feb 23
2004 you want to
restore that filegroup backup. After restoring that filegroup backup, you ne
ed to restore all
transaction log backups you have dine since Jan 1 2002 until Feb 23 2004! Un
til all those
transaction log backups has been restored, the database is *not* available.
So, make sure that you
*really* test these scenarios well. What I've learned about filegroup backup
s, I have learned from
Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jos Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in mess
age
news:86095755-1364-49F6-8450-51A26DC1DC56@.microsoft.com...
> Yes, on the bigining that was my idea.
> But i saw that that was not possible.
> At least with this script, i can do one backup of the past year and store
it
> on one tape on a safe place.
> And daily just do a backup of the current year.
> with this i will save time and disk space of doing full Backups.
> This is possibles, right?
> Can you point out some links regarding Filegroups Backups?
> Thanks for your help.
>|||I will pay attention to that.
Thanks for your help.

Filegroup Restore

I need to start using filegroups, because i have a very large Database.
I want to use the primary for the system tables and to have on filegroup by
year.
Example:
Year 2002 tables goes to filegroup 2002
Year 2003 tables goes to filegroup 2003
Year 2004 tables goes to filegroup 2004
If we acidently delete records on the 2004 tables,
is posssible to only restore the filegroup 2004 and the last Log backup?
It doesn't work that way. If you deleted the rows in that table, and have committed, filegroup
backup will not help you. This is because a filegroup restore work in the way that you restore the
filegroup. Then all subsequent log backups until now. And you have already committed the delete...
However, you can restore an fg from a full backup into a new database (the desired user fg along
with the PRIMARY fg). You do this using the PARTIAL option of the RESTORE command. Then you can copy
the desired data to your production database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jos Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in message
news:09FE6709-6219-4B81-AFA5-AF4D54BC1C14@.microsoft.com...
>I need to start using filegroups, because i have a very large Database.
> I want to use the primary for the system tables and to have on filegroup by
> year.
> Example:
> Year 2002 tables goes to filegroup 2002
> Year 2003 tables goes to filegroup 2003
> Year 2004 tables goes to filegroup 2004
> If we acidently delete records on the 2004 tables,
> is posssible to only restore the filegroup 2004 and the last Log backup?
>
|||Thanks for your reply.
I understant that i can't only restore 2004 fg.
My idea now is to have a backup of 2002 and 2003 on tape on a safeplace.
And during the Year, i will do backups of the 2004 fg and the log.
Then if in the middle of the year, i acidentely delete some records.
I will restore 2002, 2003 fg and the last 2004 and the last log.
This will work, right?
|||Please re-read my earlier reply. You cannot go back in time for a part of the database using
filegroup backup/restore.IMO, your most viable option is what I mentioned earlier and the PARTIAL
option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jos Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in message
news:94F4FCAC-3E42-4391-9AD7-B9BA7F79896D@.microsoft.com...
> Thanks for your reply.
> I understant that i can't only restore 2004 fg.
> My idea now is to have a backup of 2002 and 2003 on tape on a safeplace.
> And during the Year, i will do backups of the 2004 fg and the log.
> Then if in the middle of the year, i acidentely delete some records.
> I will restore 2002, 2003 fg and the last 2004 and the last log.
> This will work, right?
>
|||Thanks again for your reply, I'm on my own on this.
There is no clear documentation about this
But i have tested and it seams that it works.
Can you check this.
Here is the script:
USE MASTER
GO
CREATE DATABASE SALES
GO
ALTER DATABASE SALES ADD FILEGROUP F2002
ALTER DATABASE SALES ADD FILEGROUP F2003
ALTER DATABASE SALES ADD FILEGROUP F2004
GO
ALTER DATABASE SALES ADD FILE
(NAME='2002',
FILENAME='c:\2002.dat1')
TO FILEGROUP F2002
go
ALTER DATABASE SALES ADD FILE
(NAME='2003',
FILENAME='c:\2003.dat1')
TO FILEGROUP F2003
go
ALTER DATABASE SALES ADD FILE
(NAME='2004',
FILENAME='c:\2004.dat1')
TO FILEGROUP F2004
go
use SALES
CREATE TABLE T_2002 (id int) ON F2002
CREATE TABLE T_2003 (id int) ON F2003
CREATE TABLE T_2004 (id int) ON F2004
INSERT INTO T_2002 VALUES (1)
INSERT INTO T_2003 VALUES (1)
INSERT INTO T_2004 VALUES (1)
GO
--BACKUP FOR TAPES--
USE master
BACKUP DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
TO disk ='C:\a\Sales_PRIMARY.bak'
BACKUP DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
TO disk ='C:\a\Sales_F2002.bak'
BACKUP DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
TO disk ='C:\a\Sales_F2003.bak'
--RECORDS OK--
INSERT INTO T_2004 VALUES (2)
INSERT INTO T_2004 VALUES (3)
INSERT INTO T_2004 VALUES (4)
BACKUP DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
TO disk ='C:\a\Sales_F2004.bak'
BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log'
GO
--Acidental Delete--
use Sales
delete from T_2004
--TAIL LOG BACKUP
BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
---
--MUST DO ALL THE RESTORE--
USE master
RESTORE DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\a\Sales_PRIMARY.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
FROM DISK = 'C:\a\Sales_F2002.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
FROM DISK = 'C:\a\Sales_F2003.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
FROM DISK = 'C:\a\Sales_F2004.bak'
WITH noRECOVERY
RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY
|||Yes, your script work, but you did indeed restore all parts of the database up to the point in time
prior to the accidental DELETE. I thought that you wanted to restore only a part of the database,
the part where you did the accidental delete? Perhaps I misunderstood you.
I've modified your script slightly below, so you can run it several times without needing to clean
up. Please continue on my modified script if you want to elaborate further. :-)
drop database sales
go
USE MASTER
GO
CREATE DATABASE SALES
GO
ALTER DATABASE SALES ADD FILEGROUP F2002
ALTER DATABASE SALES ADD FILEGROUP F2003
ALTER DATABASE SALES ADD FILEGROUP F2004
GO
ALTER DATABASE SALES ADD FILE
(NAME='2002',
FILENAME='c:\2002.dat1')
TO FILEGROUP F2002
go
ALTER DATABASE SALES ADD FILE
(NAME='2003',
FILENAME='c:\2003.dat1')
TO FILEGROUP F2003
go
ALTER DATABASE SALES ADD FILE
(NAME='2004',
FILENAME='c:\2004.dat1')
TO FILEGROUP F2004
go
CREATE TABLE Sales..T_2002 (id int) ON F2002
CREATE TABLE Sales..T_2003 (id int) ON F2003
CREATE TABLE Sales..T_2004 (id int) ON F2004
INSERT INTO Sales..T_2002 VALUES (1)
INSERT INTO Sales..T_2003 VALUES (1)
INSERT INTO Sales..T_2004 VALUES (1)
GO
--BACKUP FOR TAPES--
BACKUP DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
TO disk ='C:\a\Sales_PRIMARY.bak'
WITH INIT
BACKUP DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
TO disk ='C:\a\Sales_F2002.bak'
WITH INIT
BACKUP DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
TO disk ='C:\a\Sales_F2003.bak'
WITH INIT
--RECORDS OK--
INSERT INTO sales..T_2004 VALUES (2)
INSERT INTO sales..T_2004 VALUES (3)
INSERT INTO sales..T_2004 VALUES (4)
select * from sales..T_2004
BACKUP DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
TO disk ='C:\a\Sales_F2004.bak'
WITH INIT
BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log' WITH INIT
GO
--Acidental Delete--
delete from sales..T_2004
INSERT INTO sales..T_2003 VALUES (2)
--TAIL LOG BACKUP
BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
---
--MUST DO ALL THE RESTORE--
RESTORE DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\a\Sales_PRIMARY.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
FROM DISK = 'C:\a\Sales_F2002.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
FROM DISK = 'C:\a\Sales_F2003.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
FROM DISK = 'C:\a\Sales_F2004.bak'
WITH noRECOVERY
RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY
SELECT * FROM sales..T_2004
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jos Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in message
news:16AC95DA-C148-4CF9-A338-D9C2F11F0B8B@.microsoft.com...
> Thanks again for your reply, I'm on my own on this.
> There is no clear documentation about this
> But i have tested and it seams that it works.
> Can you check this.
> Here is the script:
> USE MASTER
> GO
> CREATE DATABASE SALES
> GO
> ALTER DATABASE SALES ADD FILEGROUP F2002
> ALTER DATABASE SALES ADD FILEGROUP F2003
> ALTER DATABASE SALES ADD FILEGROUP F2004
> GO
> ALTER DATABASE SALES ADD FILE
> (NAME='2002',
> FILENAME='c:\2002.dat1')
> TO FILEGROUP F2002
> go
> ALTER DATABASE SALES ADD FILE
> (NAME='2003',
> FILENAME='c:\2003.dat1')
> TO FILEGROUP F2003
> go
> ALTER DATABASE SALES ADD FILE
> (NAME='2004',
> FILENAME='c:\2004.dat1')
> TO FILEGROUP F2004
> go
> use SALES
> CREATE TABLE T_2002 (id int) ON F2002
> CREATE TABLE T_2003 (id int) ON F2003
> CREATE TABLE T_2004 (id int) ON F2004
> INSERT INTO T_2002 VALUES (1)
> INSERT INTO T_2003 VALUES (1)
> INSERT INTO T_2004 VALUES (1)
> GO
> --
> --BACKUP FOR TAPES--
> --
> USE master
> BACKUP DATABASE SALES
> FILE = 'Sales',
> FILEGROUP = 'PRIMARY'
> TO disk ='C:\a\Sales_PRIMARY.bak'
> BACKUP DATABASE SALES
> FILE = '2002',
> FILEGROUP = 'F2002'
> TO disk ='C:\a\Sales_F2002.bak'
> BACKUP DATABASE SALES
> FILE = '2003',
> FILEGROUP = 'F2003'
> TO disk ='C:\a\Sales_F2003.bak'
> --
> --RECORDS OK--
> --
> INSERT INTO T_2004 VALUES (2)
> INSERT INTO T_2004 VALUES (3)
> INSERT INTO T_2004 VALUES (4)
> BACKUP DATABASE SALES
> FILE = '2004',
> FILEGROUP = 'F2004'
> TO disk ='C:\a\Sales_F2004.bak'
> BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log'
> GO
> --
> --Acidental Delete--
> --
> use Sales
> delete from T_2004
> --TAIL LOG BACKUP
> BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
> --MUST DO ALL THE RESTORE--
> USE master
> RESTORE DATABASE SALES
> FILE = 'Sales',
> FILEGROUP = 'PRIMARY'
> FROM DISK = 'C:\a\Sales_PRIMARY.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2002',
> FILEGROUP = 'F2002'
> FROM DISK = 'C:\a\Sales_F2002.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2003',
> FILEGROUP = 'F2003'
> FROM DISK = 'C:\a\Sales_F2003.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2004',
> FILEGROUP = 'F2004'
> FROM DISK = 'C:\a\Sales_F2004.bak'
> WITH noRECOVERY
> RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY
>
>
|||Yes, on the bigining that was my idea.
But i saw that that was not possible.
At least with this script, i can do one backup of the past year and store it
on one tape on a safe place.
And daily just do a backup of the current year.
with this i will save time and disk space of doing full Backups.
This is possibles, right?
Can you point out some links regarding Filegroups Backups?
Thanks for your help.
|||Well, with filegroups backup, you need to apply all subsequent transaction log backups after the
filegroup backup occurred. Say you do a fg backup Jan 1 2002. Then at Feb 23 2004 you want to
restore that filegroup backup. After restoring that filegroup backup, you need to restore all
transaction log backups you have dine since Jan 1 2002 until Feb 23 2004! Until all those
transaction log backups has been restored, the database is *not* available. So, make sure that you
*really* test these scenarios well. What I've learned about filegroup backups, I have learned from
Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jos Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in message
news:86095755-1364-49F6-8450-51A26DC1DC56@.microsoft.com...
> Yes, on the bigining that was my idea.
> But i saw that that was not possible.
> At least with this script, i can do one backup of the past year and store it
> on one tape on a safe place.
> And daily just do a backup of the current year.
> with this i will save time and disk space of doing full Backups.
> This is possibles, right?
> Can you point out some links regarding Filegroups Backups?
> Thanks for your help.
>
|||I will pay attention to that.
Thanks for your help.

Filegroup

When a database is created, all system objects will be stored in this
default filegroup, and also the later user tables, right ?
So if I then create another filegroup as default filegroup, are all previous
user tables also move to this default filegroup ?
No. (Re)-create the clustered index on a table to move it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> When a database is created, all system objects will be stored in this
> default filegroup, and also the later user tables, right ?
> So if I then create another filegroup as default filegroup, are all previous
> user tables also move to this default filegroup ?
>
|||So how about:
When I create a databbase in EM, I also create secondary database file in
secondary file group in the dialog box.
Wiil all user tables be stored in the secondary database file in the
secondary file group ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> No. (Re)-create the clustered index on a table to move it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
previous
>
|||Current or future tables? To have current tables move to the filegroup, (re) create the tables
clustered index (as I mentioned earlier). For future tables, either specify ON <FGNAME> when you
create the table or index, or make the file groups the default filegroups for the database (see the
ALTER DATABASE command).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uCcjB8B0EHA.3808@.TK2MSFTNGP15.phx.gbl...
> So how about:
> When I create a databbase in EM, I also create secondary database file in
> secondary file group in the dialog box.
> Wiil all user tables be stored in the secondary database file in the
> secondary file group ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> previous
>

Filegroup

When a database is created, all system objects will be stored in this
default filegroup, and also the later user tables, right ?
So if I then create another filegroup as default filegroup, are all previous
user tables also move to this default filegroup ?No. (Re)-create the clustered index on a table to move it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> When a database is created, all system objects will be stored in this
> default filegroup, and also the later user tables, right ?
> So if I then create another filegroup as default filegroup, are all previous
> user tables also move to this default filegroup ?
>|||So how about:
When I create a databbase in EM, I also create secondary database file in
secondary file group in the dialog box.
Wiil all user tables be stored in the secondary database file in the
secondary file group ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> No. (Re)-create the clustered index on a table to move it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> > When a database is created, all system objects will be stored in this
> > default filegroup, and also the later user tables, right ?
> > So if I then create another filegroup as default filegroup, are all
previous
> > user tables also move to this default filegroup ?
> >
> >
>|||Current or future tables? To have current tables move to the filegroup, (re) create the tables
clustered index (as I mentioned earlier). For future tables, either specify ON <FGNAME> when you
create the table or index, or make the file groups the default filegroups for the database (see the
ALTER DATABASE command).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uCcjB8B0EHA.3808@.TK2MSFTNGP15.phx.gbl...
> So how about:
> When I create a databbase in EM, I also create secondary database file in
> secondary file group in the dialog box.
> Wiil all user tables be stored in the secondary database file in the
> secondary file group ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> > No. (Re)-create the clustered index on a table to move it.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> > news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> > > When a database is created, all system objects will be stored in this
> > > default filegroup, and also the later user tables, right ?
> > > So if I then create another filegroup as default filegroup, are all
> previous
> > > user tables also move to this default filegroup ?
> > >
> > >
> >
> >
>

Filegroup

When a database is created, all system objects will be stored in this
default filegroup, and also the later user tables, right ?
So if I then create another filegroup as default filegroup, are all previous
user tables also move to this default filegroup ?No. (Re)-create the clustered index on a table to move it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> When a database is created, all system objects will be stored in this
> default filegroup, and also the later user tables, right ?
> So if I then create another filegroup as default filegroup, are all previo
us
> user tables also move to this default filegroup ?
>|||So how about :
When I create a databbase in EM, I also create secondary database file in
secondary file group in the dialog box.
Wiil all user tables be stored in the secondary database file in the
secondary file group ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> No. (Re)-create the clustered index on a table to move it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
previous[vbcol=seagreen]
>|||Current or future tables? To have current tables move to the filegroup, (re)
create the tables
clustered index (as I mentioned earlier). For future tables, either specify
ON <FGNAME> when you
create the table or index, or make the file groups the default filegroups fo
r the database (see the
ALTER DATABASE command).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uCcjB8B0EHA.3808@.TK2MSFTNGP15.phx.gbl...
> So how about :
> When I create a databbase in EM, I also create secondary database file in
> secondary file group in the dialog box.
> Wiil all user tables be stored in the secondary database file in the
> secondary file group ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> previous
>

File Watcher Task doesnt wait for the entire file to be completely finish before it completes

Hey there

Ive built an SSIS package which generates a file from a legacy system and then downloads the file into a designated folder on the server. I need the file watcher task to wait for a the file to completely finish loading before it says it is complete. Currently, as soon as the file is created, the WMI step finishes.

Any help would be greatly appreciated!

Kind Regards

David

Check out http://www.sqlis.com/default.aspx?23

Thanks,
Loonysan

|||

Hi

Thanks for that. We decided to take a different approach though.

Ive downloaded another file watcher task from Konesans. Konesans.Dts.FilewatcherTask.FileWatcherTask. I have added it into my SSIS package and it is working like a dream!

However, when to Add it to the SQL Server Agent, I get a strange error message (Package Execution Progress):

- -> Wait for File to Download

Validation has started

Error: The task has failed to load. The contact information for this task is "".

Error: There were errors during the task validation

Validation is completed

Any ideas of how to resolve this?

Thanking you in advance

David

|||

Konesans file watcher is the same one as http://www.sqlis.com/default.aspx?23

Please check if this component is installed in the machine where you are trying to execute the package via SQL Agent. It looks like the SSIS Package is not able to load the FileWatcher Task at runtime.

If the problem persists - contact http://www.konesans.com/contact.aspx

Thanks,
Loonysan

File Watcher Task doesnt wait for the entire file to be completely finish before it complete

Hey there

Ive built an SSIS package which generates a file from a legacy system and then downloads the file into a designated folder on the server. I need the file watcher task to wait for a the file to completely finish loading before it says it is complete. Currently, as soon as the file is created, the WMI step finishes.

Any help would be greatly appreciated!

Kind Regards

David

Check out http://www.sqlis.com/default.aspx?23

Thanks,
Loonysan

|||

Hi

Thanks for that. We decided to take a different approach though.

Ive downloaded another file watcher task from Konesans. Konesans.Dts.FilewatcherTask.FileWatcherTask. I have added it into my SSIS package and it is working like a dream!

However, when to Add it to the SQL Server Agent, I get a strange error message (Package Execution Progress):

- -> Wait for File to Download

Validation has started

Error: The task has failed to load. The contact information for this task is "".

Error: There were errors during the task validation

Validation is completed

Any ideas of how to resolve this?

Thanking you in advance

David

|||

Konesans file watcher is the same one as http://www.sqlis.com/default.aspx?23

Please check if this component is installed in the machine where you are trying to execute the package via SQL Agent. It looks like the SSIS Package is not able to load the FileWatcher Task at runtime.

If the problem persists - contact http://www.konesans.com/contact.aspx

Thanks,
Loonysan

File Task Moving with variables...

I have a 'file system task' moving files from one server to another for processing. I have defined the path and filename as separate variables. When I attempt to pass them together into the task I receive an error.

Source Path & Filename:

@.[User:: DataSourceFolder]+"\\"+ @.[User::CD_PaidClaimSource]

Outputs:

\\umrdwh2\FTP\CLAIMDAILY.DATA

Destination Path Now: (Filename not specified per another thread.)

@.[User::WorkingFolder]

Original Destination Path: (Which did not work.)

@.[User::WorkingFolder]+"\\"+ @.[User::CD_PaidClaimSource]

I receive validation errors:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

How do I overcome this without hard coding the path in the task?

You should be editing the file connection managers (source and destination) via variable expressions, not the file system task, I believe.|||

CaptainMyCaptain wrote:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

That error is showing you the value of your variable, not it's name. Not sure how that could have happened, but hopefully it helps you figure out what is wrong.
|||

JayH wrote:

CaptainMyCaptain wrote:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

That error is showing you the value of your variable, not it's name. Not sure how that could have happened, but hopefully it helps you figure out what is wrong.

Oh, I think I get it. Did you specify that expression as the Source Path in the File System Task? I didn't think the UI allowed that. If so, you need to create a new variable, set it EvaluateAsExpression=true and put the expression there. Then reference that variable from your File System Task's SourceVariable property.
|||An example to illustrate what we're saying:

http://www.ssistalk.com/file_system_task.jpg|||

The picture helped immensely, thank you. I was trying to establish the source and destination on the expressions screen as well as the general screen which was causing some of the error. Once I removed the destination and source from the expressions screen it worked better.

I am having another issue now, however. Following the syntax in the picture exactly I am getting an error because the filename is being considered a part of the folder path in addition to the filename. I have as an expression:

My expression (like your picture):

"\\\\Apsw9026g1\\Admin\\Logs\\CLAIMDAILY.DATA"

Which outputs:

\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA

But this is the error on execution:

Information: 0xC002F30E at CLAIMDAILY Move, File System Task: File or directory "\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA" was deleted.

Error: 0xC002F304 at CLAIMDAILY Move, File System Task: An error occurred with the following error message: "Could not find a part of the path '\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA'.".

Why is it duplicating the filename as part of the path? Did I miss a setting?

Roger

|||Try deleting the file system task and starting over.|||

Deleting everything variable, task, container, etc and recreating all of it results in the same error. Ideas? The funny part is the system loves the 'Source'; there are no errors, and it is the same syntax. It is only the 'destination' that is having trouble.

Rog

|||

CaptainMyCaptain wrote:

The picture helped immensely, thank you. I was trying to establish the source and destination on the expressions screen as well as the general screen which was causing some of the error. Once I removed the destination and source from the expressions screen it worked better.

I am having another issue now, however. Following the syntax in the picture exactly I am getting an error because the filename is being considered a part of the folder path in addition to the filename. I have as an expression:

My expression (like your picture):

"\\\\Apsw9026g1\\Admin\\Logs\\CLAIMDAILY.DATA"

Which outputs:

\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA

But this is the error on execution:

Information: 0xC002F30E at CLAIMDAILY Move, File System Task: File or directory "\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA" was deleted.

Error: 0xC002F304 at CLAIMDAILY Move, File System Task: An error occurred with the following error message: "Could not find a part of the path '\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA'.".

Why is it duplicating the filename as part of the path? Did I miss a setting?

Roger

Roger,

I have an example that uses the file system task to move/rename files in case you are interested: http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

If I recall correctly, when using variables in the file system task, IsDestiantionPathVariable and IsSourcePathVarible, and depending on the operation used; some times the task expect only the path that points to the file and some times it expects the path AND the file name. In your case it looks like it only expects the path; so remove the file name from the expression. Do not assume both variables need path and file name.

|||

That was the answer. Thank you all for helping this greenhorn to get his job done!

Roger

File System Watcher can not read excel from SharePoint

I used file system watcher to read excel on my pc it worked fine, but when I tried to read the excel from SharePoint it did't work. The FileWatcher box showing the yellow color for long time that I had to stop the ssis.

So my question what is the cause of this. Do i need to set something or am i missing something? Please help.

Plase

|||

What is the path you are using?

I think of sharepoint files as being accessed via http, and of course it will not work for that.

|||

Have you try enabaling package logging and letting it run until it finish?

It would be ggod to see what is reported in log execution.

|||They can be referenced by UNC paths (like \\my.server.org\sites\MySite\Shared Documents\myfile.xls) but I believe that only works if you are on the same network as the server - not over the web.|||Thank you everybody. Sorry I used file Watcher Task, as provided on SQLIS.com, in SSIS and used UNC paths and http to connect SharePoint but it did not connect . Am i missing somethig? Since if I used the watcher task to connect to my local computer it worked.|||

AU.T wrote:

Thank you everybody. Sorry I used file Watcher Task, as provided on SQLIS.com, in SSIS and used UNC paths and http to connect SharePoint but it did not connect . Am i missing somethig? Since if I used the watcher task to connect to my local computer it worked.

Try making a connection using Execute Process to do a "net use" first - that will make sure that you can connect and have security permissions. I've also found that when using UNC paths to SharePoint, I usually have to make an initial connection to establish my credentials before I can access it programmatically.

File System Task: Move/Rename

I can use the File System Task to Move a file from one directory to another and to rename a file, but is there a way to do this in one step, i.e., move file named 'c:\foo.txt' to 'c:\backup\foo_bkp.txt'?You should be able to accomplish this in an expr.|||

OK, maybe I'm being dense ... but how? I have both Source and Destination set to variables. The "Move File" facility only accepts a directory as the Destination because when I coded it with a new file name, I got an error due to the destination file name being ["DestVariable"]\["SourceFileName"] so it looked like:

c:\backup\foo_bkp.txt\foo.txt

|||Raul,
You can use a script task to do this. See here: http://blogs.conchango.com/jamiethomson/archive/2005/09/14/2149.aspx

-Jamie

File System Task Question

Hello,

I am currently just starting to learn SSIS and I have a question on creating a directory using the File System Task.

Would I be able to create a directory where the directory name is generated and stored in a SSIS variable?

Any help is appreciated and thank you in advance for the assistance.

Eric

In tinkering around with SSIS Designer I found a way to create the directory in question.

|||

For info and anyone else wondering, open the task and follow steps below

1 Change Operation to Create directory.

2 Change IsSourcePathVariable to True.

3 Change SourceVariable to the variable you wish to use, or select <New variable> to create one now.

To generate the folder name you can use an expression on the variable. Close the File System Task editor, and setup the expression.

1 Open the SSIS Variables window.

2 Select the variable you wish to work with.

3 Select or Open the Visual Studio Properties window

4 In the Properties window, change the EvaluateAsExpression property of the variable to True.

5 Set the Expression property. This is generates the directory name for you. (SP1 and above will show an ellipsis button against this property which will invoke the expression editor dialog.) For example a folder name of C:\MyFolderyyyymmdd would use the following expression-

"C:\\MyFolder" + (DT_WSTR,4)YEAR(GETDATE())
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2)

6 Check the Value property of the variable to see the evaluated expression result which will be used by the task.

File System Task Problem

I'm trying to realize a file system task that rename files from a
foreach loop container. So that means the task have a variable in the
source connection. This variable got the value (as an expression) of
"c:\\.....\\"+@.[User::ForeachloopVar].
But an error message appears when i run it.The message is

File System Task: An error occurred with the following error message:
"The given path's format is not supported."

When i don′t use the variable in the source connection it works fine.
Anyone knows what might be the problem?
Thanks.

Escape the slashes to be "\\"

BTW - you may find this blog post from Kirk Haselden useful ... http://sqljunkies.com/WebLog/knight_reign/archive/2005/02/12/7750.aspx

Donald

|||I′m sory but that′s not the problem.Because i′ve just wrote the example whitout the slashes but they′re there. And i′ve already looked at that blog. It was helpfull to create my package. But thats the thing in his case the value of the source variable from the foreach loop appears in the value of the expression, mine doesn′t.|||

I see that you are creating a path and appending the source variable in an expression. However, the ForEach loop includes the option to return the fully qualified name of the file. Is there a reason you're not using that?

Donald

|||I′m using the fully qualified name option that's not the problem.|||

It′s working now i did some changes like using only name and extension option on the foreach an voilá.

thanks.

|||

That link was quite a nice example, but I seem to be having a bit of a novice problem. I've followed the blog virtually to the letter, even changing to the variable names used, but on the File System Task I get an error saying that ' FileSourcePath is used as a source or destination and is empty '.

I created the ForEach loop container as was described:

On the collection I have set the path (browsed to the path, so it does exist). I've specified files as '*.txt' - there will be 2 files in the directory by the time the container executes. In the variable mappings I have specified the user::FileSourcePath variable - it has an index value of 0. The variables have a scope of the entire package. I feel I've missed something obvious here, but somehow my container does not update the values for that variable - any help?

|||

Look i was having that same problem before.I did just like the blog shows and it alway fails. I don't know why because it seems to work in the blog. But i don't know if my solution was the best one but i created another variable to contain the flat file source path and and in the foreach loop i've chose the option on the collection pane on the retrieve file name -Name and extension. That means i'm olnly getting the *.txt for the variable @.[user::FileSourcePath]. And then i've put the new variable whit the @.[user::FileSourcePath] in the Connection Manager connectionstring. So then i've just wrote the following on the evaluate as expression of the user::FileDestinationPath

@.[User::FileDestinationFolder] + "\\" +(DT_WSTR, 10)(DT_DBdate)GETDATE()+ @.[User::FileDestinationPath]

and it rename the file just like i wanted and put the date before the old name.

Look i'm new at this thing to so i don't know if i've explained well. But i hope that you can use something.

|||

If you receive an error when the package starts saying that a source or destination placeholder variable is empty you can typically fix it this way: simply ensure that the default (initial) value of the variable is set to a filename.

Setting DelayedValidation on the loop may also help in your case.

Donald

File System Task in SSIS

Wanting to use File System Task in SSIS to move files from

one location to another for archiving. I can't seem to figure out how to use a

wild card for the file name. It seems that I must specify the actual file name

which is a problem because only the first 4 letters in the file name remain a

constant.

Does anyone know how to use a wild card or a way to work

this in?

Darrin Turner wrote:

Wanting to use File System Task in SSIS to move files from one location to another for archiving. I can't seem to figure out how to use a wild card for the file name. It seems that I must specify the actual file name which is a problem because only the first 4 letters in the file name remain a constant.

Does anyone know how to use a wild card or a way to work this in?

Yes. use a ForEach loop to loop over the files (which you can specify with a wildcard) and then use the FileSystem task on each iterated file.

-Jamie

|||Ok, thank you. I will give that a try.