Friday, September 16, 2011

Copying files between network locations

Most of times we need to transfer folders/files to different locations like today I got a request to copy backup files between network locations. When tried DOS then understood that DOS can’t recognize UNC paths (network paths like \\dirname\foldername). So when searched found command called ‘ROBOCOPY’, this will help in transferring files between network locations.

Syntax:


Above command will copy only files from 1 to 2. Robocopy has several options which helps in copying subfolders, attributes and so on.

To copy all files and subfolders we can use \S \E.


Example:
robocopy D:\Backup \\testserver\sqlbackup$\ AdventureWorks.bak


Refer below links for detailed information.

Thursday, September 15, 2011

SQL Server Upgrade plans

  • Why does the business or department want to upgrade to SQL Server 2008?
  • What SQL Server are in scope?
  • What applications access the SQL Servers?
  • What users leverage the applications?
  • What are the user operating hours?
  • Who are the points of contact\management for the users?
  • What automated processes leverage the SQL Servers?
  • What third party applications, controls, plug-ins, etc are needed?
  • What are the upstream and downstream applications?
  • Who are the key IT team members?
  • When does the project need to be completed?
  • Which management members are supporting the project?
  • Which budgets are paying for the project?
  • Have the budgets been finalized or is management waiting for estimates?

Tuesday, September 13, 2011

Moving System Databases files to new location (SQL Server 2008 R2)

Moving tempdb files to new location: (SQL Server 2008 R2)

·         Connect to server.
·         Run the query ‘select * from sysfiles’.
·         Get the current mdf/ldf file locations.
·         Run the command:
                      ALTER DATABASE tempdb
                      MODIFY FILE ( NAME ='tempdev',
                      FILENAME = 'New Location')
                      ALTER DATABASE tempdb
                      MODIFY FILE ( NAME ='templog',
                      FILENAME = ' New Location')
·         While moving 'tempdb' do not forget to mention tempdb file names also...
Eg: alter database tempdb modify file (name = 'tempdev',
filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVERNEW\MSSQL\DATA\Tempdb\tempdev.mdf')
alter database tempdb modify file (name ='templog',
filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVERNEW\MSSQL\DATA\Tempdb\templog.ldf')
·         Restart SQL Server Services.
·         Connect back to server and run again above query to find mdf/ldf file locations.
·         Also verify in the folder weather files are moved to new locations.

·         For moving 'msdb','ReportServer','reportservertempdb' and 'model' databases we need to move files physically after using  above given command and restart sql server services.
·         SQL Server will start even if the files of ‘msdb’, 'ReportServer','reportservertempdb' database are not moved fully. But those DB's will not be accessible.
Note: SQL Server will not start if master or model database files are not moved correctly.

Original Page and moving master db to new location.