Please read the following articles:
Copying Databases with Backup and Restore
How to Restore a Database to a New Location and Name (Transact-SQL)
Examples
-------------
How to change recovery model of a database
----------------------------------------------
alter database [Database1] set recovery full
alter database [Database1] set recovery simple
==================================
How to backup a database
---------------------------
backup database [Database1] to disk = 'c:\Backup\back.bak' with init
===============================================
How to find information about a database
-----------------------------------------
SP_HELPDB [Database1]
===============================================
How to find the logical name of the database
---------------------------------------------
RESTORE FILELISTONLY FROM DISK='full_path_of_backup_file'
===============================================
How to restore the database to a new server with a new name
------------------------------------------------------------
RESTORE DATABASE [Database2]
FROM DISK='full_path_of_backup_file'
WITH MOVE 'Database1' TO 'C:\Customers\DBS\Database2.mdf',
MOVE 'Database1_log' TO 'C:\Customers\DBS\Database2_log.ldf'
---------------------------------------
How to restore a vcenter database
---------------------------------------
----Alter Database to single user mode
ALTER DATABASE VIM_VCDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
----Restore Database
RESTORE DATABASE VIM_VCDB
FROM DISK='C:\Backup\VCDB.bak'
WITH REPLACE,
MOVE 'vcdb' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\VIM_VCDB.mdf',
MOVE 'vcdb_log' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\VIM_VCDB_log.ldf'
----If there is an error in statement above, enter the following
----command to revert to multi user
ALTER DATABASE VIM_VCDB SET MULTI_USER
GO
Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts
Saturday, September 11, 2010
Tuesday, September 7, 2010
How to enable remote connections to an SQL Server 2005 Express VMware VCenter Database
- Enabled remote connection on SQL Server Surface Area Configuration
Open SQL Server Surface Area Configuration.
Select Surface Area Configuration for Services and Connections.
On the left side, expand your SQL Server instance -> Database Engine -> Remote Connections. On the right side, select Local and remote connections -> using both TCP/IP and named pipes.
On the left side, select SQL Server Browser -> Service.
On the right side, if the startup type is Disable, you need to change to Automatic and click Apply and click Start button to start the service. Then, click OK.
- Change this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode
(MSSQL.1 relates to the instance you want to play with – it could be MSSQL.2, or .3 etc).
It’s default value is 1. Set it to 2 to allow both windows authentication and sql logins.
You have to restart the SQL services before the changes apply.
- To change sa password open a command prompt and enter this command
osql -E -S vcenter\SQLEXP_VIM
You will see this prompt:
1>
In this prompt enter these commands (replace sapwd with your password)
ALTER LOGIN sa WITH PASSWORD = 'sapwd';
GO
ALTER LOGIN sa ENABLE;
GO
Type exit to exit osql
- Try to connect to SQL Server using sa account and entering your new password
osql -U sa -S vcenter\SQLEXP_VIM
Resources:
http://support.microsoft.com/kb/322336
http://support.microsoft.com/kb/325003
http://support.microsoft.com/kb/322336
http://blogs.msdn.com/b/sql_protocols/archive/2005/10/22/483684.aspx
http://msdn.microsoft.com/en-us/library/ms165702.aspx
http://www.linglom.com/2007/08/31/enable-remote-connection-to-sql-server-2005-express/
Open SQL Server Surface Area Configuration.
Select Surface Area Configuration for Services and Connections.
On the left side, expand your SQL Server instance -> Database Engine -> Remote Connections. On the right side, select Local and remote connections -> using both TCP/IP and named pipes.
On the left side, select SQL Server Browser -> Service.
On the right side, if the startup type is Disable, you need to change to Automatic and click Apply and click Start button to start the service. Then, click OK.
- Change this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode
(MSSQL.1 relates to the instance you want to play with – it could be MSSQL.2, or .3 etc).
It’s default value is 1. Set it to 2 to allow both windows authentication and sql logins.
You have to restart the SQL services before the changes apply.
- To change sa password open a command prompt and enter this command
osql -E -S vcenter\SQLEXP_VIM
You will see this prompt:
1>
In this prompt enter these commands (replace sapwd with your password)
ALTER LOGIN sa WITH PASSWORD = 'sapwd';
GO
ALTER LOGIN sa ENABLE;
GO
Type exit to exit osql
- Try to connect to SQL Server using sa account and entering your new password
osql -U sa -S vcenter\SQLEXP_VIM
Resources:
http://support.microsoft.com/kb/322336
http://support.microsoft.com/kb/325003
http://support.microsoft.com/kb/322336
http://blogs.msdn.com/b/sql_protocols/archive/2005/10/22/483684.aspx
http://msdn.microsoft.com/en-us/library/ms165702.aspx
http://www.linglom.com/2007/08/31/enable-remote-connection-to-sql-server-2005-express/
Tuesday, July 6, 2010
How To Perform Scheduled Backups For SQL Server 2005 Express In Windows 7
from http://www.mydigitallife.info/2010/07/06/how-to-perform-scheduled-backups-for-sql-server-2005-express-in-windows-7/ we read:
The steps to perform scheduling backups for SQL Server 2005 Express in Windows 7:
1. Create a store procedure that allows generate the dynamic backup file name, with types of backup to run such as full, differential or transaction log backups and location of the backup files:
USE [master]
CREATE PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName sysname, @backupType CHAR(1)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),’/',”) +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),’:',”)
IF @backupType = ‘F’
SET @sqlCommand = ‘BACKUP DATABASE ‘ + @databaseName +
‘ TO DISK = ”C:\Backup\’ + @databaseName + ‘_Full_’ + @dateTime + ‘.BAK”’
IF @backupType = ‘D’
SET @sqlCommand = ‘BACKUP DATABASE ‘ + @databaseName +
‘ TO DISK = ”C:\Backup\’ + @databaseName + ‘_Diff_’ + @dateTime + ‘.BAK”
WITH DIFFERENTIAL’
IF @backupType = ‘L’
SET @sqlCommand = ‘BACKUP LOG ‘ + @databaseName +
‘ TO DISK = ”C:\Backup\’ + @databaseName + ‘_Log_’ + @dateTime + ‘.TRN”’
EXECUTE sp_executesql @sqlCommand
END
2. Create a SQL script to run the backup. In this example, we will backup database master and saved the below SQL script as dbbackup.sql and save in “c:\Backup” folder.
sp_BackupDatabase ‘master’, ‘F’
GO
QUIT
3. Create a scheduled task in Windows 7 which can be found in Control Panel -> System and Security -> Administrative Tools -> Schedule Tasks.
Click on “Create a Basic Task” and “Create Basic Task” wizard will be displayed. Type in “SQL Express Data Backup” in the “Name” and click “Next” button.
4. Specify when you want the task to be start (Daily), then click “Next” button.
5. Specify what time you want the task to be start, then click “Next” button.
6. Click Start a program to perform the task, then click “Next” button.
7. Click on Browse button to select SQLCMD.exe file from “C:\Program Files\Microsoft SQL Server\90\Tools\Binn” and type the following command in “Add arguments (optional)”: -S serverName -E -i C:\Backup\dbBackup.sql
The meaning of the command:
-S (this specifies the server\instance name for SQL Server)
serverName (this is the server\instance name for SQL Server)
-E (this allows you to make a trusted connection)
-i (this specifies the input command file)
8. Click on “Next” button to finish creating task.
If you want to test the task which has been created then you can go back to the Task Scheduler, right click on the task and select "Run".
The steps to perform scheduling backups for SQL Server 2005 Express in Windows 7:
1. Create a store procedure that allows generate the dynamic backup file name, with types of backup to run such as full, differential or transaction log backups and location of the backup files:
USE [master]
CREATE PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName sysname, @backupType CHAR(1)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),’/',”) +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),’:',”)
IF @backupType = ‘F’
SET @sqlCommand = ‘BACKUP DATABASE ‘ + @databaseName +
‘ TO DISK = ”C:\Backup\’ + @databaseName + ‘_Full_’ + @dateTime + ‘.BAK”’
IF @backupType = ‘D’
SET @sqlCommand = ‘BACKUP DATABASE ‘ + @databaseName +
‘ TO DISK = ”C:\Backup\’ + @databaseName + ‘_Diff_’ + @dateTime + ‘.BAK”
WITH DIFFERENTIAL’
IF @backupType = ‘L’
SET @sqlCommand = ‘BACKUP LOG ‘ + @databaseName +
‘ TO DISK = ”C:\Backup\’ + @databaseName + ‘_Log_’ + @dateTime + ‘.TRN”’
EXECUTE sp_executesql @sqlCommand
END
2. Create a SQL script to run the backup. In this example, we will backup database master and saved the below SQL script as dbbackup.sql and save in “c:\Backup” folder.
sp_BackupDatabase ‘master’, ‘F’
GO
QUIT
3. Create a scheduled task in Windows 7 which can be found in Control Panel -> System and Security -> Administrative Tools -> Schedule Tasks.
Click on “Create a Basic Task” and “Create Basic Task” wizard will be displayed. Type in “SQL Express Data Backup” in the “Name” and click “Next” button.
4. Specify when you want the task to be start (Daily), then click “Next” button.
5. Specify what time you want the task to be start, then click “Next” button.
6. Click Start a program to perform the task, then click “Next” button.
7. Click on Browse button to select SQLCMD.exe file from “C:\Program Files\Microsoft SQL Server\90\Tools\Binn” and type the following command in “Add arguments (optional)”: -S serverName -E -i C:\Backup\dbBackup.sql
The meaning of the command:
-S (this specifies the server\instance name for SQL Server)
serverName (this is the server\instance name for SQL Server)
-E (this allows you to make a trusted connection)
-i (this specifies the input command file)
8. Click on “Next” button to finish creating task.
If you want to test the task which has been created then you can go back to the Task Scheduler, right click on the task and select "Run".
Tuesday, November 24, 2009
How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
Read this article from MS knowledge base article
http://support.microsoft.com/kb/224071
http://support.microsoft.com/kb/224071
Thursday, May 7, 2009
How to change the name of an SQL Server machine
You may experience problem with replication and linked servers and none of SQL Agent jobs are going to work properly after rename of the server.
Here is the script that we can run on the SQL server 2000 after server get renamed in XP/2000/NT level:
sp_helpserver --to see the list of servers
-- probably only yours and probably the old name?
exec sp_dropserver 'OLD SERVER NAME'
go
exec sp_addserver 'NEW SERVER NAME', local Go
-- In SQL Server 2000 do the following!!!
USE msdb
go
Update msdb..sysjobs
set originating_server = 'NEW SERVER NAME'
where originating_server = 'OLD SERVER NAME'
go
-- Then restart SQL server services. You're done for 2000
Subscribe to:
Posts (Atom)