Script to rename database (and files)

I need to rename several databases and also rename the data and log files
(since these filenames contain the DB name). I was thinking of writing a
script to do so but before I did, I thought I would ask if anyone had such a
script. If someone happens to have something like this - I would greatly
appreciate it.
Thanks in advance.Hi,
To rename the logical name of the file:- use the below script
alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
='NEW_MDF_NAME')
Do the same for LDF file as well.
For renaming the database.
alter database OLD_DB_NAME modify name = new_db_name
To rename the physical files. Usae the below script sample.
sp_detach_db <dbname>
go
--rename the LDF file and MDF physical files using windows explorer
go
sp_attach_db <Dbname> ,'phsical_mdf_with_path','new_ldf_with_p
ath'
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> I need to rename several databases and also rename the data and log files
> (since these filenames contain the DB name). I was thinking of writing a
> script to do so but before I did, I thought I would ask if anyone had such
a
> script. If someone happens to have something like this - I would greatly
> appreciate it.
> Thanks in advance.
>|||... also you can use sp_renamedb to rename a database, but follow Hari's so
lution to change the logical & physical filenames.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Perform
ance topic.
"Hari Prasad" wrote:

> Hi,
> To rename the logical name of the file:- use the below script
> alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
> ='NEW_MDF_NAME')
> Do the same for LDF file as well.
>
> For renaming the database.
> alter database OLD_DB_NAME modify name = new_db_name
>
> To rename the physical files. Usae the below script sample.
> sp_detach_db <dbname>
> go
> --rename the LDF file and MDF physical files using windows explorer
> go
> sp_attach_db <Dbname> ,'phsical_mdf_with_path','new_ldf_with_p
ath'
> --
> Thanks
> Hari
> MCDBA
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> a
>
>

Script to rename database (and files)

I need to rename several databases and also rename the data and log files
(since these filenames contain the DB name). I was thinking of writing a
script to do so but before I did, I thought I would ask if anyone had such a
script. If someone happens to have something like this - I would greatly
appreciate it.
Thanks in advance.
Hi,
To rename the logical name of the file:- use the below script
alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
='NEW_MDF_NAME')
Do the same for LDF file as well.
For renaming the database.
alter database OLD_DB_NAME modify name = new_db_name
To rename the physical files. Usae the below script sample.
sp_detach_db <dbname>
go
--rename the LDF file and MDF physical files using windows explorer
go
sp_attach_db <Dbname>,'phsical_mdf_with_path','new_ldf_with_pat h'
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> I need to rename several databases and also rename the data and log files
> (since these filenames contain the DB name). I was thinking of writing a
> script to do so but before I did, I thought I would ask if anyone had such
a
> script. If someone happens to have something like this - I would greatly
> appreciate it.
> Thanks in advance.
>
|||... also you can use sp_renamedb to rename a database, but follow Hari's solution to change the logical & physical filenames.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Performance topic.
"Hari Prasad" wrote:

> Hi,
> To rename the logical name of the file:- use the below script
> alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
> ='NEW_MDF_NAME')
> Do the same for LDF file as well.
>
> For renaming the database.
> alter database OLD_DB_NAME modify name = new_db_name
>
> To rename the physical files. Usae the below script sample.
> sp_detach_db <dbname>
> go
> --rename the LDF file and MDF physical files using windows explorer
> go
> sp_attach_db <Dbname>,'phsical_mdf_with_path','new_ldf_with_pat h'
> --
> Thanks
> Hari
> MCDBA
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> a
>
>

Script to rename database (and files)

I need to rename several databases and also rename the data and log files
(since these filenames contain the DB name). I was thinking of writing a
script to do so but before I did, I thought I would ask if anyone had such a
script. If someone happens to have something like this - I would greatly
appreciate it.
Thanks in advance.Hi,
To rename the logical name of the file:- use the below script
alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
='NEW_MDF_NAME')
Do the same for LDF file as well.
For renaming the database.
alter database OLD_DB_NAME modify name = new_db_name
To rename the physical files. Usae the below script sample.
sp_detach_db <dbname>
go
--rename the LDF file and MDF physical files using windows explorer
go
sp_attach_db <Dbname>,'phsical_mdf_with_path','new_ldf_with_path'
--
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> I need to rename several databases and also rename the data and log files
> (since these filenames contain the DB name). I was thinking of writing a
> script to do so but before I did, I thought I would ask if anyone had such
a
> script. If someone happens to have something like this - I would greatly
> appreciate it.
> Thanks in advance.
>|||... also you can use sp_renamedb to rename a database, but follow Hari's solution to change the logical & physical filenames.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Performance topic.
"Hari Prasad" wrote:
> Hi,
> To rename the logical name of the file:- use the below script
> alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
> ='NEW_MDF_NAME')
> Do the same for LDF file as well.
>
> For renaming the database.
> alter database OLD_DB_NAME modify name = new_db_name
>
> To rename the physical files. Usae the below script sample.
> sp_detach_db <dbname>
> go
> --rename the LDF file and MDF physical files using windows explorer
> go
> sp_attach_db <Dbname>,'phsical_mdf_with_path','new_ldf_with_path'
> --
> Thanks
> Hari
> MCDBA
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> > I need to rename several databases and also rename the data and log files
> > (since these filenames contain the DB name). I was thinking of writing a
> > script to do so but before I did, I thought I would ask if anyone had such
> a
> > script. If someone happens to have something like this - I would greatly
> > appreciate it.
> >
> > Thanks in advance.
> >
> >
>
>

Script to log/record the users who accessed my database

Hi,

I am hosting my database on a third-party Microsoft SQL server. The guest user is "Off". But, I am not sure if any one else is accessing/viewing/editing my database contents.

Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script or any exisitng machnism which can be used to track these users.

Also, are there any things that I should take care with my database from outside users.

Thanks
-SudhakarThe only way I can think of to log user access (especially read access) is to constantly run a Profiler trace. This will very likely not be possible on a third party server, even if you have admin access to the server.
A lesser option is to have the third party turn on login successful auditing, which will put an entry in the errorlog every time someone logs into the server. This will not tell you what database they log into, nor will it tell you what they did there.sql

Script task: Dts.Logging.Log

I am trying to understand the use-case of Dts.Logging.Log.

Following BOL, I see that I can use Dts.Log to send a custom log message to my log provider. The following example code produces an event called "User:ScriptTaskLogEntry", with the message "My custom message here".

Dim dataBytes(0) As Byte

Dts.Log("My custom message here", 0, dataBytes)

I would like to be able to produce my own custom event (lets call it "MyCustomEvent"), as well as a custom log message. I thought it would be possible via Dts.Logging.Log, which requires that I specify an event name, in addition to a log entry. However, when I place the following code in the script task, I do not get any logging whatsoever.

Dim dataBytes(0) As Byte

Dts.Logging.Log("MyCustomEvent", System.Environment.MachineName, System.Environment.UserName, "Custom Log", "", "", "My custom message here", Now, Now, 0, dataBytes)

Any thoughts?

Thanks!

The script task can't log custom message type. The problem is that custom log message needs to be declared by task in advance so that it appears in the Details tab of the Logging window.

Custom task (reusable separately packaged .NET or C++ code, that appears on toolbox just like any Microsoft task) can do it, but this functionality is not available in Script task.sql

Script

Hi All,
I need to write a script that would check the size of the db file and the size of transaction log file and it they are getting bigger than a certain amount I get notified. Any suggestions?
Thanks.start with sysfiles. the size number might not be totally accurate, you may want use sp_spaceused with the updateuseage argument first.|||Do you have a sample script?|||In fact I do have something somewhere that just goes and tallies the size of the database and log files. It does not however update usage because I was just looking for a quick and dirty number. This however will not meet your needs. I suggest you read up on sp_spaceused and take a look at the system catalog starting with sysfiles. This is an easy thing and it is all layed out for you in BOL if you know where to look.

