I have to automate a process that will restore the latest BAK file and all the TRN files that occured afterwards on a remote server. I can't use the log shipping process. The BAK file is created nightly, and TRN files are created every 15 mins. I'm using a maintenance plan, so the file names change
Can anyone think of a good way to recover all those files, without all the clicking and typing
Jeff ZuerleinJeff
Create JOB under Management -SQL Server Agent folders with RESTORE DATABASE
..... command.
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:367639E5-7A83-4D3D-8FA4-2507A221F958@.microsoft.com...
> I have to automate a process that will restore the latest BAK file and all
the TRN files that occured afterwards on a remote server. I can't use the
log shipping process. The BAK file is created nightly, and TRN files are
created every 15 mins. I'm using a maintenance plan, so the file names
change.
> Can anyone think of a good way to recover all those files, without all the
clicking and typing?
> Jeff Zuerlein|||Jeff,
I'm interested in why you can't use log shipping. If it is because you're
not using Enterprise Edition, then there are scripts in the Resource Kit to
do it manually for Standard Edition and below, or online there are a few
people who provide them for free:
http://www.sql-server-performance.com/sql_server_log_shipping.asp
HTH,
Paul Ibison|||It's purely political
I'm using a maintenance plan, so the names of the transaction logs change
I think I could code my way out, but I hate to spend the time if there is a better solution
Jeff|||To load logs from a folder you can get the file names into a temp table in
date order (earliest first) using something like this pseudo code
declare @.files int
create table #files(filename varchar(255))
insert #files exec master..xp_cmdshell 'dir /B /A-D /O-D c:\logs\*.trn'
delete #files where filename is null or filename like '%File Not Found%'
select @.files = count(*) from #files
If @.files >0
begin
-- loop through files in a cursor issuing a restore log command
end
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:FCFA3898-9DAB-46EA-8BD8-0AF5982572EF@.microsoft.com...
> It's purely political.
> I'm using a maintenance plan, so the names of the transaction logs change.
> I think I could code my way out, but I hate to spend the time if there is
a better solution.
> Jeffsql
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Friday, March 30, 2012
How Do I Automate A Recovery Process With Lots of Transaction Logs
I have to automate a process that will restore the latest BAK file and all t
he TRN files that occured afterwards on a remote server. I can't use the lo
g shipping process. The BAK file is created nightly, and TRN files are crea
ted every 15 mins. I'm usi
ng a maintenance plan, so the file names change.
Can anyone think of a good way to recover all those files, without all the c
licking and typing?
Jeff ZuerleinJeff
Create JOB under Management -SQL Server Agent folders with RESTORE DATABASE
..... command.
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:367639E5-7A83-4D3D-8FA4-2507A221F958@.microsoft.com...
> I have to automate a process that will restore the latest BAK file and all
the TRN files that occured afterwards on a remote server. I can't use the
log shipping process. The BAK file is created nightly, and TRN files are
created every 15 mins. I'm using a maintenance plan, so the file names
change.
> Can anyone think of a good way to recover all those files, without all the
clicking and typing?
> Jeff Zuerlein|||Jeff,
I'm interested in why you can't use log shipping. If it is because you're
not using Enterprise Edition, then there are scripts in the Resource Kit to
do it manually for Standard Edition and below, or online there are a few
people who provide them for free:
http://www.sql-server-performance.c...og_shipping.asp
HTH,
Paul Ibison|||It's purely political.
I'm using a maintenance plan, so the names of the transaction logs change.
I think I could code my way out, but I hate to spend the time if there is a
better solution.
Jeff|||To load logs from a folder you can get the file names into a temp table in
date order (earliest first) using something like this pseudo code
declare @.files int
create table #files(filename varchar(255))
insert #files exec master..xp_cmdshell 'dir /B /A-D /O-D c:\logs\*.trn'
delete #files where filename is null or filename like '%File Not Found%'
select @.files = count(*) from #files
If @.files >0
begin
-- loop through files in a cursor issuing a restore log command
end
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:FCFA3898-9DAB-46EA-8BD8-0AF5982572EF@.microsoft.com...
> It's purely political.
> I'm using a maintenance plan, so the names of the transaction logs change.
> I think I could code my way out, but I hate to spend the time if there is
a better solution.
> Jeff
he TRN files that occured afterwards on a remote server. I can't use the lo
g shipping process. The BAK file is created nightly, and TRN files are crea
ted every 15 mins. I'm usi
ng a maintenance plan, so the file names change.
Can anyone think of a good way to recover all those files, without all the c
licking and typing?
Jeff ZuerleinJeff
Create JOB under Management -SQL Server Agent folders with RESTORE DATABASE
..... command.
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:367639E5-7A83-4D3D-8FA4-2507A221F958@.microsoft.com...
> I have to automate a process that will restore the latest BAK file and all
the TRN files that occured afterwards on a remote server. I can't use the
log shipping process. The BAK file is created nightly, and TRN files are
created every 15 mins. I'm using a maintenance plan, so the file names
change.
> Can anyone think of a good way to recover all those files, without all the
clicking and typing?
> Jeff Zuerlein|||Jeff,
I'm interested in why you can't use log shipping. If it is because you're
not using Enterprise Edition, then there are scripts in the Resource Kit to
do it manually for Standard Edition and below, or online there are a few
people who provide them for free:
http://www.sql-server-performance.c...og_shipping.asp
HTH,
Paul Ibison|||It's purely political.
I'm using a maintenance plan, so the names of the transaction logs change.
I think I could code my way out, but I hate to spend the time if there is a
better solution.
Jeff|||To load logs from a folder you can get the file names into a temp table in
date order (earliest first) using something like this pseudo code
declare @.files int
create table #files(filename varchar(255))
insert #files exec master..xp_cmdshell 'dir /B /A-D /O-D c:\logs\*.trn'
delete #files where filename is null or filename like '%File Not Found%'
select @.files = count(*) from #files
If @.files >0
begin
-- loop through files in a cursor issuing a restore log command
end
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:FCFA3898-9DAB-46EA-8BD8-0AF5982572EF@.microsoft.com...
> It's purely political.
> I'm using a maintenance plan, so the names of the transaction logs change.
> I think I could code my way out, but I hate to spend the time if there is
a better solution.
> Jeff
How do I apply a LDF log file to a restored database?
I only have the ldf file and the bak file.
My bak file was current to 3/2/2007
My ldf file was current to 3/25/2007
I do not have the mdf file.
How to I apply the transaction from the ldf file to my resotred database?
Hello David,
According to my knowledge it not is possible to apply a .ldf file but if you are using some external tool like Logexplorer then you can atleast view the transactions in that .ldf file.
|||If this is the case it would seem to negate the purpose of having a log file?|||
The purposes of the log file are:
To facilitate rollback/rollforward when a database is brought online after an unusual shutdown|||
you said you have a .bak file and a log file. My gut feeling is that , this bak file is a full backup of your database. In that case you can restore the database from this back file as on the bak file is created.
YOu have mentioned that you have a ldf file. is it physical file or is it a bakup file. confirm this
run the following command to know what all are the contents inside the bak file
RESTORE FILELISTONLY FROM DISK = 'D:\your bak filename.bak'
if it is a full backup use restore command to restore it. Read about Restore command in BOL
Madhu
|||No David ,
It would not negate the use of a log file. if you had a .mdf file then you could attach the log file with the .mdf file. .trn files are the log back up file not the .ldf files.
How do I apply a LDF log file to a restored database?
I only have the ldf file and the bak file.
My bak file was current to 3/2/2007
My ldf file was current to 3/25/2007
I do not have the mdf file.
How to I apply the transaction from the ldf file to my resotred database?
Hello David,
According to my knowledge it not is possible to apply a .ldf file but if you are using some external tool like Logexplorer then you can atleast view the transactions in that .ldf file.
|||If this is the case it would seem to negate the purpose of having a log file?|||The purposes of the log file are:
To facilitate rollback/rollforward when a database is brought online after an unusual shutdown|||you said you have a .bak file and a log file. My gut feeling is that , this bak file is a full backup of your database. In that case you can restore the database from this back file as on the bak file is created.
YOu have mentioned that you have a ldf file. is it physical file or is it a bakup file. confirm this
run the following command to know what all are the contents inside the bak file
RESTORE FILELISTONLY FROM DISK = 'D:\your bak filename.bak'
if it is a full backup use restore command to restore it. Read about Restore command in BOL
Madhu
|||No David ,
It would not negate the use of a log file. if you had a .mdf file then you could attach the log file with the .mdf file. .trn files are the log back up file not the .ldf files.
Wednesday, March 28, 2012
How do I add 1000+ New Users to ASPNET_DB?
Ok some company has handed me this .xls file containing a 1000+ users -- their emails (which are to be their user names), and their passwords. Both are in plain text format. I want to add these users to the ASPNET_DB, with the condition that the passwords and userids are encrypted, as they are in the table.
How should I do this?
Thanks very much.
The encryption should be in a client side form. You don't want to encrypt a datatype on the DB end and attempt to decrpt it for display.
Create the Table as normal with both the username and password fields as varchar's. Use the import/export wizard to dump it in.
Encrypt the text box control on your form when entering.
<input id="Password1" type="password" />
It'll match against your varchar.
Adamus
|||No, I do want to encrypt the password and user name actually.|||Why is this a TSQL question? Do you want to perform the encryption on the client? Who will need to decrypt the username/password? What accesses the table? There are many questions and depending on that you can pick a method. Below are some ideas:
1. If you need to encrypt / decrypt the username & password combination on the client then it is best to use some algorithm that you can use on the client. So write a program that will encrypt the Excel data and produce a file. You can then load the file into the database
2. You could also use SQLCLR and use one of the Crypto API classes to do the encryption / decryption. You can then use it on the server-side easily.
And it seems like you should post this in the security newsgroup also. Deciding what level of encryption to use and the purpose of encryption depends on your requirements. The security guys will be able to help on the choice of the encryption algorithm or technology.
How do I access a mdf-file directly?
Hello!
I want to access a sql server file without having sql server installed on my laptop. How can I do this? Can you give me an example please because I'm new with it.
Thx a lot!
TomI don't think this can be done. The closes you can get is to do a minimial installation, use the Personal Desktop edition. I don't think there is any tool our COM object that will allow one to open the file and read throuigh it.
If you need to analyse some data or tables you could export the data to MS Access, Excel or flat files.|||You will need to install either msde (I think) or sql server then you can do a sp_attach_single_file_db to access the database.|||I dont think that you would be able to do it.
But as suggested previously, you can try minimal installation and then get the data out in the format that you want.
Thanks|||hii
i think there is a microsoft desktop engine which minimally installs the components required by SQL. Try that that. i dont have info on that at the moment . will try to get u|||hii
Install MSDE which comes in the office 2000 Cd and Use SQL DMO object and attach it to the instance of MSDE. Try , it should work
:confused:
I want to access a sql server file without having sql server installed on my laptop. How can I do this? Can you give me an example please because I'm new with it.
Thx a lot!
TomI don't think this can be done. The closes you can get is to do a minimial installation, use the Personal Desktop edition. I don't think there is any tool our COM object that will allow one to open the file and read throuigh it.
If you need to analyse some data or tables you could export the data to MS Access, Excel or flat files.|||You will need to install either msde (I think) or sql server then you can do a sp_attach_single_file_db to access the database.|||I dont think that you would be able to do it.
But as suggested previously, you can try minimal installation and then get the data out in the format that you want.
Thanks|||hii
i think there is a microsoft desktop engine which minimally installs the components required by SQL. Try that that. i dont have info on that at the moment . will try to get u|||hii
Install MSDE which comes in the office 2000 Cd and Use SQL DMO object and attach it to the instance of MSDE. Try , it should work
:confused:
How do I "apply" a log to a database? Urgent...
Hello!
The database is giving the torn page error.
I have yesterday's backup and today's log file ldf.
How do I apply the log file to database? We didn't backup today's log...
Thanks for your help and attention.
Jorge C.
rdc02271@.yahoo.com
You don't. If you want to recover to a point in time which is between database backups, then you
should have proper operating measures for that requirement. I.e., do log backups. This is what log
backups are for.
What is the recovery mode for your database? If it is simple, then the information needed to recover
is most probably not in the ldf file anymore. If it is full or bulk logged, then you can give any of
the log reader tools a try. I have listed some on my links page.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>
|||Jorge,
- Backup current transaction log WITH NO_TRUNCATE
- Restore full backup using NORECOVERY clause
- Restore transaction log backup WITH RECOVERY
See "Restoring a Database to a Prior State" in BOL.
AMB
"Jorge C" wrote:
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>
|||Oops. Perhaps I didn't read the OP close enough. Assuming that you do indeed perform regular
transaction log backups as well as regular database backups, see Alejandro's post. (I read it as you
didn't perform transaction log backups *at all*, which is a big difference from not performed
*today's* transaction log backup yet.
You might want to check out:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:uDMBL2bRFHA.3880@.tk2msftngp13.phx.gbl...
> You don't. If you want to recover to a point in time which is between database backups, then you
> should have proper operating measures for that requirement. I.e., do log backups. This is what log
> backups are for.
> What is the recovery mode for your database? If it is simple, then the information needed to
> recover is most probably not in the ldf file anymore. If it is full or bulk logged, then you can
> give any of the log reader tools a try. I have listed some on my links page.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
>
The database is giving the torn page error.
I have yesterday's backup and today's log file ldf.
How do I apply the log file to database? We didn't backup today's log...
Thanks for your help and attention.
Jorge C.
rdc02271@.yahoo.com
You don't. If you want to recover to a point in time which is between database backups, then you
should have proper operating measures for that requirement. I.e., do log backups. This is what log
backups are for.
What is the recovery mode for your database? If it is simple, then the information needed to recover
is most probably not in the ldf file anymore. If it is full or bulk logged, then you can give any of
the log reader tools a try. I have listed some on my links page.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>
|||Jorge,
- Backup current transaction log WITH NO_TRUNCATE
- Restore full backup using NORECOVERY clause
- Restore transaction log backup WITH RECOVERY
See "Restoring a Database to a Prior State" in BOL.
AMB
"Jorge C" wrote:
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>
|||Oops. Perhaps I didn't read the OP close enough. Assuming that you do indeed perform regular
transaction log backups as well as regular database backups, see Alejandro's post. (I read it as you
didn't perform transaction log backups *at all*, which is a big difference from not performed
*today's* transaction log backup yet.
You might want to check out:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:uDMBL2bRFHA.3880@.tk2msftngp13.phx.gbl...
> You don't. If you want to recover to a point in time which is between database backups, then you
> should have proper operating measures for that requirement. I.e., do log backups. This is what log
> backups are for.
> What is the recovery mode for your database? If it is simple, then the information needed to
> recover is most probably not in the ldf file anymore. If it is full or bulk logged, then you can
> give any of the log reader tools a try. I have listed some on my links page.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
>
How do I "apply" a log to a database? Urgent...
Hello!
The database is giving the torn page error.
I have yesterday's backup and today's log file ldf.
How do I apply the log file to database? We didn't backup today's log...
Thanks for your help and attention.
Jorge C.
rdc02271@.yahoo.comYou don't. If you want to recover to a point in time which is between databa
se backups, then you
should have proper operating measures for that requirement. I.e., do log bac
kups. This is what log
backups are for.
What is the recovery mode for your database? If it is simple, then the infor
mation needed to recover
is most probably not in the ldf file anymore. If it is full or bulk logged,
then you can give any of
the log reader tools a try. I have listed some on my links page.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>|||Jorge,
- Backup current transaction log WITH NO_TRUNCATE
- Restore full backup using NORECOVERY clause
- Restore transaction log backup WITH RECOVERY
See "Restoring a Database to a Prior State" in BOL.
AMB
"Jorge C" wrote:
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>|||Oops. Perhaps I didn't read the OP close enough. Assuming that you do indeed
perform regular
transaction log backups as well as regular database backups, see Alejandro's
post. (I read it as you
didn't perform transaction log backups *at all*, which is a big difference f
rom not performed
*today's* transaction log backup yet.
You might want to check out:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:uDMBL2bRFHA.3880@.tk2msftngp13.phx.gbl...
> You don't. If you want to recover to a point in time which is between data
base backups, then you
> should have proper operating measures for that requirement. I.e., do log b
ackups. This is what log
> backups are for.
> What is the recovery mode for your database? If it is simple, then the inf
ormation needed to
> recover is most probably not in the ldf file anymore. If it is full or bul
k logged, then you can
> give any of the log reader tools a try. I have listed some on my links pag
e.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MS
FTNGP15.phx.gbl...
>sql
The database is giving the torn page error.
I have yesterday's backup and today's log file ldf.
How do I apply the log file to database? We didn't backup today's log...
Thanks for your help and attention.
Jorge C.
rdc02271@.yahoo.comYou don't. If you want to recover to a point in time which is between databa
se backups, then you
should have proper operating measures for that requirement. I.e., do log bac
kups. This is what log
backups are for.
What is the recovery mode for your database? If it is simple, then the infor
mation needed to recover
is most probably not in the ldf file anymore. If it is full or bulk logged,
then you can give any of
the log reader tools a try. I have listed some on my links page.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>|||Jorge,
- Backup current transaction log WITH NO_TRUNCATE
- Restore full backup using NORECOVERY clause
- Restore transaction log backup WITH RECOVERY
See "Restoring a Database to a Prior State" in BOL.
AMB
"Jorge C" wrote:
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>|||Oops. Perhaps I didn't read the OP close enough. Assuming that you do indeed
perform regular
transaction log backups as well as regular database backups, see Alejandro's
post. (I read it as you
didn't perform transaction log backups *at all*, which is a big difference f
rom not performed
*today's* transaction log backup yet.
You might want to check out:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:uDMBL2bRFHA.3880@.tk2msftngp13.phx.gbl...
> You don't. If you want to recover to a point in time which is between data
base backups, then you
> should have proper operating measures for that requirement. I.e., do log b
ackups. This is what log
> backups are for.
> What is the recovery mode for your database? If it is simple, then the inf
ormation needed to
> recover is most probably not in the ldf file anymore. If it is full or bul
k logged, then you can
> give any of the log reader tools a try. I have listed some on my links pag
e.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MS
FTNGP15.phx.gbl...
>sql
How do I "apply" a log to a database? Urgent...
Hello!
The database is giving the torn page error.
I have yesterday's backup and today's log file ldf.
How do I apply the log file to database? We didn't backup today's log...
Thanks for your help and attention.
Jorge C.
rdc02271@.yahoo.comYou don't. If you want to recover to a point in time which is between database backups, then you
should have proper operating measures for that requirement. I.e., do log backups. This is what log
backups are for.
What is the recovery mode for your database? If it is simple, then the information needed to recover
is most probably not in the ldf file anymore. If it is full or bulk logged, then you can give any of
the log reader tools a try. I have listed some on my links page.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>|||Jorge,
- Backup current transaction log WITH NO_TRUNCATE
- Restore full backup using NORECOVERY clause
- Restore transaction log backup WITH RECOVERY
See "Restoring a Database to a Prior State" in BOL.
AMB
"Jorge C" wrote:
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>|||Oops. Perhaps I didn't read the OP close enough. Assuming that you do indeed perform regular
transaction log backups as well as regular database backups, see Alejandro's post. (I read it as you
didn't perform transaction log backups *at all*, which is a big difference from not performed
*today's* transaction log backup yet.
You might want to check out:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:uDMBL2bRFHA.3880@.tk2msftngp13.phx.gbl...
> You don't. If you want to recover to a point in time which is between database backups, then you
> should have proper operating measures for that requirement. I.e., do log backups. This is what log
> backups are for.
> What is the recovery mode for your database? If it is simple, then the information needed to
> recover is most probably not in the ldf file anymore. If it is full or bulk logged, then you can
> give any of the log reader tools a try. I have listed some on my links page.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
>> Hello!
>> The database is giving the torn page error.
>> I have yesterday's backup and today's log file ldf.
>> How do I apply the log file to database? We didn't backup today's log...
>> Thanks for your help and attention.
>> Jorge C.
>> rdc02271@.yahoo.com
>>
>
The database is giving the torn page error.
I have yesterday's backup and today's log file ldf.
How do I apply the log file to database? We didn't backup today's log...
Thanks for your help and attention.
Jorge C.
rdc02271@.yahoo.comYou don't. If you want to recover to a point in time which is between database backups, then you
should have proper operating measures for that requirement. I.e., do log backups. This is what log
backups are for.
What is the recovery mode for your database? If it is simple, then the information needed to recover
is most probably not in the ldf file anymore. If it is full or bulk logged, then you can give any of
the log reader tools a try. I have listed some on my links page.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>|||Jorge,
- Backup current transaction log WITH NO_TRUNCATE
- Restore full backup using NORECOVERY clause
- Restore transaction log backup WITH RECOVERY
See "Restoring a Database to a Prior State" in BOL.
AMB
"Jorge C" wrote:
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>|||Oops. Perhaps I didn't read the OP close enough. Assuming that you do indeed perform regular
transaction log backups as well as regular database backups, see Alejandro's post. (I read it as you
didn't perform transaction log backups *at all*, which is a big difference from not performed
*today's* transaction log backup yet.
You might want to check out:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:uDMBL2bRFHA.3880@.tk2msftngp13.phx.gbl...
> You don't. If you want to recover to a point in time which is between database backups, then you
> should have proper operating measures for that requirement. I.e., do log backups. This is what log
> backups are for.
> What is the recovery mode for your database? If it is simple, then the information needed to
> recover is most probably not in the ldf file anymore. If it is full or bulk logged, then you can
> give any of the log reader tools a try. I have listed some on my links page.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
>> Hello!
>> The database is giving the torn page error.
>> I have yesterday's backup and today's log file ldf.
>> How do I apply the log file to database? We didn't backup today's log...
>> Thanks for your help and attention.
>> Jorge C.
>> rdc02271@.yahoo.com
>>
>
How do File DSN find the server
How does a File DSN locate the server specified in the file? I am trying to
find out if it used DNS or WINS. As I am hoping it is DNS as I can then add
an alias to redirect the cilents of an application, rather than having to
change the dsn on all the client pc's.
Hi
If you are using TCP/IP it will use the DNS, you can create and alias using
the SQL Server client networking utility, change the hosts file or add an
entry in the DNS.
John
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:55DAD1EE-7E30-4ED8-BC8C-DD28275F9883@.microsoft.com...
> How does a File DSN locate the server specified in the file? I am trying
> to
> find out if it used DNS or WINS. As I am hoping it is DNS as I can then
> add
> an alias to redirect the cilents of an application, rather than having to
> change the dsn on all the client pc's.
find out if it used DNS or WINS. As I am hoping it is DNS as I can then add
an alias to redirect the cilents of an application, rather than having to
change the dsn on all the client pc's.
Hi
If you are using TCP/IP it will use the DNS, you can create and alias using
the SQL Server client networking utility, change the hosts file or add an
entry in the DNS.
John
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:55DAD1EE-7E30-4ED8-BC8C-DD28275F9883@.microsoft.com...
> How does a File DSN locate the server specified in the file? I am trying
> to
> find out if it used DNS or WINS. As I am hoping it is DNS as I can then
> add
> an alias to redirect the cilents of an application, rather than having to
> change the dsn on all the client pc's.
How do File DSN find the server
How does a File DSN locate the server specified in the file? I am trying to
find out if it used DNS or WINS. As I am hoping it is DNS as I can then add
an alias to redirect the cilents of an application, rather than having to
change the dsn on all the client pc's.Hi
If you are using TCP/IP it will use the DNS, you can create and alias using
the SQL Server client networking utility, change the hosts file or add an
entry in the DNS.
John
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:55DAD1EE-7E30-4ED8-BC8C-DD28275F9883@.microsoft.com...
> How does a File DSN locate the server specified in the file? I am trying
> to
> find out if it used DNS or WINS. As I am hoping it is DNS as I can then
> add
> an alias to redirect the cilents of an application, rather than having to
> change the dsn on all the client pc's.
find out if it used DNS or WINS. As I am hoping it is DNS as I can then add
an alias to redirect the cilents of an application, rather than having to
change the dsn on all the client pc's.Hi
If you are using TCP/IP it will use the DNS, you can create and alias using
the SQL Server client networking utility, change the hosts file or add an
entry in the DNS.
John
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:55DAD1EE-7E30-4ED8-BC8C-DD28275F9883@.microsoft.com...
> How does a File DSN locate the server specified in the file? I am trying
> to
> find out if it used DNS or WINS. As I am hoping it is DNS as I can then
> add
> an alias to redirect the cilents of an application, rather than having to
> change the dsn on all the client pc's.
How do auto rename datafiles on a multiple restore?
Hello,
i have a SQL 2005 question.
I create multiple databases from a specific backup file.
I want the database to rename the data & log files automatically if
they already exist (due to the prior creation). I know it is possible,
I do not know how to configure it.
YanivYaniv,shalom
Take a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b092-
0379fc6983da.htm
"ALTER DATABASE" topic in the BOL
<yaniv.harpaz@.gmail.com> wrote in message
news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> Hello,
> i have a SQL 2005 question.
> I create multiple databases from a specific backup file.
> I want the database to rename the data & log files automatically if
> they already exist (due to the prior creation). I know it is possible,
> I do not know how to configure it.
> Yaniv
>|||Hi Uri, thanks for the answer.
Could you gimme a focus (which option are you referring to)?
Shouldn't that be on the instance level?
Yaniv
Uri Dimant wrote:[vbcol=seagreen]
> Yaniv,shalom
> Take a look at
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b09
2-0379fc6983da.htm
> "ALTER DATABASE" topic in the BOL
>
>
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> Hello,
> i have a SQL 2005 question.
> I create multiple databases from a specific backup file.
> I want the database to rename the data & log files automatically if
> they already exist (due to the prior creation). I know it is possible,
> I do not know how to configure it.
>
Check out RESTORE DATABASE ... WITH MOVE in BOL
David|||That's what I am using currently.
I want to be able automatically restore any file I get,
without manually rename each logical file into a new physical one.
David Browne wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
>
> Check out RESTORE DATABASE ... WITH MOVE in BOL
>
> David|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> That's what I am using currently.
> I want to be able automatically restore any file I get,
> without manually rename each logical file into a new physical one.
Not clear then exactly what you're missing then.
Build the string for the file name and use dynamic SQL to execute it.
> David Browne wrote:
>|||I wanted to know if I can manage without it.
Restore and let the server generate the data & log files automatically
on the default locations.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> Not clear then exactly what you're missing then.
> Build the string for the file name and use dynamic SQL to execute it.
>|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153739359.238308.167670@.m73g2000cwd.googlegroups.com...
> I wanted to know if I can manage without it.
> Restore and let the server generate the data & log files automatically
> on the default locations.
No. The restore process restores the database to the location indicated
within the backup.
The only way I'm aware of changing this is the WITH MOVE option.
> Greg D. Moore (Strider) wrote:
if[vbcol=seagreen]
possible,[vbcol=seagreen]
>|||I am trying this on one of my servers and it is working and on the
other it doesn't. how come?
I am using the same backup file.
create database "George_1Month001"
go
restore database "George_1Month001" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month002"
go
restore database "George_1Month002" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month003"
go
restore database "George_1Month003" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153739359.238308.167670@.m73g2000cwd.googlegroups.com...
> No. The restore process restores the database to the location indicated
> within the backup.
> The only way I'm aware of changing this is the WITH MOVE option.
>
> if
> possible,|||yaniv.harpaz@.gmail.com wrote:
> I am trying this on one of my servers and it is working and on the
> other it doesn't. how come?
> I am using the same backup file.
> create database "George_1Month001"
> go
> restore database "George_1Month001" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month002"
> go
> restore database "George_1Month002" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month003"
> go
> restore database "George_1Month003" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
>
>
Could you post the error message(s) you get ?
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator
i have a SQL 2005 question.
I create multiple databases from a specific backup file.
I want the database to rename the data & log files automatically if
they already exist (due to the prior creation). I know it is possible,
I do not know how to configure it.
YanivYaniv,shalom
Take a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b092-
0379fc6983da.htm
"ALTER DATABASE" topic in the BOL
<yaniv.harpaz@.gmail.com> wrote in message
news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> Hello,
> i have a SQL 2005 question.
> I create multiple databases from a specific backup file.
> I want the database to rename the data & log files automatically if
> they already exist (due to the prior creation). I know it is possible,
> I do not know how to configure it.
> Yaniv
>|||Hi Uri, thanks for the answer.
Could you gimme a focus (which option are you referring to)?
Shouldn't that be on the instance level?
Yaniv
Uri Dimant wrote:[vbcol=seagreen]
> Yaniv,shalom
> Take a look at
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b09
2-0379fc6983da.htm
> "ALTER DATABASE" topic in the BOL
>
>
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> Hello,
> i have a SQL 2005 question.
> I create multiple databases from a specific backup file.
> I want the database to rename the data & log files automatically if
> they already exist (due to the prior creation). I know it is possible,
> I do not know how to configure it.
>
Check out RESTORE DATABASE ... WITH MOVE in BOL
David|||That's what I am using currently.
I want to be able automatically restore any file I get,
without manually rename each logical file into a new physical one.
David Browne wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
>
> Check out RESTORE DATABASE ... WITH MOVE in BOL
>
> David|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> That's what I am using currently.
> I want to be able automatically restore any file I get,
> without manually rename each logical file into a new physical one.
Not clear then exactly what you're missing then.
Build the string for the file name and use dynamic SQL to execute it.
> David Browne wrote:
>|||I wanted to know if I can manage without it.
Restore and let the server generate the data & log files automatically
on the default locations.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> Not clear then exactly what you're missing then.
> Build the string for the file name and use dynamic SQL to execute it.
>|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153739359.238308.167670@.m73g2000cwd.googlegroups.com...
> I wanted to know if I can manage without it.
> Restore and let the server generate the data & log files automatically
> on the default locations.
No. The restore process restores the database to the location indicated
within the backup.
The only way I'm aware of changing this is the WITH MOVE option.
> Greg D. Moore (Strider) wrote:
if[vbcol=seagreen]
possible,[vbcol=seagreen]
>|||I am trying this on one of my servers and it is working and on the
other it doesn't. how come?
I am using the same backup file.
create database "George_1Month001"
go
restore database "George_1Month001" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month002"
go
restore database "George_1Month002" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month003"
go
restore database "George_1Month003" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153739359.238308.167670@.m73g2000cwd.googlegroups.com...
> No. The restore process restores the database to the location indicated
> within the backup.
> The only way I'm aware of changing this is the WITH MOVE option.
>
> if
> possible,|||yaniv.harpaz@.gmail.com wrote:
> I am trying this on one of my servers and it is working and on the
> other it doesn't. how come?
> I am using the same backup file.
> create database "George_1Month001"
> go
> restore database "George_1Month001" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month002"
> go
> restore database "George_1Month002" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month003"
> go
> restore database "George_1Month003" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
>
>
Could you post the error message(s) you get ?
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator
How do auto rename datafiles on a multiple restore?
Hello,
i have a SQL 2005 question.
I create multiple databases from a specific backup file.
I want the database to rename the data & log files automatically if
they already exist (due to the prior creation). I know it is possible,
I do not know how to configure it.
YanivYaniv,shalom
Take a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b092-0379fc6983da.htm
"ALTER DATABASE" topic in the BOL
<yaniv.harpaz@.gmail.com> wrote in message
news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> Hello,
> i have a SQL 2005 question.
> I create multiple databases from a specific backup file.
> I want the database to rename the data & log files automatically if
> they already exist (due to the prior creation). I know it is possible,
> I do not know how to configure it.
> Yaniv
>|||Hi Uri, thanks for the answer.
Could you gimme a focus (which option are you referring to)?
Shouldn't that be on the instance level?
Yaniv
Uri Dimant wrote:
> Yaniv,shalom
> Take a look at
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b092-0379fc6983da.htm
> "ALTER DATABASE" topic in the BOL
>
>
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > Hello,
> > i have a SQL 2005 question.
> > I create multiple databases from a specific backup file.
> >
> > I want the database to rename the data & log files automatically if
> > they already exist (due to the prior creation). I know it is possible,
> > I do not know how to configure it.
> >
> > Yaniv
> >|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> Hello,
> i have a SQL 2005 question.
> I create multiple databases from a specific backup file.
> I want the database to rename the data & log files automatically if
> they already exist (due to the prior creation). I know it is possible,
> I do not know how to configure it.
>
Check out RESTORE DATABASE ... WITH MOVE in BOL
David|||That's what I am using currently.
I want to be able automatically restore any file I get,
without manually rename each logical file into a new physical one.
David Browne wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > Hello,
> > i have a SQL 2005 question.
> > I create multiple databases from a specific backup file.
> >
> > I want the database to rename the data & log files automatically if
> > they already exist (due to the prior creation). I know it is possible,
> > I do not know how to configure it.
> >
>
> Check out RESTORE DATABASE ... WITH MOVE in BOL
>
> David|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> That's what I am using currently.
> I want to be able automatically restore any file I get,
> without manually rename each logical file into a new physical one.
Not clear then exactly what you're missing then.
Build the string for the file name and use dynamic SQL to execute it.
> David Browne wrote:
> > <yaniv.harpaz@.gmail.com> wrote in message
> > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > Hello,
> > > i have a SQL 2005 question.
> > > I create multiple databases from a specific backup file.
> > >
> > > I want the database to rename the data & log files automatically if
> > > they already exist (due to the prior creation). I know it is possible,
> > > I do not know how to configure it.
> > >
> >
> >
> > Check out RESTORE DATABASE ... WITH MOVE in BOL
> >
> >
> > David
>|||I wanted to know if I can manage without it.
Restore and let the server generate the data & log files automatically
on the default locations.
Greg D. Moore (Strider) wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> > That's what I am using currently.
> > I want to be able automatically restore any file I get,
> > without manually rename each logical file into a new physical one.
> Not clear then exactly what you're missing then.
> Build the string for the file name and use dynamic SQL to execute it.
>
> >
> > David Browne wrote:
> > > <yaniv.harpaz@.gmail.com> wrote in message
> > > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > > Hello,
> > > > i have a SQL 2005 question.
> > > > I create multiple databases from a specific backup file.
> > > >
> > > > I want the database to rename the data & log files automatically if
> > > > they already exist (due to the prior creation). I know it is possible,
> > > > I do not know how to configure it.
> > > >
> > >
> > >
> > > Check out RESTORE DATABASE ... WITH MOVE in BOL
> > >
> > >
> > > David
> >|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153739359.238308.167670@.m73g2000cwd.googlegroups.com...
> I wanted to know if I can manage without it.
> Restore and let the server generate the data & log files automatically
> on the default locations.
No. The restore process restores the database to the location indicated
within the backup.
The only way I'm aware of changing this is the WITH MOVE option.
> Greg D. Moore (Strider) wrote:
> > <yaniv.harpaz@.gmail.com> wrote in message
> > news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> > > That's what I am using currently.
> > > I want to be able automatically restore any file I get,
> > > without manually rename each logical file into a new physical one.
> >
> > Not clear then exactly what you're missing then.
> >
> > Build the string for the file name and use dynamic SQL to execute it.
> >
> >
> > >
> > > David Browne wrote:
> > > > <yaniv.harpaz@.gmail.com> wrote in message
> > > > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > > > Hello,
> > > > > i have a SQL 2005 question.
> > > > > I create multiple databases from a specific backup file.
> > > > >
> > > > > I want the database to rename the data & log files automatically
if
> > > > > they already exist (due to the prior creation). I know it is
possible,
> > > > > I do not know how to configure it.
> > > > >
> > > >
> > > >
> > > > Check out RESTORE DATABASE ... WITH MOVE in BOL
> > > >
> > > >
> > > > David
> > >
>|||I am trying this on one of my servers and it is working and on the
other it doesn't. how come?
I am using the same backup file.
create database "George_1Month001"
go
restore database "George_1Month001" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month002"
go
restore database "George_1Month002" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month003"
go
restore database "George_1Month003" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
Greg D. Moore (Strider) wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153739359.238308.167670@.m73g2000cwd.googlegroups.com...
> > I wanted to know if I can manage without it.
> > Restore and let the server generate the data & log files automatically
> > on the default locations.
> No. The restore process restores the database to the location indicated
> within the backup.
> The only way I'm aware of changing this is the WITH MOVE option.
>
> >
> > Greg D. Moore (Strider) wrote:
> > > <yaniv.harpaz@.gmail.com> wrote in message
> > > news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> > > > That's what I am using currently.
> > > > I want to be able automatically restore any file I get,
> > > > without manually rename each logical file into a new physical one.
> > >
> > > Not clear then exactly what you're missing then.
> > >
> > > Build the string for the file name and use dynamic SQL to execute it.
> > >
> > >
> > > >
> > > > David Browne wrote:
> > > > > <yaniv.harpaz@.gmail.com> wrote in message
> > > > > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > > > > Hello,
> > > > > > i have a SQL 2005 question.
> > > > > > I create multiple databases from a specific backup file.
> > > > > >
> > > > > > I want the database to rename the data & log files automatically
> if
> > > > > > they already exist (due to the prior creation). I know it is
> possible,
> > > > > > I do not know how to configure it.
> > > > > >
> > > > >
> > > > >
> > > > > Check out RESTORE DATABASE ... WITH MOVE in BOL
> > > > >
> > > > >
> > > > > David
> > > >
> >|||yaniv.harpaz@.gmail.com wrote:
> I am trying this on one of my servers and it is working and on the
> other it doesn't. how come?
> I am using the same backup file.
> create database "George_1Month001"
> go
> restore database "George_1Month001" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month002"
> go
> restore database "George_1Month002" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month003"
> go
> restore database "George_1Month003" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
>
>
Could you post the error message(s) you get ?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||for Example -
Msg 1834, Level 16, State 1, Line 1
The file 'c:\MSSQL\data\CleanDB_1001371.mdf' cannot be overwritten. It
is being used by database 'Cyprus_1Month002'.
Msg 3156, Level 16, State 4, Line 1
File 'CleanDB_1001371' cannot be restored to
'c:\MSSQL\data\CleanDB_1001371.mdf'. Use WITH MOVE to identify a valid
location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'c:\MSSQL\data\CleanDB_1001371_log.LDF' cannot be overwritten.
It is being used by database 'Cyprus_1Month002'.
Msg 3156, Level 16, State 4, Line 1
File 'CleanDB_1001371_log' cannot be restored to
'c:\MSSQL\data\CleanDB_1001371_log.LDF'. Use WITH MOVE to identify a
valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement.
Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Steen Persson (DK) wrote:
> yaniv.harpaz@.gmail.com wrote:
> > I am trying this on one of my servers and it is working and on the
> > other it doesn't. how come?
> > I am using the same backup file.
> >
> > create database "George_1Month001"
> > go
> >
> > restore database "George_1Month001" from
> > disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> > go
> >
> > -- Restore into a new database
> > create database "George_1Month002"
> > go
> >
> > restore database "George_1Month002" from
> > disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> > go
> >
> > -- Restore into a new database
> > create database "George_1Month003"
> > go
> >
> > restore database "George_1Month003" from
> > disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> > go
> >
> >
> >
> Could you post the error message(s) you get ?
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||yaniv.harpaz@.gmail.com wrote:
> for Example -
> Msg 1834, Level 16, State 1, Line 1
> The file 'c:\MSSQL\data\CleanDB_1001371.mdf' cannot be overwritten. It
> is being used by database 'Cyprus_1Month002'.
> Msg 3156, Level 16, State 4, Line 1
> File 'CleanDB_1001371' cannot be restored to
> 'c:\MSSQL\data\CleanDB_1001371.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Msg 1834, Level 16, State 1, Line 1
> The file 'c:\MSSQL\data\CleanDB_1001371_log.LDF' cannot be overwritten.
> It is being used by database 'Cyprus_1Month002'.
> Msg 3156, Level 16, State 4, Line 1
> File 'CleanDB_1001371_log' cannot be restored to
> 'c:\MSSQL\data\CleanDB_1001371_log.LDF'. Use WITH MOVE to identify a
> valid location for the file.
> Msg 3119, Level 16, State 1, Line 1
> Problems were identified while planning for the RESTORE statement.
> Previous messages provide details.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
But then you have the solution in the error message - use the MOVE
option. SQL server tries to restore to the original files but those
files are in use by another database.
Look up RESTORE command in Books On Line - that will give you all the
options and syntax you need.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||have a look at one of the prior posts I wrote.
I want it to take place automatically, without manually telling the SQL
Server which physical path the data files should have.
there is one server in which I can do it, but I do not know what is the
difference in the configuration. this one works:
create database "George_1Month001"
go
restore database "George_1Month001" from
disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month002"
go
restore database "George_1Month002" from
disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
Steen Persson (DK) wrote:
> yaniv.harpaz@.gmail.com wrote:
> > for Example -
> > Msg 1834, Level 16, State 1, Line 1
> > The file 'c:\MSSQL\data\CleanDB_1001371.mdf' cannot be overwritten. It
> > is being used by database 'Cyprus_1Month002'.
> > Msg 3156, Level 16, State 4, Line 1
> > File 'CleanDB_1001371' cannot be restored to
> > 'c:\MSSQL\data\CleanDB_1001371.mdf'. Use WITH MOVE to identify a valid
> > location for the file.
> > Msg 1834, Level 16, State 1, Line 1
> > The file 'c:\MSSQL\data\CleanDB_1001371_log.LDF' cannot be overwritten.
> > It is being used by database 'Cyprus_1Month002'.
> > Msg 3156, Level 16, State 4, Line 1
> > File 'CleanDB_1001371_log' cannot be restored to
> > 'c:\MSSQL\data\CleanDB_1001371_log.LDF'. Use WITH MOVE to identify a
> > valid location for the file.
> > Msg 3119, Level 16, State 1, Line 1
> > Problems were identified while planning for the RESTORE statement.
> > Previous messages provide details.
> > Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> >
> But then you have the solution in the error message - use the MOVE
> option. SQL server tries to restore to the original files but those
> files are in use by another database.
> Look up RESTORE command in Books On Line - that will give you all the
> options and syntax you need.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||yaniv.harpaz@.gmail.com wrote:
> have a look at one of the prior posts I wrote.
> I want it to take place automatically, without manually telling the SQL
> Server which physical path the data files should have.
> there is one server in which I can do it, but I do not know what is the
> difference in the configuration. this one works:
> create database "George_1Month001"
> go
> restore database "George_1Month001" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month002"
> go
> restore database "George_1Month002" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
>
There must be a difference in these 2 servers. The errormessage you
posted, says that the mdf file can't be overwritten because it's used by
another database. Try to check if any other database on that server, is
using that mdf file. What about the disk an directory layout - is that
the same on both servers?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
i have a SQL 2005 question.
I create multiple databases from a specific backup file.
I want the database to rename the data & log files automatically if
they already exist (due to the prior creation). I know it is possible,
I do not know how to configure it.
YanivYaniv,shalom
Take a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b092-0379fc6983da.htm
"ALTER DATABASE" topic in the BOL
<yaniv.harpaz@.gmail.com> wrote in message
news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> Hello,
> i have a SQL 2005 question.
> I create multiple databases from a specific backup file.
> I want the database to rename the data & log files automatically if
> they already exist (due to the prior creation). I know it is possible,
> I do not know how to configure it.
> Yaniv
>|||Hi Uri, thanks for the answer.
Could you gimme a focus (which option are you referring to)?
Shouldn't that be on the instance level?
Yaniv
Uri Dimant wrote:
> Yaniv,shalom
> Take a look at
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b092-0379fc6983da.htm
> "ALTER DATABASE" topic in the BOL
>
>
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > Hello,
> > i have a SQL 2005 question.
> > I create multiple databases from a specific backup file.
> >
> > I want the database to rename the data & log files automatically if
> > they already exist (due to the prior creation). I know it is possible,
> > I do not know how to configure it.
> >
> > Yaniv
> >|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> Hello,
> i have a SQL 2005 question.
> I create multiple databases from a specific backup file.
> I want the database to rename the data & log files automatically if
> they already exist (due to the prior creation). I know it is possible,
> I do not know how to configure it.
>
Check out RESTORE DATABASE ... WITH MOVE in BOL
David|||That's what I am using currently.
I want to be able automatically restore any file I get,
without manually rename each logical file into a new physical one.
David Browne wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > Hello,
> > i have a SQL 2005 question.
> > I create multiple databases from a specific backup file.
> >
> > I want the database to rename the data & log files automatically if
> > they already exist (due to the prior creation). I know it is possible,
> > I do not know how to configure it.
> >
>
> Check out RESTORE DATABASE ... WITH MOVE in BOL
>
> David|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> That's what I am using currently.
> I want to be able automatically restore any file I get,
> without manually rename each logical file into a new physical one.
Not clear then exactly what you're missing then.
Build the string for the file name and use dynamic SQL to execute it.
> David Browne wrote:
> > <yaniv.harpaz@.gmail.com> wrote in message
> > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > Hello,
> > > i have a SQL 2005 question.
> > > I create multiple databases from a specific backup file.
> > >
> > > I want the database to rename the data & log files automatically if
> > > they already exist (due to the prior creation). I know it is possible,
> > > I do not know how to configure it.
> > >
> >
> >
> > Check out RESTORE DATABASE ... WITH MOVE in BOL
> >
> >
> > David
>|||I wanted to know if I can manage without it.
Restore and let the server generate the data & log files automatically
on the default locations.
Greg D. Moore (Strider) wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> > That's what I am using currently.
> > I want to be able automatically restore any file I get,
> > without manually rename each logical file into a new physical one.
> Not clear then exactly what you're missing then.
> Build the string for the file name and use dynamic SQL to execute it.
>
> >
> > David Browne wrote:
> > > <yaniv.harpaz@.gmail.com> wrote in message
> > > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > > Hello,
> > > > i have a SQL 2005 question.
> > > > I create multiple databases from a specific backup file.
> > > >
> > > > I want the database to rename the data & log files automatically if
> > > > they already exist (due to the prior creation). I know it is possible,
> > > > I do not know how to configure it.
> > > >
> > >
> > >
> > > Check out RESTORE DATABASE ... WITH MOVE in BOL
> > >
> > >
> > > David
> >|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153739359.238308.167670@.m73g2000cwd.googlegroups.com...
> I wanted to know if I can manage without it.
> Restore and let the server generate the data & log files automatically
> on the default locations.
No. The restore process restores the database to the location indicated
within the backup.
The only way I'm aware of changing this is the WITH MOVE option.
> Greg D. Moore (Strider) wrote:
> > <yaniv.harpaz@.gmail.com> wrote in message
> > news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> > > That's what I am using currently.
> > > I want to be able automatically restore any file I get,
> > > without manually rename each logical file into a new physical one.
> >
> > Not clear then exactly what you're missing then.
> >
> > Build the string for the file name and use dynamic SQL to execute it.
> >
> >
> > >
> > > David Browne wrote:
> > > > <yaniv.harpaz@.gmail.com> wrote in message
> > > > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > > > Hello,
> > > > > i have a SQL 2005 question.
> > > > > I create multiple databases from a specific backup file.
> > > > >
> > > > > I want the database to rename the data & log files automatically
if
> > > > > they already exist (due to the prior creation). I know it is
possible,
> > > > > I do not know how to configure it.
> > > > >
> > > >
> > > >
> > > > Check out RESTORE DATABASE ... WITH MOVE in BOL
> > > >
> > > >
> > > > David
> > >
>|||I am trying this on one of my servers and it is working and on the
other it doesn't. how come?
I am using the same backup file.
create database "George_1Month001"
go
restore database "George_1Month001" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month002"
go
restore database "George_1Month002" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month003"
go
restore database "George_1Month003" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
Greg D. Moore (Strider) wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153739359.238308.167670@.m73g2000cwd.googlegroups.com...
> > I wanted to know if I can manage without it.
> > Restore and let the server generate the data & log files automatically
> > on the default locations.
> No. The restore process restores the database to the location indicated
> within the backup.
> The only way I'm aware of changing this is the WITH MOVE option.
>
> >
> > Greg D. Moore (Strider) wrote:
> > > <yaniv.harpaz@.gmail.com> wrote in message
> > > news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> > > > That's what I am using currently.
> > > > I want to be able automatically restore any file I get,
> > > > without manually rename each logical file into a new physical one.
> > >
> > > Not clear then exactly what you're missing then.
> > >
> > > Build the string for the file name and use dynamic SQL to execute it.
> > >
> > >
> > > >
> > > > David Browne wrote:
> > > > > <yaniv.harpaz@.gmail.com> wrote in message
> > > > > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > > > > Hello,
> > > > > > i have a SQL 2005 question.
> > > > > > I create multiple databases from a specific backup file.
> > > > > >
> > > > > > I want the database to rename the data & log files automatically
> if
> > > > > > they already exist (due to the prior creation). I know it is
> possible,
> > > > > > I do not know how to configure it.
> > > > > >
> > > > >
> > > > >
> > > > > Check out RESTORE DATABASE ... WITH MOVE in BOL
> > > > >
> > > > >
> > > > > David
> > > >
> >|||yaniv.harpaz@.gmail.com wrote:
> I am trying this on one of my servers and it is working and on the
> other it doesn't. how come?
> I am using the same backup file.
> create database "George_1Month001"
> go
> restore database "George_1Month001" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month002"
> go
> restore database "George_1Month002" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month003"
> go
> restore database "George_1Month003" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
>
>
Could you post the error message(s) you get ?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||for Example -
Msg 1834, Level 16, State 1, Line 1
The file 'c:\MSSQL\data\CleanDB_1001371.mdf' cannot be overwritten. It
is being used by database 'Cyprus_1Month002'.
Msg 3156, Level 16, State 4, Line 1
File 'CleanDB_1001371' cannot be restored to
'c:\MSSQL\data\CleanDB_1001371.mdf'. Use WITH MOVE to identify a valid
location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'c:\MSSQL\data\CleanDB_1001371_log.LDF' cannot be overwritten.
It is being used by database 'Cyprus_1Month002'.
Msg 3156, Level 16, State 4, Line 1
File 'CleanDB_1001371_log' cannot be restored to
'c:\MSSQL\data\CleanDB_1001371_log.LDF'. Use WITH MOVE to identify a
valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement.
Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Steen Persson (DK) wrote:
> yaniv.harpaz@.gmail.com wrote:
> > I am trying this on one of my servers and it is working and on the
> > other it doesn't. how come?
> > I am using the same backup file.
> >
> > create database "George_1Month001"
> > go
> >
> > restore database "George_1Month001" from
> > disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> > go
> >
> > -- Restore into a new database
> > create database "George_1Month002"
> > go
> >
> > restore database "George_1Month002" from
> > disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> > go
> >
> > -- Restore into a new database
> > create database "George_1Month003"
> > go
> >
> > restore database "George_1Month003" from
> > disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> > go
> >
> >
> >
> Could you post the error message(s) you get ?
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||yaniv.harpaz@.gmail.com wrote:
> for Example -
> Msg 1834, Level 16, State 1, Line 1
> The file 'c:\MSSQL\data\CleanDB_1001371.mdf' cannot be overwritten. It
> is being used by database 'Cyprus_1Month002'.
> Msg 3156, Level 16, State 4, Line 1
> File 'CleanDB_1001371' cannot be restored to
> 'c:\MSSQL\data\CleanDB_1001371.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Msg 1834, Level 16, State 1, Line 1
> The file 'c:\MSSQL\data\CleanDB_1001371_log.LDF' cannot be overwritten.
> It is being used by database 'Cyprus_1Month002'.
> Msg 3156, Level 16, State 4, Line 1
> File 'CleanDB_1001371_log' cannot be restored to
> 'c:\MSSQL\data\CleanDB_1001371_log.LDF'. Use WITH MOVE to identify a
> valid location for the file.
> Msg 3119, Level 16, State 1, Line 1
> Problems were identified while planning for the RESTORE statement.
> Previous messages provide details.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
But then you have the solution in the error message - use the MOVE
option. SQL server tries to restore to the original files but those
files are in use by another database.
Look up RESTORE command in Books On Line - that will give you all the
options and syntax you need.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||have a look at one of the prior posts I wrote.
I want it to take place automatically, without manually telling the SQL
Server which physical path the data files should have.
there is one server in which I can do it, but I do not know what is the
difference in the configuration. this one works:
create database "George_1Month001"
go
restore database "George_1Month001" from
disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month002"
go
restore database "George_1Month002" from
disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
Steen Persson (DK) wrote:
> yaniv.harpaz@.gmail.com wrote:
> > for Example -
> > Msg 1834, Level 16, State 1, Line 1
> > The file 'c:\MSSQL\data\CleanDB_1001371.mdf' cannot be overwritten. It
> > is being used by database 'Cyprus_1Month002'.
> > Msg 3156, Level 16, State 4, Line 1
> > File 'CleanDB_1001371' cannot be restored to
> > 'c:\MSSQL\data\CleanDB_1001371.mdf'. Use WITH MOVE to identify a valid
> > location for the file.
> > Msg 1834, Level 16, State 1, Line 1
> > The file 'c:\MSSQL\data\CleanDB_1001371_log.LDF' cannot be overwritten.
> > It is being used by database 'Cyprus_1Month002'.
> > Msg 3156, Level 16, State 4, Line 1
> > File 'CleanDB_1001371_log' cannot be restored to
> > 'c:\MSSQL\data\CleanDB_1001371_log.LDF'. Use WITH MOVE to identify a
> > valid location for the file.
> > Msg 3119, Level 16, State 1, Line 1
> > Problems were identified while planning for the RESTORE statement.
> > Previous messages provide details.
> > Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> >
> But then you have the solution in the error message - use the MOVE
> option. SQL server tries to restore to the original files but those
> files are in use by another database.
> Look up RESTORE command in Books On Line - that will give you all the
> options and syntax you need.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||yaniv.harpaz@.gmail.com wrote:
> have a look at one of the prior posts I wrote.
> I want it to take place automatically, without manually telling the SQL
> Server which physical path the data files should have.
> there is one server in which I can do it, but I do not know what is the
> difference in the configuration. this one works:
> create database "George_1Month001"
> go
> restore database "George_1Month001" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month002"
> go
> restore database "George_1Month002" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
>
There must be a difference in these 2 servers. The errormessage you
posted, says that the mdf file can't be overwritten because it's used by
another database. Try to check if any other database on that server, is
using that mdf file. What about the disk an directory layout - is that
the same on both servers?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
How do attach database while installing msde on client pc ?
I have added MSDE merge modules in VS Installer as well as MDF and LDF file,
now what should I do, so that setup can automatically Attach the database
after installing MSDE client's Machine.
Best Regards,
Luqman
Hi Luqman,
see this thread here:
http://groups.google.de/group/micros...8f627e88171d02
HTH, Jens Suessmeyer.
now what should I do, so that setup can automatically Attach the database
after installing MSDE client's Machine.
Best Regards,
Luqman
Hi Luqman,
see this thread here:
http://groups.google.de/group/micros...8f627e88171d02
HTH, Jens Suessmeyer.
how data file and log file grow?
Greetings,
The following shows how the Properties of a database look like:
Data Files:
File Name: student_dat
Location: e:\data\MSSQL\Data\student.mdf
Space allocated (MB): 62
'Automatically grow file' checked
File growth: 'By percent 10%' checked
Maximum file size: 'Unrestricted file growth' checked
Transaction Log:
File Name: student_log
Location: e:\data\MSSQL\Data\student.ldf
Space allocated (MB): 52
'Automatically grow file' checked
File growth: 'By percent 10%' checked
Maximum file size: 'Unrestricted file growth' checked
The physical files look like this:
Name Size Type Modified
---------------
student.ldf 52,416 KB Database File 2/11/2004 10:34PM
student.mdf 63,424 KB Database File 2/11/2004 10:34PM
My question is now that it has been specified 'Unrestricted file growth'
for both the data and the log file, why both haven't increased any in
size since 2/11/2004? Actually, the modified timestamps of some other
databases files are also '2/11/2004 10:34PM'. That's weird.
I found the following message that's relevent to the above timestamp in
the SQL Server Logs:
=====
2004-02-11 22:34:10.12 server SQL Server terminating because of
system shutdown.
=====
I'm pretty sure there have been a lot updates taking place on this
database. We don't hear any complaints from the customers that they
have had any problems (such as, no space left) with the databases.
Did the SQL server write the data and log somewhere else?
Any insight on what's going on would be appreciated.
BingBing Du (bdu@.iastate.edu) writes:
> The physical files look like this:
> Name Size Type Modified
> ---------------
> student.ldf 52,416 KB Database File 2/11/2004 10:34PM
> student.mdf 63,424 KB Database File 2/11/2004 10:34PM
> My question is now that it has been specified 'Unrestricted file growth'
> for both the data and the log file, why both haven't increased any in
> size since 2/11/2004? Actually, the modified timestamps of some other
> databases files are also '2/11/2004 10:34PM'. That's weird.
If the files are said to be modified in November this year, then there
is something fishy.
> I found the following message that's relevent to the above timestamp in
> the SQL Server Logs:
>=====
> 2004-02-11 22:34:10.12 server SQL Server terminating because of
> system shutdown.
>=====
But if the date is supposed to be in February, then it seems that files
has not been touched since. Either the files were detached, or the
databases has not been set to autoclose. Or the entire SQL Server instance
has not been running since then.
On my machine, about all my databases has a last modified date of
2004-03-01. I started SQL Server today, but I have not visited all
databases. But on startup, SQL Server performs recovery of all databases,
and thus writes to them.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the instant response!
No, not November. The files were last modified on 2/11/2004. That's
Feb. 11, 2004. If the files were detached, the users would not be able
to access the databases, right? But we have not heard any complaints.
I don't think those databases haven't been touched since 2/11/2004.
These databases are in production. A lot updates are going on every
day. From my understanding, any updates (insert, update, delete) should
trigger the modified timestamp change on the files. Also, the SQL
server instance has been running fine since then. The only thing I'm
not sure is 'databases has not been set to autoclose' you mentioned.
Where can I find that setting?
Bing
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I've checked the sysdatabases table. The 'status' is 16 for all our
user databases. Per SQL Server Online Book, '1 = autoclose, set with
sp_dboption' and
'16 = torn page detection, set with sp_dboption'. Do I need to change
'status'?
Another thing was the SQL Server Agent was not set to start
automatically whenever the SQL Server instance is started. Our server
instance terminated and started on 2/11/2004. We started the SQL Server
Agent manually on 2/15/2004. Would this have done anything to make the
database files not been changed since 2/11/2004?
Bing
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I've checked the sysdatabases table. The 'status' is 16 for all our
user databases. Per SQL Server Online Book, '1 = autoclose, set with
sp_dboption' and
'16 = torn page detection, set with sp_dboption'. Do I need to change
'status'?
Another thing was the SQL Server Agent was not set to start
automatically whenever the SQL Server instance is started. Our server
instance terminated and started on 2/11/2004. We started the SQL Server
Agent manually on 2/15/2004. Would this have done anything to make the
database files not been changed since 2/11/2004?
Bing
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||comp.databases.ms-sqlserver (anonymous@.devdex.com) writes:
> No, not November. The files were last modified on 2/11/2004. That's
> Feb. 11, 2004.
Not in any date format I would use. :-)
> If the files were detached, the users would not be able to access the
> databases, right? But we have not heard any complaints. I don't think
> those databases haven't been touched since 2/11/2004. These databases
> are in production. A lot updates are going on every day. From my
> understanding, any updates (insert, update, delete) should trigger the
> modified timestamp change on the files. Also, the SQL server instance
> has been running fine since then. The only thing I'm not sure is
> 'databases has not been set to autoclose' you mentioned. Where can I
> find that setting?
Looks like I spoke too soon when I said that my files had a date that
agreed with my latest startup of SQL Server. I forgot that I rarely to
to bed before midnight, so the times I saw was from the last shutdown
as in your case. So I guess that everything's normal.
As for autogrow not setting in, one has to assume that the number of inserts
vs. the number deletes balances each other fairly well.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||So are you saying the time actually shown for those MDF and LDF files
was the time the Server instance last terminated? Any updates (lke
INSERT, DELETE or UPDATE) won't get the timestamps changed?
Bing
Erland Sommarskog wrote:
> comp.databases.ms-sqlserver (anonymous@.devdex.com) writes:
>>No, not November. The files were last modified on 2/11/2004. That's
>>Feb. 11, 2004.
>
> Not in any date format I would use. :-)
>
>>If the files were detached, the users would not be able to access the
>>databases, right? But we have not heard any complaints. I don't think
>>those databases haven't been touched since 2/11/2004. These databases
>>are in production. A lot updates are going on every day. From my
>>understanding, any updates (insert, update, delete) should trigger the
>>modified timestamp change on the files. Also, the SQL server instance
>>has been running fine since then. The only thing I'm not sure is
>>'databases has not been set to autoclose' you mentioned. Where can I
>>find that setting?
>
> Looks like I spoke too soon when I said that my files had a date that
> agreed with my latest startup of SQL Server. I forgot that I rarely to
> to bed before midnight, so the times I saw was from the last shutdown
> as in your case. So I guess that everything's normal.
> As for autogrow not setting in, one has to assume that the number of inserts
> vs. the number deletes balances each other fairly well.|||Bing Du (bdu@.iastate.edu) writes:
> So are you saying the time actually shown for those MDF and LDF files
> was the time the Server instance last terminated? Any updates (lke
> INSERT, DELETE or UPDATE) won't get the timestamps changed?
It does not seem like that.
I would guess that if you set the database to autoclose, the date would
be updated each time the database was closed.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Monday, March 26, 2012
How delete or rename file in storedprocedure
I want to rename or delete a file in my storedprocedure.
how?
TarvirdiYou can use xp_cmdshell, assuming you understand the permission implications
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tarvirdi" <m_tarvirdi@.isc.iranet.net> wrote in message
news:ObmPN62YGHA.2208@.TK2MSFTNGP03.phx.gbl...
>I want to rename or delete a file in my storedprocedure.
> how?
> Tarvirdi
>|||U should have permission to execute xp_cmdshell.
U should have configured sql server to enable services for xp_cmdshell.
u can configure xp_cmdshell from sp_configure option.
HTH
from
Doller
how?
TarvirdiYou can use xp_cmdshell, assuming you understand the permission implications
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tarvirdi" <m_tarvirdi@.isc.iranet.net> wrote in message
news:ObmPN62YGHA.2208@.TK2MSFTNGP03.phx.gbl...
>I want to rename or delete a file in my storedprocedure.
> how?
> Tarvirdi
>|||U should have permission to execute xp_cmdshell.
U should have configured sql server to enable services for xp_cmdshell.
u can configure xp_cmdshell from sp_configure option.
HTH
from
Doller
How delete or rename file in storedprocedure
I want to rename or delete a file in my storedprocedure.
how?
TarvirdiYou can use xp_cmdshell, assuming you understand the permission implications.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tarvirdi" <m_tarvirdi@.isc.iranet.net> wrote in message
news:ObmPN62YGHA.2208@.TK2MSFTNGP03.phx.gbl...
>I want to rename or delete a file in my storedprocedure.
> how?
> Tarvirdi
>|||U should have permission to execute xp_cmdshell.
U should have configured sql server to enable services for xp_cmdshell.
u can configure xp_cmdshell from sp_configure option.
HTH
from
Dollersql
how?
TarvirdiYou can use xp_cmdshell, assuming you understand the permission implications.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tarvirdi" <m_tarvirdi@.isc.iranet.net> wrote in message
news:ObmPN62YGHA.2208@.TK2MSFTNGP03.phx.gbl...
>I want to rename or delete a file in my storedprocedure.
> how?
> Tarvirdi
>|||U should have permission to execute xp_cmdshell.
U should have configured sql server to enable services for xp_cmdshell.
u can configure xp_cmdshell from sp_configure option.
HTH
from
Dollersql
How delete a log file
I have unfortunately create a second log file for a database.
So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
I would delete DataLog2.ldf, but it's now the active log file, and the
operation can't terminate.
Thanks
Try DBCC SHRINKFILE with the EMPTYFILE option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:56283E5B-FB80-46DA-8DEE-D446F940F43C@.microsoft.com...
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks
|||Hi Jean-Paul,
Can you try detaching the database, delete the log file and then attach
the database? There are packages available within sqlserver "sp_detach_db"
and
"sp_attach_single_file_db".? Take a backup to be safe before making this
structural change.
Thanks,
Chandan Dutta
"Jean-Paul" wrote:
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks
|||Hi Chandan,
sp_attach_single_file_db command might fail if you have multiple LDF files.
As well it is risky to do this command on a production server.
Jean_paul,
You could follow the Dan Guzmans option, I can detail you the steps to
remove 1 LDF file :-
Insted of detaching the database you can empty the second LDF and Remove the
file.
How to check the number of LDF files used by a database:-
sp_helpdb <dbname>
If you have 2 LDF files ,to remove a file , you must first have the data
moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHRINKFILE command. This
will empty the file and mark it as unavailable.
See the commands to remove a ldf :-
backup log <dbname> to disk='c:\backup\dbname.trn'
go
use <dbname>
go
dbcc shrinkfile('logical_log_filename_to_delete','empty file')
go
alter database <dbname> remove file 'logical_log_file_name_to_delete'
Thanks
Hari
MCDBA
"Chandan Dutta" <ChandanDutta@.discussions.microsoft.com> wrote in message
news:25EC1FE9-F2AB-4AD4-BE70-48D06165A384@.microsoft.com...
> Hi Jean-Paul,
> Can you try detaching the database, delete the log file and then
attach[vbcol=seagreen]
> the database? There are packages available within sqlserver "sp_detach_db"
> and
> "sp_attach_single_file_db".? Take a backup to be safe before making this
> structural change.
> Thanks,
> Chandan Dutta
>
> "Jean-Paul" wrote:
sql
So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
I would delete DataLog2.ldf, but it's now the active log file, and the
operation can't terminate.
Thanks
Try DBCC SHRINKFILE with the EMPTYFILE option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:56283E5B-FB80-46DA-8DEE-D446F940F43C@.microsoft.com...
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks
|||Hi Jean-Paul,
Can you try detaching the database, delete the log file and then attach
the database? There are packages available within sqlserver "sp_detach_db"
and
"sp_attach_single_file_db".? Take a backup to be safe before making this
structural change.
Thanks,
Chandan Dutta
"Jean-Paul" wrote:
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks
|||Hi Chandan,
sp_attach_single_file_db command might fail if you have multiple LDF files.
As well it is risky to do this command on a production server.
Jean_paul,
You could follow the Dan Guzmans option, I can detail you the steps to
remove 1 LDF file :-
Insted of detaching the database you can empty the second LDF and Remove the
file.
How to check the number of LDF files used by a database:-
sp_helpdb <dbname>
If you have 2 LDF files ,to remove a file , you must first have the data
moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHRINKFILE command. This
will empty the file and mark it as unavailable.
See the commands to remove a ldf :-
backup log <dbname> to disk='c:\backup\dbname.trn'
go
use <dbname>
go
dbcc shrinkfile('logical_log_filename_to_delete','empty file')
go
alter database <dbname> remove file 'logical_log_file_name_to_delete'
Thanks
Hari
MCDBA
"Chandan Dutta" <ChandanDutta@.discussions.microsoft.com> wrote in message
news:25EC1FE9-F2AB-4AD4-BE70-48D06165A384@.microsoft.com...
> Hi Jean-Paul,
> Can you try detaching the database, delete the log file and then
attach[vbcol=seagreen]
> the database? There are packages available within sqlserver "sp_detach_db"
> and
> "sp_attach_single_file_db".? Take a backup to be safe before making this
> structural change.
> Thanks,
> Chandan Dutta
>
> "Jean-Paul" wrote:
sql
How delete a log file
I have unfortunately create a second log file for a database.
So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
I would delete DataLog2.ldf, but it's now the active log file, and the
operation can't terminate.
ThanksTry DBCC SHRINKFILE with the EMPTYFILE option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:56283E5B-FB80-46DA-8DEE-D446F940F43C@.microsoft.com...
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks|||Hi Jean-Paul,
Can you try detaching the database, delete the log file and then attach
the database? There are packages available within sqlserver "sp_detach_db"
and
"sp_attach_single_file_db".? Take a backup to be safe before making this
structural change.
Thanks,
Chandan Dutta
"Jean-Paul" wrote:
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks|||Hi Chandan,
sp_attach_single_file_db command might fail if you have multiple LDF files.
As well it is risky to do this command on a production server.
Jean_paul,
You could follow the Dan Guzmans option, I can detail you the steps to
remove 1 LDF file :-
Insted of detaching the database you can empty the second LDF and Remove the
file.
How to check the number of LDF files used by a database:-
sp_helpdb <dbname>
If you have 2 LDF files ,to remove a file , you must first have the data
moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHRINKFILE command. This
will empty the file and mark it as unavailable.
See the commands to remove a ldf :-
backup log <dbname> to disk='c:\backup\dbname.trn'
go
use <dbname>
go
dbcc shrinkfile('logical_log_filename_to_dele
te','emptyfile')
go
alter database <dbname> remove file 'logical_log_file_name_to_delete'
Thanks
Hari
MCDBA
"Chandan Dutta" <ChandanDutta@.discussions.microsoft.com> wrote in message
news:25EC1FE9-F2AB-4AD4-BE70-48D06165A384@.microsoft.com...
> Hi Jean-Paul,
> Can you try detaching the database, delete the log file and then
attach[vbcol=seagreen]
> the database? There are packages available within sqlserver "sp_detach_db"
> and
> "sp_attach_single_file_db".? Take a backup to be safe before making this
> structural change.
> Thanks,
> Chandan Dutta
>
> "Jean-Paul" wrote:
>
So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
I would delete DataLog2.ldf, but it's now the active log file, and the
operation can't terminate.
ThanksTry DBCC SHRINKFILE with the EMPTYFILE option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:56283E5B-FB80-46DA-8DEE-D446F940F43C@.microsoft.com...
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks|||Hi Jean-Paul,
Can you try detaching the database, delete the log file and then attach
the database? There are packages available within sqlserver "sp_detach_db"
and
"sp_attach_single_file_db".? Take a backup to be safe before making this
structural change.
Thanks,
Chandan Dutta
"Jean-Paul" wrote:
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks|||Hi Chandan,
sp_attach_single_file_db command might fail if you have multiple LDF files.
As well it is risky to do this command on a production server.
Jean_paul,
You could follow the Dan Guzmans option, I can detail you the steps to
remove 1 LDF file :-
Insted of detaching the database you can empty the second LDF and Remove the
file.
How to check the number of LDF files used by a database:-
sp_helpdb <dbname>
If you have 2 LDF files ,to remove a file , you must first have the data
moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHRINKFILE command. This
will empty the file and mark it as unavailable.
See the commands to remove a ldf :-
backup log <dbname> to disk='c:\backup\dbname.trn'
go
use <dbname>
go
dbcc shrinkfile('logical_log_filename_to_dele
te','emptyfile')
go
alter database <dbname> remove file 'logical_log_file_name_to_delete'
Thanks
Hari
MCDBA
"Chandan Dutta" <ChandanDutta@.discussions.microsoft.com> wrote in message
news:25EC1FE9-F2AB-4AD4-BE70-48D06165A384@.microsoft.com...
> Hi Jean-Paul,
> Can you try detaching the database, delete the log file and then
attach[vbcol=seagreen]
> the database? There are packages available within sqlserver "sp_detach_db"
> and
> "sp_attach_single_file_db".? Take a backup to be safe before making this
> structural change.
> Thanks,
> Chandan Dutta
>
> "Jean-Paul" wrote:
>
How delete a log file
I have unfortunately create a second log file for a database.
So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
I would delete DataLog2.ldf, but it's now the active log file, and the
operation can't terminate.
ThanksTry DBCC SHRINKFILE with the EMPTYFILE option.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:56283E5B-FB80-46DA-8DEE-D446F940F43C@.microsoft.com...
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks|||Hi Chandan,
sp_attach_single_file_db command might fail if you have multiple LDF files.
As well it is risky to do this command on a production server.
Jean_paul,
You could follow the Dan Guzmans option, I can detail you the steps to
remove 1 LDF file :-
Insted of detaching the database you can empty the second LDF and Remove the
file.
How to check the number of LDF files used by a database:-
sp_helpdb <dbname>
If you have 2 LDF files ,to remove a file , you must first have the data
moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHRINKFILE command. This
will empty the file and mark it as unavailable.
See the commands to remove a ldf :-
backup log <dbname> to disk='c:\backup\dbname.trn'
go
use <dbname>
go
dbcc shrinkfile('logical_log_filename_to_delete','emptyfile')
go
alter database <dbname> remove file 'logical_log_file_name_to_delete'
--
Thanks
Hari
MCDBA
"Chandan Dutta" <ChandanDutta@.discussions.microsoft.com> wrote in message
news:25EC1FE9-F2AB-4AD4-BE70-48D06165A384@.microsoft.com...
> Hi Jean-Paul,
> Can you try detaching the database, delete the log file and then
attach
> the database? There are packages available within sqlserver "sp_detach_db"
> and
> "sp_attach_single_file_db".? Take a backup to be safe before making this
> structural change.
> Thanks,
> Chandan Dutta
>
> "Jean-Paul" wrote:
> > I have unfortunately create a second log file for a database.
> > So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> > I would delete DataLog2.ldf, but it's now the active log file, and the
> > operation can't terminate.
> >
> > Thanks
So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
I would delete DataLog2.ldf, but it's now the active log file, and the
operation can't terminate.
ThanksTry DBCC SHRINKFILE with the EMPTYFILE option.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:56283E5B-FB80-46DA-8DEE-D446F940F43C@.microsoft.com...
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks|||Hi Chandan,
sp_attach_single_file_db command might fail if you have multiple LDF files.
As well it is risky to do this command on a production server.
Jean_paul,
You could follow the Dan Guzmans option, I can detail you the steps to
remove 1 LDF file :-
Insted of detaching the database you can empty the second LDF and Remove the
file.
How to check the number of LDF files used by a database:-
sp_helpdb <dbname>
If you have 2 LDF files ,to remove a file , you must first have the data
moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHRINKFILE command. This
will empty the file and mark it as unavailable.
See the commands to remove a ldf :-
backup log <dbname> to disk='c:\backup\dbname.trn'
go
use <dbname>
go
dbcc shrinkfile('logical_log_filename_to_delete','emptyfile')
go
alter database <dbname> remove file 'logical_log_file_name_to_delete'
--
Thanks
Hari
MCDBA
"Chandan Dutta" <ChandanDutta@.discussions.microsoft.com> wrote in message
news:25EC1FE9-F2AB-4AD4-BE70-48D06165A384@.microsoft.com...
> Hi Jean-Paul,
> Can you try detaching the database, delete the log file and then
attach
> the database? There are packages available within sqlserver "sp_detach_db"
> and
> "sp_attach_single_file_db".? Take a backup to be safe before making this
> structural change.
> Thanks,
> Chandan Dutta
>
> "Jean-Paul" wrote:
> > I have unfortunately create a second log file for a database.
> > So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> > I would delete DataLog2.ldf, but it's now the active log file, and the
> > operation can't terminate.
> >
> > Thanks
Subscribe to:
Posts (Atom)