I have only minimal exposure to SQL Server, just need to do something
that is hopefully relatively simple.

A client installed (upgraded) a program and took all the defaults, which
put the SQL database for Time Matters on the C: drive, which -already-
was short on space. It also tries to backup the SQL database to a
backup folder on C:.

I want to move at least the backup location to D: which has tons of space.

Is this a complex process? I have basic experience with database mgmt
programs, and just low-level admin with SQL Server, but lots of computer
experience/knowledge. So a sensible outline would help.

The web searches I've done don't really explain how to do this in simple
terms; I can change the Time Matters file locations but there seems to
be a separate SQL database backup, and the file is pretty huge (about
1GB). To be honest, I don't even know if I can do this with something
like ODBC Admin, or if I have to run a SQL-Server-specific utility.

Also I've seen references here to moving the log file, and I'd like to
do that if not too complex.

There are very ample backups, including to tape with an open-file
option, and from the backup folders to both tape and external disk drive
.... so risk is low.

Thanks.

[Yeah, I know, it would have been easier if he hadn't jumped in & done
the install with the wrong locations and then started using the program
before he mentioned it ........]

Please be gentle.

DE (nowhere (AT) all (DOT) disorg) writes:
The difficult part for me is that this backup was set up by this
third-party program Time Matters, which I've heard of before. Obviously
then, I don't know how it was set up.

But the most likely guess is that it installs an Agent job. Use Object
Explorer in SQL Server Management Studio, and expand the node called
SQL Server Agent, which is the last node. Then expand it to find Jobs,
and see if there are any entries. If you find a job, you will find that
changing the backup location is straightforward.

Then again, if Time Matters install Express Edition of SQL Server, it
is definitely not an Agent job, because Express does not come with
Agent. In this case, they may do it with Windows Task Scheduler.

And who knows, they may have their own scheduler to it, and their
own GUI to control where the backups go.

In the end, it may be better to turn to the vendor for support.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog wrote:
moment, just thinking so based on cost) and never thought of looking at
the Task Scheduler, but actually what happens is that TM seems to
"manage" the backup process. However, in doing so, it backs up in two
parts, the first being the SQL database & the second being files
specific to TM data. The latter is easy to redirect through a TM
utility, but the SQL backup part is problematic because it wants to go
to the C: drive under the default folder structure for SQL server.

I have urged the client to get his TM support renewed, but the vendor
hasn't been terribly responsive so far. (Kind of amazing, usually folks
are fast to take your $$ and slow to respond when you have a question!)

In any case, you've given me the places to look for possible changes,
and it may turn out to be relatively simple.

Thanks. I'd just as well keep the data where it is and just move the
backup if I can.

-- DE

DE (nowhere (AT) all (DOT) disorg) writes:
So what exactly to you have a Task Scheduler? Something that runs SQLCMD?
Could you post the BACKUP command?


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog wrote:
sequentially: first the SQL database, then the other TM files. Every
indication is that TM doesn't provide a way to control where the SQL
backup goes.

This is the best reference I've found so far:
http://support.lexisnexis.com/TimeMa...ckup_r estore
and I could probably work it out from that, assuming I can use the same
SQL Server Management Studio Express program to do a backup (and not
just a restore).

I think I'm close to finding what I need, anyway ... thanks, though I'd
still appreciate any hints.

Well, this all worked ok to move the database itself ... I used the SQL
Server Mgmt Studio Express (downloaded), detached the database, moved
the files, then attached it (no issues at all) ... but it isn't helping
me at all with the backup location. Despite the database being moved to
the partition that has lots of space, the backups still want to go to
the default location on C:. I can kick off a manual backup & locate it
where I want, from within the Mgmt Studio program, but it doesn't "stick".

Is there any chance there's a registry edit to set that location? I
haven't found it in any INI or similar config file.

Also, this is a manual backup; it's initiated using the TMWE program,
and wants to back up the SQL data before backing up the TM files. But I
can find -nothing- on changing that backup location once the
installation is complete.

-- DE




DE wrote:

Hi,

The database files and backup files location is not related in any way, to
moving the database files will have no effect on where the backup is being
stored.

I normally never do a SQL backup without specifying where the backup should
be placed, so I'm not sure if there is a "default" location for a backup. If
such thing exists, I'd expect it to be in the folder under Program
Files\Microsoft SQL Server\MSSQL.X\Backup folder. I'm not sure if that's
something you can change though so it might not help you a lot.

Since you are using a third-party program for this backup, you'll have to
find somebody that knows this program and can tell you how to change the
backup location. It's not a SQL Server issue as such, so it mght be
difficult to get any more help in here.


--
Regards
Steen Schlüter Persson (DK)


"DE" <nowhere (AT) all (DOT) disorg> wrote

DE (nowhere (AT) all (DOT) disorg) writes:
Assuming that Time Matters says

BACKUP DATABASE db TO DISK = 'db.bak'

It seems that they go the directory specified by this registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSQLServer\BackupDirectory

MSSQL.1 is you instance identifier, and it may be different in your
case.

I have never seen this this path exposed. Or discussed. There are
the usual caveats that apply for editing the registry. And if
Time Matters acutally specifies the whole path that is not going
to help you.

If you do it, restart SQL Server when you are done.

An alternative is create a new partition, but instead of giving it a
drive letter, you mount it on the backup directory (which you first
need to move or rename). Had you been on Unix, you could just have
replace BACKUP with a symlink, but I don't think this is possible
on Windows.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx