How to properly do an export and import of a database
Last year, I came across a database that had 2 corrupted tables. Fortunately, these 2 tables were actually not used so it can safely get ignored. However with those 2 corrupted tables still in the database, I could not get a passing result when doing DBCC CheckDB against the database. And I have tried exporting the database into an “empty” database created by the same application without success.
Here is how I fixed my problem:
Step 1 – Export Database
- Launch SSMS as an admin and login as SA if possible or with a user with sysadmin rights.
- Right-click on the database and select Tasks > Export Data. Click Next.
- Select SQL Server Native Client 11.x under Data Source, specify the Server Name, use SA (preferred). And then select the source database. Click Next.
- Under Destination, select SQL Server Native Client 11.x. And specify the other values.
- Click New under Database to create a new DB where the data will be exported to. Click Next.
- In the next screen, select Copy data from one or more tables or views. Click Next.
- In the next screen, select all tables you would like to export and select Next. I omitted the Views at this stage as I do not need them. And leave all the rest as default and click on Finish to start the export process.
Step 2 – Working on the Empty Database
- In my case, the application my database was using has the ability to create an empty database with all views, schemas, keys, etc. My only problem is that it then also populates some of the tables with default data. I tried to truncate these tables got FK restrictions would not allow me. So I had to run a delete from on all tables.
use <empty_db>
EXEC sp_msforeachtable @command1=”delete from ?”
GO
- This command can also be used to make sure your database is really empty. I have seen other posts where they suggest to delete all FKs and then delete the data and then reinstall FKs. For me, that is too much work.
Step 3 – Importing Data into the Empty Database
- Right-click on the Empty database, select Task > Import Data
- Fill out the data source referencing to the exported database from the previous step. Click Next.
- In the Choose a Destination window, set it to the empty database. Click Next.
- Again select Copy data from one or more tables or views on the next screen.
- In the next screen, select all tables. When they are all selected, click Edit Mappings and select Enable Identity Insert. Click Next. And hit Finish to start the import.
You should not get any errors at this point. But if you do, review the errors and make appropriate adjustments and try again.
——————————- Additional Info ———————————–
If you are having problems with constraints when deleting existing data on the table or when trying to import data. Use the command below:
EXEC sp_msforeachtable @command1=”ALTER TABLE ? NOCHECK CONSTRAINT ALL”
However, you may need to know what constraints are currently set so you know what to enable later. I use this command to show me what they are.
SELECT name, tbl=object_name(parent_obj)
FROM sysobjects
WHERE objectproperty(id, ‘CnstIsNotTrusted’) = 1
GO
SELECT *, name, is_not_trusted
FROM sys.foreign_keys
WHERE is_not_trusted = 1
GO
Disclaimer: Scripts are provided AS IS, and author is not responsible with any issues resulting to usage of this script.
EVENT 25039, LS Mediation for Server Skype for Business 2015
While I was playing around in Skype, I inadvertently caused an issue with incoming calls. After making changes in the Topology Builder for the PSTN gateway, the error below started showing up in the logs for Skype. And calls are no longer working.
Event 25039, LS Mediation Server
SDP negotiation failed with the Trunk.
Trunk FQDN xxxxxxx; trunk=xxxxxxxx, Reason SRTP is not enabled for Mediation Server.
Cause: The Trunk is either not configured correctly, incompatible with Mediation Server, or not certified.
History and Resolution:
Our PSTN gateway (AudioCodes 500) has 2 NICS, one facing the Shoretel or WAN side of the network and the other facing Skype. In Skype PSTN gateways configuration for the AudioCodes gateway, I tried changing the IPv4 Address to ‘use all configured IP Addresses‘ which caused the issue. To fix this, I set ‘Limit service usage to selected IP address‘ and I entered the IP of the AudioCodes NIC facing Skype.
I hope this post helps anyone out there having trouble configuring their skype gateways. Please feel free to leave a comment or question.
Shoretel Call problems with Juniper SRX 220
Symptoms:
- Calls to a workgroups on a different Shoretel site drops after few seconds or times out
- Calls to an ACD queue times out
- Call transfers don’t work
- Phones behind Juniper SRX 220 had same issue as above when registered to a Shoregear on a different site.
- Regular extension to extension calls work just fine
Setup:
- Phone and Shoregear switch is behind a Juniper SRX 220
- Shoregear is on a different VLAN as the phones
Findings:
- We have worked intensively with Juniper support and what they figured out is that ALG MGCP is not correctly allowed within the Juniper SRX 220. SRX isn’t handling the second MDCX connection for complex calls as expected. If the Shoregear is on the same VLAN as the phone, then the problem goes away. However, there are instances where you need the Shogear to be on a separate VLAN especially when you have a multistory building and you only have 1 shoregear at the whole site. Or if there is a Shoregear failure and you have to register the phones to a different site.
Workaround:
- As of the time of this post, Juniper is still working on a fix. But the workaround is to disable MGCP and allow MGCP related applications on the firewall.
#Disable MGCP
Set security alg mgcp disable
#Clear MGCP Sessions
clear security flow session application mgcp-ua
clear security flow session application mgcp-ca
#Allow MGCP traffic
[edit security policies from-zone trust to-zone untrust policy TR-UNTR-MGCP]
set match source-address NET-LOCAL
set match destination-address any
set match application junos-MGCP
set match application junos-MGCP-CA
set match application junos-MGCP-UA
set then permit
[edit security policies from-zone untrust to-zone trust policy UNTR-TR-MGCP]
set match source-address any
set match destination-address NET-LOCAL
set match application junos-MGCP
set match application junos-MGCP-CA
set match application junos-MGCP-UA
set then permit
[EASY] How to backup a SQL Express database using SQLCMD
Here are the steps I am doing to backup a SQL express database. In this example, I am going to demonstrate how to backup a Skype for Business SQL express database.
Step 1: Connect to your SQL Express Database Engine instance using SSMS. Make sure to put the instance name.
Step 2: Once you are connected, find the database you want to backup, right-click > Tasks > Backup
Step 3: On the Backup Database window, make the changes you would like to make (backup type, destination, etc). Click the drop-down for Script and select ‘Script Action to New Query Window’
Step 4: On the new Query window, make the final changes. You can change the path to a UNC path if you wish. Run the script to make sure it works.
BACKUP DATABASE [lyss] TO DISK = N’\\path_to_backup_directory\lyss.bak’ WITH NOFORMAT, NOINIT, NAME = N’lyss-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
If you wish to backup more databases on 1 script, repeat step 2 and select ‘Script action to Clipboard’ and put all scripts into one query window and then save it as one SQL file.
Step 5: Save the script to a .SQL file
Step 6: Create a scheduled task if you wish and under Actions, browse to the location of the SQLCMD file.
“C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE”
And add the arguments as: -S \<instance name> -i <path to the SQL file you saved in step 5>
If you are not using a scheduled task. Simply run the command:
SQLCMD -S \<instance name> -i <path of the SQL file you saved in step 5>
“Assign Policy” option missing for Outlook users.
I have always wondered why I was the only one in my whole organization that has the option ‘Assign Policy’ in Outlook. I am the Exchange Engineer and administrator, but I don’t remember giving myself exclusive permissions. Searching online also did not give me what I was looking for.
If you are like me, who searched online and did not find any answers. Here is how I fixed this issue for regular users.
I am using Exchange 2013, to allow your users to assign their own policies, follow the steps below
- Login to Exchange Admin Center (EAC)
- Go to Permissions > User Roles and modify your default Role Assignment Policy, or whichever policy is assigned to your users.
- Check the box for ‘MyRetentionPolicies’ and that should give users permissions they are missing.
Shoretel IM (Instant Messenger) presence not showing in Outlook 2013
Hi there,
I recently just reimaged my PC due to issues, and after installing Outlook 2013 and Shoretel Communicator, I noticed that my IM presence is no longer showing.
Here are the things that worked for me:
1. Under HKCU\Software\IM Providers, the DefaultIMApp was set to Lync, and I had to change it to Communicator. Try re-launching Outlook and see if it works.
2. If IM presence still does not show, under HKCU\Software\IM Providers\Communicator, check and make sure the UpAndRunning value is set to 2. You may have to toggle from 0 and back to 2.
How to send email alerts via Task Scheduler in Server 2012 R2
In Server 2008 R2 task scheduler, the send email feature is available as an option. And I have used this feature a lot mostly for my phone system servers because I get real time alerts whenever there is a potential issues on switches and servers.
When 2012R2 came out, the send mail feature was not longer available.
I am making this post to help others out there with the same issues and hopefully you will find this post useful to you.
Here are the procedures:
1. Create a powershell script to send emails, the entries for Attachment, SMTPPort, and UseSSL are optional unless required.
##############################################################################
$From = “alert@email.com”
$To = “user@email.com”
$Cc = “boss@email.com”
$Attachment = “C:\temp\Some random file.txt”
$Subject = “Subject here”
$Body = “Body of the email here”
$SMTPServer = “smtprelay.your.org”
$SMTPPort = “52”
Send-MailMessage -From $From -to $To -Cc $Cc -Subject $Subject -Body $Body -SmtpServer $SMTPServer#-port $SMTPPort -UseSsl #-Credential (Get-Credential) # -Attachments $Attachment
##############################################################################
2. Next is to create your scheduled task, and under Actions, add the powershell script to send the email.
This is very simple yet very useful to anyone that wants to setup a scheduled task that also sends email alerts. Although you can have SCOM send those alerts, it is still useful if you can do it directly from the server.
Please do not forget to leave me a comment if you see any corrections, or if you have any suggestions. Or better, if you like this post, please feel free to let me know. It won’t cost you anything.
SQL SSRS: creating multiple parameters and allowing search on single or multiple entries
I built an SSRS report for one of my colleagues and it took me a little bit to figure how to properly setup the Parameters. I am hoping this would help somebody out there that may run into same issue.
Here is the scenario:
I created 4 parameters named ‘Asset Barcode, Asset Serial, Purchase Order and Former Tag’. And if all these 4 parameters are set to blank, then all data should be displayed. And if any of the parameters are blank, then it will not be included in the query.
For the past couple of days, I have been testing and searching online on how to get this working, but it seems that a lot of people are suggesting to create a stored procedure which is very cumbersome. Without the correct script, the blank parameters are still being included in the query and thereby not giving an accurate result.
Here is how my script looks like that worked:
NOTE: DO NOT copy and paste the code below, the commas and quotes are not formatted correctly in WordPress.
SELECT
Assets.CreatedBy
,Assets.ModifiedBy
,Assets.[General/Asset Name]
,Assets.[General/Site_id]
,Assets.[General/Loc_id]
,Assets.[Assets/Classification]
,Assets.[Assets/Usage]
,Assets.[Assets/Condition]
,Assets.[Assets/Inventory Date]
,Assets.[Assets/Data Source]
,Assets.[Assets/Serial Number]
,Assets.[Assets/Former Tag No]
,Assets.[Assets/Equipment/Model]
,Assets.DateCreated
,Assets.DateModified
,Assets.[General/Purchase Order]
,Assets.[General/Barcode]
,Assets.[Employee Details/Employee Notes]
,Assets.[Assets/Mfg]
,Assets.[Assets/Category]
FROM
Assets
WHERE
(@Asset_Barcode = ” or Assets.[General/Barcode] LIKE ‘%’ + @Asset_Barcode + ‘%’) and
(@Asset_Serial = ” or Assets.[Assets/Serial Number] LIKE ‘%’ + @Asset_Serial + ‘%’) and
(@Purchase_Order = ” or Assets.[General/Purchase Order] LIKE ‘%’ + @Purchase_Order + ‘%’) and
(@Former_Tag = ” or Assets.[Assets/Former Tag No] LIKE ‘%’ + @Former_Tag + ‘%’)
Merging two tables in a non-conventional way
I found this an easy way to merge two tables together without using UNION, UNION ALL. Here is a summary that one of our admins are having that led me into creating this script:
We are using a hardware asset manager software, and the vendor did all the data migration from the old version to the new version which also included a migration of data to a new database with different table schemas. The migration went fine, but our admin noticed some values that were not migrated correctly. So on the new database, there are some missing data.
Instead of using the UNION or UNION ALL to merge the old and new database. I decided to create my own script. Which also made more sense for us, since we only wanted to update the data that are on the new database and not add junk to it. The old DB has 10,000 records and the new DB has only 7,000. The old DB included the retired assets that we no longer want.
So here is how I did it in a script: (I will leave the table and database name as is)
— You may have to fix the single and double quotes after pasting it to SMC as wordpress formatted the characters differently
use vam_master
DECLARE @T TABLE (SQL varchar(1000))
INSERT INTO @T
SELECT ‘UPDATE dbo.assets SET AssetSerialNo=”’ + [assets/serial number] + ”’ WHERE Barcode=”’ + CAST([general/barcode] as varchar(10)) + ””
FROM dbo.assets
where [general/barcode] is not NULL
INSERT INTO @T
SELECT ‘UPDATE dbo.assets SET PurchaseOrderNo=”’ + [general/purchase order] + ”’ WHERE Barcode=”’ + CAST([general/barcode] as varchar(10)) + ””
FROM dbo.assets
where [general/barcode] is not NULL
INSERT INTO @T
SELECT ‘UPDATE dbo.assets SET Model=”’ + [assets/equipment/model] + ”’ WHERE Barcode=”’+ CAST([general/barcode] as varchar(10)) + ””
FROM dbo.assets
where [general/barcode] is not NULL
INSERT INTO @T
SELECT ‘UPDATE dbo.assets SET mfg=”’ + [assets/mfg] + ”’ WHERE Barcode=”’+ CAST([general/barcode] as varchar(10)) + ””
FROM dbo.assets
where [general/barcode] is not NULL
select * from @T
Running this script will output results like ‘UPDATE dbo.assets SET AssetSerialNo=’blah’ WHERE Barcode=’blah’. So you can then run this against the new DB and it will update the table with missing values for what you have selected and just ignore those entries that do not exist on the new DB.
I hope this helps, if you are on the same situation as me.
WIKIPinoy 