In BOL, go to Contents\Transact SQL reference and than take a look at system stored procedure and system tables.|||Why wouldn't you just use alerts?|||We don't have a necessary software to do that. To get notified I use mailsend command.

scratch that

I am getting the error

Error: 18456, Severity: 14, State: 16.
Login failed for user 'PRI\sqlservice'. [CLIENT: 172.16.9.2]

in the log repeated every 15 seconds or so. I am logged into the server as pri\sqlservice, I have started the sql server and sql server agent with pri\sqlservice and I can access databases and tables inside the Server Management studio. I have set up the login pri\sqlservice and given it dbo owner access to all databases.

I am in the process of upgrading from 2000 to 2005. I have installed a new sql server 2005, restored backups of databases from 2000. I have also converted all packages and uploaded those to the new server (although they all point to the 2000 server). I have imported the jobs, but all but one are disabled and the one that is enabled ran okay when I ran it manually.

I have researched and researched this issue, and none seem to apply to my specific issue.

This is an extract of my errorlog:

2006-12-20 16:23:16.95 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

2006-12-20 16:23:16.98 Server (c) 2005 Microsoft Corporation.
2006-12-20 16:23:16.98 Server All rights reserved.
2006-12-20 16:23:16.98 Server Server process ID is 1780.
2006-12-20 16:23:16.98 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2006-12-20 16:23:16.98 Server This instance of SQL Server last reported using a process ID of 4532 at 12/20/2006 4:23:52 PM (local) 12/20/2006 9:23:52 PM (UTC). This is an informational message only; no user action is required.
2006-12-20 16:23:16.98 Server Registry startup parameters:
2006-12-20 16:23:17.00 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-12-20 16:23:17.00 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-12-20 16:23:17.00 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-12-20 16:23:17.23 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-12-20 16:23:17.23 Server Detected 2 CPUs. This is an informational message; no user action is required.
2006-12-20 16:23:17.93 Server Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
2006-12-20 16:23:19.65 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2006-12-20 16:23:22.07 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2006-12-20 16:23:23.21 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2006-12-20 16:23:23.48 Server Database Mirroring Transport is disabled in the endpoint configuration.
2006-12-20 16:23:23.79 spid5s Starting up database 'master'.
2006-12-20 16:23:25.01 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-12-20 16:23:26.17 spid5s SQL Trace ID 1 was started by login "sa".
2006-12-20 16:23:26.37 spid5s Starting up database 'mssqlsystemresource'.
2006-12-20 16:23:27.93 spid9s Starting up database 'model'.
2006-12-20 16:23:27.95 spid5s Server name is 'KSDBT01'. This is an informational message only. No user action is required.
2006-12-20 16:23:29.06 spid9s Clearing tempdb database.
2006-12-20 16:23:29.54 Server A self-generated certificate was successfully loaded for encryption.
2006-12-20 16:23:29.62 Server Server is listening on [ 'any' <ipv4> 1433].
2006-12-20 16:23:29.62 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2006-12-20 16:23:29.62 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2006-12-20 16:23:29.64 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2006-12-20 16:23:29.64 Server Dedicated admin connection support was established for listening locally on port 1434.
2006-12-20 16:23:29.68 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-12-20 16:23:32.56 spid9s Starting up database 'tempdb'.
2006-12-20 16:23:32.67 spid13s Starting up database 'msdb'.
2006-12-20 16:23:32.67 spid12s Starting up database 'test'.
2006-12-20 16:23:32.67 spid14s Starting up database 'bravoAztecnology'.
2006-12-20 16:23:32.67 spid15s Starting up database 'bravoKNE'.
2006-12-20 16:23:32.67 spid16s Starting up database 'bravoKNS'.
2006-12-20 16:23:32.67 spid17s Starting up database 'bravoKNSBelgium'.
2006-12-20 16:23:32.68 spid18s Starting up database 'bravoKNSCanada'.
2006-12-20 16:23:32.68 spid19s Starting up database 'bravoKNSEngland'.
2006-12-20 16:23:34.79 spid20s The Service Broker protocol transport is disabled or not configured.
2006-12-20 16:23:34.81 spid20s The Database Mirroring protocol transport is disabled or not configured.
2006-12-20 16:23:35.01 spid12s Starting up database 'bravoKNSKennedy'.
2006-12-20 16:23:35.20 spid17s Starting up database 'bravoKNSSuperior'.
2006-12-20 16:23:35.35 spid13s Starting up database 'bravoKNSSuperiorSS'.
2006-12-20 16:23:35.48 spid19s Starting up database 'stamp'.
2006-12-20 16:23:35.83 spid20s Service Broker manager has started.
2006-12-20 16:23:38.59 spid14s Starting up database 'KNSSpecial'.
2006-12-20 16:23:39.31 spid18s Starting up database 'knsRPCS'.
2006-12-20 16:23:39.39 spid14s Analysis of database 'KNSSpecial' (16) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2006-12-20 16:23:43.03 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2006-12-20 16:23:43.03 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-12-20 16:23:50.95 spid51 Using 'xpsqlbot.dll' version '2005.90.1399' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2006-12-20 16:23:55.85 spid51 Using 'xpstar90.dll' version '2005.90.1399' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2006-12-20 16:23:57.10 spid51 Using 'xplog70.dll' version '2005.90.1399' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2006-12-20 16:24:00.02 Logon Error: 18456, Severity: 14, State: 16.
2006-12-20 16:24:00.02 Logon Login failed for user 'PRI\sqlservice'. [CLIENT: 172.16.9.2]
2006-12-20 16:24:00.06 Logon Error: 18456, Severity: 14, State: 16.
2006-12-20 16:24:00.06 Logon Login failed for user 'PRI\sqlservice'. [CLIENT: 172.16.9.2]
2006-12-20 16:24:01.06 Logon Error: 18456, Severity: 14, State: 16.
2006-12-20 16:24:01.06 Logon Login failed for user 'PRI\sqlservice'. [CLIENT: 172.16.9.2]
2006-12-20 16:24:01.07 Logon Error: 18456, Severity: 14, State: 16.
2006-12-20 16:24:01.07 Logon Login failed for user 'PRI\sqlservice'. [CLIENT: 172.16.9.2]
2006-12-20 16:24:03.84 Logon Error: 18456, Severity: 14, State: 16.
2006-12-20 16:24:03.84 Logon Login failed for user 'PRI\sqlservice'. [CLIENT: 172.16.9.2]
2006-12-20 16:24:03.85 Logon Error: 18456, Severity: 14, State: 16.
2006-12-20 16:24:03.85 Logon Login failed for user 'PRI\sqlservice'. [CLIENT: 172.16.9.2]
2006-12-20 16:24:03.96 Logon Error: 18456, Severity: 14, State: 16.
2006-12-20 16:24:03.96 Logon Login failed for user 'PRI\sqlservice'. [CLIENT: 172.16.9.2]
2006-12-20 16:24:03.98 Logon Error: 18456, Severity: 14, State: 16.
2006-12-20 16:24:03.98 Logon Login failed for user 'PRI\sqlservice'. [CLIENT: 172.16.9.2]
2006-12-20 16:24:41.41 Logon Error: 18456, Severity: 14, State: 16.
2006-12-20 16:24:41.41 Logon Login failed for user 'PRI\sqlservice'. [CLIENT: 172.16.9.2]
2006-12-20 16:24:41.41 Logon Error: 18456, Severity: 14, State: 16.
2006-12-20 16:24:41.41 Logon Login failed for user 'PRI\sqlservice'. [CLIENT: 172.16.9.2]

Just found out its someone running a script to get into our server.|||That wasn't it. The same server is the one trying to log in. I have database mail turned on, too.